Skip to Content

Applies to:       SAP BW 7.X

Summary:      

This document gives a clear picture on How to handle (Calculate)  Before Aggregation (This option was available in the BW 3.x version) at BEx Query level which is obsolete in BW 7.x

Author:           Ravikumar Kypa

Company:       NTT DATA Global Delivery Services Limited

Created On:    24th July 2015

Author Bio  

Ravikumar is a Principal Consultant at NTT DATA from the SAP Analytics Practice.

Scenario:

In some of the reporting scenarios, we need to get the number of records from the info cube and we have to use that counter in calculations. We can easily achieve this in BW 3.x system, as there is a readymade option given by SAP (i.e. Before Aggregation in the Enhance tab of a Calculated Key Figure) at Bex query level.

But this option is obsolete in BW7.X system and we can’t use this option. But SAP has given a different mechanism to achieve this at Bex level.

The below illustration explains you this scenario:

Data:

0DOC_NUMBER

MAT_DOC

MATERIAL

MAT_ITEM

PLANT

CALDAY

PRICE

UNIT

12346

23457

ABC

3

2000

20150102

30

USD

12346

23458

ABC

3

2000

20150102

30

USD

12347

23459

DEF

4

3000

20150103

40

USD

12347

23459

DEF

4

4000

20150103

40

USD

12345

23456

XYZ

1

1000

20150101

25

USD

12345

23456

XYZ

2

1000

20150101

25

USD

The user wants to see the Price of each material in the report, and the format of the report is as shown below:

MATERIAL

                  Price / Material

ABC

30 USD

DEF

40 USD

XYZ

25 USD

If we execute the report in Bex, it will give the below result:

/wp-content/uploads/2015/07/1_755143.jpg

But expected output is:

MATERIAL

PRICE OF EACH UNIT

ABC

30 USD

DEF

40 USD

XYZ

25 USD

We have to calculate this using Counter at Bex query level. In BW 3.X version we can achieve this by using the option ‘Before Aggregation’ in Enhance tab of the Calculated Key Figure (Counter).

Steps to achieve this in BW 3.X system:

Formula to calculate Price of each material is Price / Counter.

Create New Calculated Key Figure (ZCOUNTER1) and give the value as 1.

/wp-content/uploads/2015/07/2_755148.jpg

In the properties of the Calculated Key Figure Click on Enhance tab:

/wp-content/uploads/2015/07/3_755149.jpg

Keep the Time of Calculation as Before Aggregation as shown in the below screen shot:

/wp-content/uploads/2015/07/4_755159.jpg

If we don’t select the above option,the Counter Value will be 1 and it gives the below output:

/wp-content/uploads/2015/07/5_755160.jpg

So we have to calculate Price of each Material with Before Aggregation property (Now the counter value will be 2):

Now the output of the query will be like this:

/wp-content/uploads/2015/07/6_755161.jpg

Now we can hide the Columns ‘Price’ and ‘Counter (Before Aggr)’ and deliver this report to Customer as per his requirement.

/wp-content/uploads/2015/07/7_755162.jpg

This option is obsolete in BW 7.X ( check the below screen shot) :

Create a Calculated Key Figure as mentioned below (Give value 1):

/wp-content/uploads/2015/07/8_755164.jpg

In the Aggregation Tab, unselect the check box: ‘After Aggregation’.

/wp-content/uploads/2015/07/9_755169.jpg

You will get the below message:

Info: Calculated Key Figure Counter (Before Aggr) uses the obsolete setting ‘Calculation Before Aggregation’.

Steps to achieve this in BW 7.X system:

Create a Calculated Key Figure as mentioned below (Give value 1):

/wp-content/uploads/2015/07/10_755180.jpg

If we this Counter directly in the calculation it will give the below output:

/wp-content/uploads/2015/07/11_755181.jpg

We can achieve the ‘Before Aggregation’ option in BW 7.x system by following the below steps:

Create Counter1 with fixed value 1:

/wp-content/uploads/2015/07/12_755182.jpg

In Aggregation Tab select the below options:

          Exception Aggregation: Counter for All detailed Values

          Characteristic: 0MAT_DOC (Because we have different Material Documents (23457, 23458) for the material ABC):

/wp-content/uploads/2015/07/13_755187.jpg

Now the output of query has given correct value for the material ABC and the other 2 are not correct as they have same Material documents (refer sample data):

/wp-content/uploads/2015/07/14_755188.jpg

Now create Counter2:

/wp-content/uploads/2015/07/15_755192.jpg

Aggregation Tab:

Exception Aggregation: Summation

Ref. Characteristic: 0MAT_ITEM (Because we have different Material Items (1, 2) for the material XYZ).

/wp-content/uploads/2015/07/16_755194.jpg

Now the output is showing correct values for the materials ABC and XYZ and still we are getting wrong values for the material DEF, as it has same Material documents and Material Items:

/wp-content/uploads/2015/07/17_755201.jpg

Now create Counter3:

/wp-content/uploads/2015/07/18_755202.jpg

    Exception Aggregation: Summation

    Ref. Characteristic: 0PLANT (Because we have different Plants (3000 and 4000) for the material DEF).

/wp-content/uploads/2015/07/19_755203.jpg

Now create New Formula: Price of Each Material

Price of Each Material  = Price / Counter3

/wp-content/uploads/2015/07/20_755207.jpg

Now the output is:

/wp-content/uploads/2015/07/21_755208.jpg

Now we can hide the columns ‘Price’ and ‘Counter3’ and show the Price of each material in the output:

/wp-content/uploads/2015/07/22_755213.jpg

Likewise we have to analyze the data in the info cube and we have to identify the Characteristics on the aggregation has happened at Bex query level and we have to use them as the Ref. Characteristic in the Calculated Key Figure and we can achieve the counter ( no. of records aggregated).

To report this post you need to login first.

4 Comments

You must be Logged on to comment or reply to a post.

  1. Mayank Jaiswal

    Hi,

    This can be achieved in two other ways:

    1) In BW 7.3 or 7.4 not sure of the version we have the option of doing exception aggregation by multiple characteristics

    2) Also you can have a concatenated infoobject which has all the combination of characteristics which you require.

    Your method was fine but with Exception Aggregation it slows the query performance a lot and in you example it is three level of exception aggregation involved.

    (0) 
      1. Mayank Jaiswal

        Hi Jalina,

        If you have GUI + BEx version 7.3 and above you will find that you will get multiple characteristics when you go for exception aggregation of any KF.

        You just have to select the characteristic combination you want to give.

        Regards,

        Mayank

        (0) 
  2. Siddhartha Anand

    Why not use the option “Last value” for exception aggregation on Price and choose any of the Characteristic Material doc or Item or Plant? It will get you the result with just one Exception Aggregation.

    Regards

    -Sid

    (0) 

Leave a Reply