How to count the occurences of a characteristic relative to one or more other characteristics.
Business Scenario
In a report you want to count the occurrences of a characteristic relative to one or more other characteristics. For example, in an InfoCube that contains the number of ordered items per order, order item, material, and customer you would like to know how many different materials were ordered by one customer, or how many different materials were ordered by one customer with a specific order.
The Result
We will create a calculated key figure that counts the number of different materials per any other characteristic. This key figure can be used in your report.
The Step By Step Solution
- Create a Key Figure with name “Counter”. It should be of type numeric and the data type should be “Dec”.
- Insert this InfoObject into your InfoCube.
- Add the InfoObject to the Communication Structure and re-create the Update Rules of the InfoCube
- In the transfer rules set the key figure counter to a constant value of 1.
- In the BEx Query Builder create a new calculated key figure for the InfoCube. The formula should be “=Counter” i.e. simply drag the key figure counter to the formula definition area.
- When you save the calculated key figure you have to provide a technical name and a description “Counter (Average per Material)”. Additionally, you have to define the exceptional aggregation behavior of the calculated key figure. On the popup window press the button “Enhance >>>”. For Exception Aggregation choose “Average of all values”. The reference characteristic should be set to the characteristic that you want to count. This is material in our case.
- Create a second calculated key figure. Here you define the formula to be “Counter/Counter (Average per Material)“. This calculated key figure now shows you the number of different materials relative to any other characteristic.
- In the properties set the number of decimal places to 0.
- Define a new Query. In the columns use the key figure "Counter (Material per any other Char.)“. In the rows insert a drill down with characteristic customer. Add the remaining characteristics to the free characteristics.
- In the properties for characteristic customer you should set Suppress result rows to “Always”.
- Run the query. The result will be the number of different materials per customer.
- By setting a filter on order you now get the number of different materials per customer and order.
