Multiprovider - Subtotal calculation

[Former Member]

Hi expert,

i have this scenario:

Report on a multiprovider based on two cube : cube 1 and cube 2.

Cube 1 and Cube 2 are related but in the last one characteristic is missing.

Example:

Cube1

Customer     Group     Mater     KF1

John            sc          M1         20

John            sc          M2         40

Cube1

Group     Mater     KF2

   sc          M1        50

   sc          M2        50

Report on multiprovider:

Customer   Group    Mater  KF1  KF2

John            sc        M1     20     50

                               M2     40     50

                  total                 60   120 

Why the total of KF2 is 120 and not 100?

thanks

Best Regards.

A.

[anshu_lilhori · Accepted solution]

Have you applied constant selection on kf2--with the characteristic customer as it is not present in cube2.Otherwise data will go in another row against #.

Moreover are the keyfigure technical name of both the cube are same if yes then apply restriction with 0infoprov object you can find it under package dimension.

Regards,

AL

[Former Member · Accepted solution]

Hi , i have applied the constant selection on KF2 with customer.

The kf1 and Kf2 have different technical name.

Thanks.

Andrea

[anshu_lilhori · Accepted solution]

Then you need to check the data to get the exact issue.

I can give you a workaround to show the total as desired.

Make a new formula--Put KF2 inside it--Hit the aggregation tab--Exception aggregation as summation--reference characteristic as Mater.

Regards,

AL

[Former Member · Accepted solution]

Thanks , but i tried also this but it doesn't work.

I find that "120" is the grand total of KF2 for Group , sc in the cube 2.

I find the value "100" only if i remove the customer characteristic.

Is normal this behavior?

Thanks.

Andrea

[former_member182470 · Accepted solution]

Dear Andrea,

Since Customer is not there in cube 2, your report is not able to aggregate the whole result with respect to cust. As you have kept Cust in rows, it has ignored the summation of KF2. Cust is the main reason for this behavior. You can find the detailed explanation in below screen.

[Former Member · Accepted solution]

Thank you .

Is there a workaround to avoid this behavior?

Andrea

[former_member182470 · Accepted solution]

I feel MP does not fit your requirement. Create an Infoset with inner join of group and Mat between both cubes. Then you will get the desired output. Need not to use constant selection in the report.

[anshu_lilhori · Accepted solution]

Yes that is the correct behavior.I will give you a little background about it.when you apply constant selection on a keyfigure then it ignores all the filter and displays the overall result based on the characteristic which is present in the cube to which that keyfiure belongs.

Same you have checked as well that grand total is 120.

You can check the below wiki for more information on the same before you conclude anything.

Constant Selection and Overall Result - SAP NetWeaver Business Warehouse - SCN Wikih

Hope this provides the complete understanding what happens in the background.

Regards,

AL

[Former Member · Accepted solution]

Hello Suman,

I took a detailed view on your excel output.

And correct me if i am wrong you are suggesting in the end that 120 is coming because it is the double of KF1 i.e. 60. If the total of KF1 would have been 150 then by your logic the result row of KF2 will be 300.

If this is your understanding then i would like to correct you here by referring to Anshu reply or the link which he has suggested to go through to know about constant selection behavior on the result rows.

To explain it more in the simple terms :

If the total of group :sc in Cube 2 is 270 .

Then in the result row of KF2 the output will be 270 and not the double of KF1 i.e. 60*2.

Regards,

Ashutosh Singh

[Former Member · Accepted solution]

Thanks for the info.

It's possible to change the Result Calculations of KF2 by customer?

I would have 100 , as sum of value showed, and not 120.

Andrea

[former_member182470 · Accepted solution]

Have you tried my suggestion of Infoset?

[former_member199945 · Accepted solution]

can you copy the same kfg into new formula & use calculate result as summation  after that also kfg value not showing correctly means then change  created  formula property  in advanced tab Eliminate formula collision  put  use result of this formula .

If this also not works then need to use cell concept.

[Former Member · Accepted solution]

Thank you for your proposal but infoset is not correct for my scenario.

I need to use an union concept with a multiprovider.

Andrea

[former_member182470 · Accepted solution]

Have you tried on a trial basis to see whether your data representation happens correctly in the report?

[Former Member · Accepted solution]

Yes, data is correct.

I solve the problem as suggest "P Seshu": "copy the same kfg into new formula & use calculate result as summation"

Thank's for your support.

Andrea

[Former Member · Accepted solution]

I solved following your suggestion: "copy the same kfg into new formula & use calculate result as summation"

Thank you very much.

Best Regards.

Andrea