how to split credit and debit amount regionwise in report based on multiprovider

[Former Member]

Hi all,

I am making report based on multi provider ,which is having two 0SD_C03 INFOCUBE AND 0FIAR_C03 INFOCUBE.

For a customer no.40071 i am having two regions,as 07 and 08.

At query designer i am using constant selection for region and sold  to party  in credit and debit amount .And for net value i am doing constant selection for sold to party .

But i am getting same credit and debit amount for both the region  07 and 08 as well.for 40071 customer no.

please some one help me how can i get split credit and debit amount region wise for 40071 customer no.i am waiting please someone help me .

I am only depending on you guys . I am attching a screenshot  of my report.

Thanks and regards ,

jyotsna singh

[Former Member · Accepted solution]

Hi Jyotsna,

at  query designer i am using constant selection for region and sold  to party  in credit and debit amount .And for net value i am doing constant selection for sold to party .

Sold-to-party, region and net value are from sales cube right? why do you need to use constant  for Net value?

[Former Member · Accepted solution]

hi,jyothi velpula,

Thanks alot for replying me.I dont know what to do so i just tried doing netvalue constant selection.Even i tried by removing that but same report i am able to get.Please help me.

Thanks and regards,

jyotsna singh

[Former Member · Accepted solution]

Hi,

0SOLD_TO and 0DEBITOR are same, hope, you assigned these two infoobjects  to 0SOLD_TO in multi provider. If not, please correct it

then take out 0SOLD_TO as constant from both 0credit and 0debit. run query and see.

[Former Member · Accepted solution]

Hi Jyothi,

Yes,i assigned both the infoobjects at multiprovider level and i was getting a compounding error while activating multiprovider so, i removed 0debitor.

I am attaching screenshot of error.Please check it and help me please.

Thanks,

Jyotsna

[Former Member · Accepted solution]

hi,

this screen shot is not clear, can you send it again?

just assign 0sold_to_region , 0debitor_region to 0sold_to_region

since country is compounded to region, please check the navigational attribute of 0sold_to_country and assign it to both 0sold_to_country, 0debitor_country.

lastly,  no need to use constant in debit and credit keyfigures, as both region and customer exists in both targets.

[Former Member · Accepted solution]

Hi jyothi,

i am attaching clear one .

[Former Member · Accepted solution]

Hi jyothi,

This compounding consistency i am not getting now but when I am removing constant selection ,my report is not getting generated.

Thanks,

jyotsna

[Former Member · Accepted solution]

can you send screen shot of how you assign region and country in multi provider please?

[Former Member · Accepted solution]

Hi jyothi,

I am attaching screenshot

[Former Member · Accepted solution]

[Former Member · Accepted solution]

ok, how dd you map 0region and 0country in sales cube? are you reading them from master data? did those two fields have data in cube?

[Former Member · Accepted solution]

0REGION and 0COUNTRY are mapped in transformation through data source 2LIS_13_VDHDR to info cube 0SD_C03.

And 2LIS_13_VDITM to 0SD_C03.

Yes, its coming from master data and data is there in cube for these fields.

[Former Member · Accepted solution]

ok, now check your multiprovider, whther it has data for sold_to, debitor, debitor_region, debitor_county , country

take one or two customers and check if multiprovider has data for all the above mentioned fields from both targets.

[Former Member · Accepted solution]

Hi jyothi,

Data is available completely from both the cubes at multi provider for example customer no.40071.I am attaching screenshot that will help you in analysis.

[Former Member · Accepted solution]

ok, hope you check the data for 0country and net value as well. if they have the data , then what is the issue? re-check your query definition and run the report.

[Former Member · Accepted solution]

yes, 0country data is coming but when infoprovider is 0SD_C03 then  net value is coming as 1700 and debit in LC is 0.00,for 40071 customer no.

and when infoprovider is 0FIAR_C03 then net value is coming as 0.00 and debit in LC coming as 1700 for 40071 customer no.

you can see in screenshot below

[Former Member · Accepted solution]

after removing constant selection for region and sold to party  from CREDIT AND DEBIT AMOUNT .I  am not getting data for region 07 and 08 but getting extra row for region 08 ,for 40071 customer no.

and this region only having all data.

[Former Member · Accepted solution]

Hi,

for first two records, there is no country in either of cube  ,please check

[Former Member · Accepted solution]

Hi ,jyothi

9th column is 0COUNTRY for both the cubes...please reply me.

[Former Member · Accepted solution]

drap and drop country to rows, and then check the results.. if country exists in both targets, you should see data in single row, if not, you will see data in multiple rows..

[Former Member · Accepted solution]

ok, and what about constant selections at key figure?

[Former Member · Accepted solution]

you should get the values for the keyfigures without any constant values. as both characteristics in rows are present in both targets.

[Former Member · Accepted solution]

ok ,jyothi ...i am trying as you suggested.

[Former Member · Accepted solution]

hi jyothi,

I drag and dropped COUNTRY to rows and without any constant selection i made a report .Which looks like this what can i do next? PLEASE HELP ME

THANKS

[Former Member · Accepted solution]

Hi,

from your last multi provider display data screen, Country looks like a navigational attribute, but before that there is country assignment screen, in which you inserted 0country to the multiprovider but not navigational attribute. Also, keep only one region in multi provider to avoid confusion.

