BEx Query - Stop Aggregation of Key Figure upon Drill

[former_member265853]

Hi,

I have a situation wherein I am joining Header and Line Item details using a BW InfoSet (Header - DSO1 and Line Item DSO2). A query is built off this InfoSet providing a single view of both the Header and Line Item information. This works great and users love it. Note that this information is coming from a 3rd part application sitting in ECC and is data before posting into SAP.

Using BEx Analyzer, we have Header and Line Item fields in the rows along with the Line Item Amount and the Header Amount. If I remove the Line Item characteristics from the rows, the Amounts get aggregated(SUM UP). This is OK for the Line Item Amount (sum up all the different lines), but not so good when the Header Amounts sum together providing an incorrect header amount.

Ex: Document Number 123 has 3 distribution lines. Total Header Amount is $100.

Doc Number(DSO1) |     Line Item Number(DSO2) |      Line Item Amount(DSO1) | Header Amount (DSO2)   

123                                   1                                             $20                                   $100                      

123                                   2                                             $20                                   $100                      

123                                   3                                             $50                                   $100                      

This view looks good. Say, we remove Line Item Number from the rows, we now get:

Doc Number |      Line Item Amount  |      Header Amount

123                              $90                       $300                       

The Header Amount is incorrect as it should be $100.  My initial plan was to use Line Item Amount as it would aggregate up and provide the detail at the line item level and sum up to the header level.

As this information is before posting into SAP. None of the SAP validations are called up for this data and the sum of the line item amounts will not always tie to the header amount  (there could be tax and freight amounts at the header level in different key figures). As seen in my example above, Line Item Amount sum up to $90 whereas the Header Amount is $100.

I guess the simplest solution would be to set the Header Amount on this report to not change with any navigation/filtering etc. As in the example, the header amount remains $100. Any way this can be achieved?

Any ideas/suggestions are welcome. Thanks in advance.

-Vivek

[anshu_lilhori · Accepted solution]

Vivek,You can make use of Exception aggregation in such situation.

Create a new formula--Drop the header amount inside it--Hit the aggregation tab--Exception aggregation as --First value--Reference characteristic as--Line item number.

Hope this works.

Regards,

AL

[yasemin_kilinc]

Hi Vivek,

In such situations I generally add a new CKF to count the line item numbers. And add another formula where I simply divide the header amount to the line item number count.

You can aslo try setting line item number as constant selection for Header amount. That will also work.

Hope it gives an idea

Regards

Yasemin...

[Former Member]

Hi,

You can create exception aggregation as first value or last value on CKF and restrict it to characteristic for header .

This should solve the issue.

Regards,

Akshay.

[former_member265853]

Hi Yasemin,

Thank you for the prompt reply. I like your idea of counting line item numbers and dividing the header amounts with the count. For my situation though, Akshay and Anshu's answer worked best and like the fact that I can leverage BEx functionality out of the box.

Akshay & Anshu,

Thank for the prompt reply. Your suggested solution worked great.

Many Thanks,

Vivek

'