How to…
Count the occurrences of a
characteristic relative to one or
more other characteristics
B USINESS I NFORMATION W AREHOUSE
ASAP How to Paper
Applicable Releases: BW 2.0B, 2.1C
May 2001
SAP (SAP America, Inc. and SAP AG) assumes no responsibility for errors or omissions in these materials.
These materials are provided “as is” without a warranty of any kind, either express or implied, including but not limited to, the
implied warranties of merchantability, fitness for a particular purpose, or non-infringement.
SAP shall not be liable for damages of any kind including without limitation direct, special, indirect, or consequential damages
that may result from the use of these materials.
SAP does not warrant the accuracy or completeness of the information, text, graphics, links or other items contained within
these materials. SAP has no control over the information that you may access through the use of hot links contained in these
materials and does not endorse your use of third party web pages nor provide any warranty whatsoever relating to third
party web pages.
HOW TO … COUNT THE OCCURRENCES OF A CHARACTERISTIC
1 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.
2 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.
Additional information: The solution described in this How-to… Guide applies
for BW 2.0B/2.1C. With BW 3.0 and higher, an additional option for the
exception aggregation of a calculated key figure is available: “Counting all
values”.
Please check the following path in the documentation to find the details: SAP
Business Information Warehouse – Business Explorer – Query Design: BEx
Query Designer – Properties of Query Components – Calculated Key Figure
Properties
2001 SAP AMERICA , I NC. AND SAP AG 1
HOW TO … COUNT THE OCCURRENCES OF A CHARACTERISTIC
3 The Step By Step Solution
1. Create a Key Figure with name
“Counter”. It should be of type
numeric and the data type should
be “Dec”.
2001 SAP AMERICA , I NC. AND SAP AG 2
HOW TO … COUNT THE OCCURRENCES OF A CHARACTERISTIC
2. Insert this InfoObject into your
InfoCube.
3. Add the InfoObject to the
Communication Structure and re-
create the Update Rules of the
InfoCube.
2001 SAP AMERICA , I NC. AND SAP AG 3
HOW TO … COUNT THE OCCURRENCES OF A CHARACTERISTIC
4. In the transfer rules set the key
figure counter to a constant value
of 1.
5. 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.
2001 SAP AMERICA , I NC. AND SAP AG 4
HOW TO … COUNT THE OCCURRENCES OF A CHARACTERISTIC
6. 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.
2001 SAP AMERICA , I NC. AND SAP AG 5
HOW TO … COUNT THE OCCURRENCES OF A CHARACTERISTIC
7. 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.
8. 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.
2001 SAP AMERICA , I NC. AND SAP AG 6
HOW TO … COUNT THE OCCURRENCES OF A CHARACTERISTIC
9. In the properties for characteristic
customer you should set Suppress
result rows to “Always”.
10. Run the query. The result will
be the number of different materials
per customer.
2001 SAP AMERICA , I NC. AND SAP AG 7
HOW TO … COUNT THE OCCURRENCES OF A CHARACTERISTIC
11. By setting a filter on order you
now get the number of different
materials per customer and order.
2001 SAP AMERICA , I NC. AND SAP AG 8