please check both region and country assignments again.

• if you are using navigational attributes, make sure the check box is ticked in undelying cubes

• if you use 0region from sales cube (assign 1) 0region from sales cube 2) odebitor_region  ) then insert compound object 0country (assign 1) 0country from sales cube 2) 0debitor_country)

• if you insert 0sold_to_region (assign 0sold_to_region from both sale and finance cubes)then insert compounding object 0sold_to_country (assign from both sales and finance)

[Former Member · Accepted solution]

Hi jyothi,

I did identification as you suggested .please check  it and let me know.

[Former Member · Accepted solution]

First two should be enough for your reporting. last two not necessary

[Former Member · Accepted solution]

Hi jyothi,

i tried to remove last to identification as you suggested for 0debitor_0country and 0debitor_0region .then also unchecked respected  navigational attribute.Now i am facing this error while activating multi provider ,please help me in resolving this error .

thanks

[Former Member · Accepted solution]

Hi jyothi ,

Error is resolved but same report i am again getting ,please help me.

thanks

[Former Member · Accepted solution]

Hi jyothi,

This is the report i can get .Please check it and let me know what next i can do.I am waiting for you.

Thanks and regards ,

jyotsna singh

[Former Member · Accepted solution]

Hi ,

try adding country as constant to Net value alone and check

[Former Member · Accepted solution]

hi,

i did and got this

[Former Member · Accepted solution]

Hi jyothi,

i got the correct  debit amount in this report this is the perfect report . I needed to get 08 region with debit amount of 1700  for 40071 customer no.and i got it while doing country as constant selection for net value.

But for customer no 40071  i am not getting 07 region .To get both region data 07 and 08 for a customer no.40071 what can i do?please help me. thanks alot

[Former Member · Accepted solution]

[Former Member · Accepted solution]

if 0region , have correct data , then your report is just strait forward..

like i said before, customer, region and country exits in both targets, then constant is not needed to display the column values against these rows ( assuming you did assign fiscperiod and fiscal variant)

previously I suggested to use country as constant for net value assuming sales doesn't have country.. please ignore it..

However, please use only these 6 chars in both targets

0sold_to  0sold_region, 0sold_country & 0debitor, 0debitor_region, 0debitor_country

and in mp use only char to char & navgiational attri to navi attrib ( if region is not a nav att in debitor, please switch it )..

please take help of your colleague or senior if you are beginner,

[Former Member · Accepted solution]

Hi, jyothi,

Thanks a lot for all your guidance,I am a fresher and  I don't have any person senior of Bi in my company.

jyothi when I am giving filter for fiscal year/period from (2015 to 2015) then for customer  40071 I am able to get region as 08 and debit amount as 1700

But when I am giving filter on fiscal year/period  (from 2014 to 2015) for same customer 40071

i am having different credit amount and different debit amount ,can you help me in understanding why it is happening?

please reply me ...because if I have to explain my client what is happening also it will be very less data on report for 2015.

[Former Member · Accepted solution]

Before jumping to below, were you able to get the sales and debit/credit values in single line in report?

when I am giving filter for fiscal year/period from (2015 to 2015) then for customer  40071 I am able to get region as 08 and debit amount as 1700

But when I am giving filter on fiscal year/period  (from 2014 to 2015) for same customer 4007 am having different credit amount and different debit amount  

this is normal behavior..  you can use fiscal period in rows to display values year wise.

[Former Member · Accepted solution]

No , jyothi,

When i am removing costant selection of country from net value  i am not getting sales and amount in one line .this is without constant selection

[Former Member · Accepted solution]

now  I am adding constant selection for country  at net value

[Former Member · Accepted solution]

Hi jyothi,

some body suggested me that i have to  load master data properly for 0region  and then we have to do constant selection on key figures for region .

please suggest me further steps .My project manager refuse to take this report  .

Client need to see both 07 and 08 region data for customer no.40071 .

I am attaching the expected report .In this report  for customer 40071 we can see two regions 07 and 08 and there credit and debit amount is same .which should be different for both region.

[Former Member · Accepted solution]

Hi,

I thought you might have loaded master data already..

ok, if you didn't .. please run the master data process chain if you have.. if not then load master data for customer, sold_to, 0debitor, 0region and then activate the master .

now without constant selection on columns .. run the report and see .

Psnote: constant is only used only if any one of the char in rows aren't present in both targets.

please search about constant selection in queries , you will get and idea.

[Former Member · Accepted solution]

Hi jyothi,

thanks a lot for your guidance .you are helping me a lot to learn concept very deeply .

I am little confuse with loading master data,how can I do can you help me with steps? or document .please

[Former Member · Accepted solution]

jyothi can i use these steps?

[Former Member · Accepted solution]

if 0CUSTOMER_ATTR & 0CUSTOMER_TEXT are not available in BW, then you should activate it from RSA5 (ECC) and replicate to BW then map them to 0CUSTOMER and run infopackage and dtp

after that activate master data (right click inf object and activate) .

since 0CUSTOMER is reference char for both 0SOLD_TO and 0DEBITOR, the same master data can be used as reference.

you can open a new thread if you have further questions in master data loading.