SAP BI Report

[Former Member]

Hi Experts,

In attached screenshots, i have forecast spend £12000 but because i did the lookup to populate PO No AND PO item and for one WBS Element there is more than on e PO no and for 1 PO no there is more than 1 item no. This forecast spend also repeated no of times in my cube. Now in the Report if i don't bring PO no and PO item then it will add and showing $756000 value. To show it correctly everytime need to bring PO no and PO item . but user don't want to bring PO no an PO item to see correct forecast spend. Please can you guide how can i restrict so it won't add up in report and will show only £12000.

[anshu_lilhori · Accepted solution]

Hi,

You need to apply Nested Exception Aggregation on your  Zspend KF.

Make a new formula and put your zspend kf inside it like this..

Nodim(Zspend)--Now hit the aggregation tab--Exception aggregation as First value--Reference char--Doc no.

Now again make a new formula and put the previously created formula inside it--again

hit the aggregation tab--Exception aggregation as First value--Reference char--item.

Hope this helps.

Regards,

AL

[Former Member · Accepted solution]

Hi Anshu,

Thanks for reply. It is worked for these values. But when value is roll up total is coming wrong against the Campaign.

e.g. Total value for Campaign CP/MP/13/RI-MPD ATL should be 159600 WHILE cp/mp/13/ri/smd btl cust should be 89500.

Please suggest how to solve this.

Thanks and REGARDS,

PK

[former_member182470 · Accepted solution]

Have you observed  your Budget value figures are same like your expected figures in ForeCast spend?

I feel you should also consider "Status" field while applying exception aggregation.

[Former Member · Accepted solution]

Hi Suman,

They are correct. Please can you suggest how to use status.

Thanks

PK

[former_member182470 · Accepted solution]

Try with the "Over All Result" settings for your Spend Kf. Eg: Summation or Nothing defined

[anshu_lilhori · Accepted solution]

Hi,

Just select final formula for forecast spend--and apply calculate result as total.

If this does not work then apply one more level of exception aggregation--Total--Refer char--the object under campaign.

Hope that helps.

Regards,

AL

[Former Member · Accepted solution]

Hi Anshu,

You are champ my friend. It worked.

Thanks

PK

[anshu_lilhori · Accepted solution]

Pawan its all matter of time and experience even i faced such issues in my projects so know the workarounds and also feel good to share my knowledge with others..

Thanks for your kind words indeed.

Regards,

AL

[former_member182470]

I too agree with anshu's approach. Nested aggregation should work with first value and last value while nesting.