How to utilize Exception Aggregation using BEx Query Designer.

[Former Member]

How to utilize Exception Aggregation using BEx Query Designer.

Exception  aggregation can be used to derive additional information out of a limited number of Key Figures provided in the InfoCube that is to be analyzed.
Thereby, you can create Calculated Key Figures by using a formula that uses exception aggregation itself (this is a nested exception aggregation). Furthermore, it is possible to use Calculated Key Figures created on cell level (using the cell editor) with the new functions of exception aggregation.

Example: If a sales manager wants to analyze the number of materials sold and the average number of different materials sold to different customers for each sales month. In the sales InfoCube, only a Key Figure of the Sales Volume in EUR for the different sold materials is provided.  Using the functions for exception aggregation,  he can derive the additionally required two Key Figures just from the sales volume in EUR for sold materials.

Procedure: To define an exception aggregation for the example above, the following steps are performed

1.  You create a Query that contains the Characteristics "Sold-to Party" (Customer) and "Material" in the free Characteristics, the characteristics

      "Cal. year/ month" in the rows and the Key Figure "Sales Volume EUR"

2. In the next step, you define a new formula ("Different materials"), which simply includes the Query Key Figure "Sales Volume in EUR" (no operator       required in this example).

Then you access the properties of this formula and choose the exception aggregation setting "count all values<> 0" for the reference Characteristic "Material".  In this way, you have defined a counter for your sold materials derived from the Key Figure "Sales Volume in EUR". You have simply counted

the number of occurrences of "Sales Volume in EUR" to give you the new Key Figure 'Different Materials'.

3. Now you will nest the exceptions: You are now able to count the number of materials sold in each month for all customers using the step above, but you want to know what the average number of materials the customers bought in each month. To achieve this, you must define another formula   ("Avg. sold materials/ customers"), which simply refers to the first formula. Again no other operators are required. You access the properties of the second formula and choose the exception aggregation "Average of all Values" for the reference Characteristic "Sold-to Party". The Key point here is that the results of the first aggregation are further processed by the second aggregation.

4. Check your query by performing a validity check to know if your query is correct or not.

5. Save your Query with a description and a technical name and then click on execute to see how exception aggregation works.