To Acorel.nl

SAP Hybris Cloud for Customer – Get more out of your reports by counting multiple unique values

Marco Meijs, 19 October 2016

Sales

Service

exception aggregation

Being able to count the unique records of different fields in your Cloud for Customer reports greatly enhances the usability of a single report for making complex selections and showing multiple results in one overview.

The use of Key-Figures in Data Sources in SAP Hybris Cloud for Customer (C4C) is an effective way to display values in your reports. The Key-Figure “Counter” is a Key-Figure that is available by default in all Data Sources and counts all rows in the Data Source. For specific calculations and other selections it can be important to count all unique values instead of all rows. To accomplish this, you can use Exception Aggregation.

Example

I will try to make this clear with below example.

We would like to know the average amount of Sub-Tickets per Ticket. For this we have a Data Source which contains Tickets and related Sub-Tickets, with the default Key-Figure: Counter.

Counting the amount of Sub-Tickets is not a problem. You can use the standard Counter for that. However, we cannot use the same Counter to count the amount of Tickets. As you can see in above example there are 5 TicketID’s displayed but only 2 are unique. The standard Counter will return the number 5 for the amount of TicketID’s. in current selection.

What we would like to see is that the TicketID is only counted twice. Normally you would need to change your selection and remove the Sub-TicketID so only the in 2 TicketID’s are counted. This way you lose the amount of Sub-TicketID’s in you overview.

To achieve that both ID’s are counted we need to have a second Key-Figure that can count the unique TicketID’s in our Data Source. This is where the use of Exception Aggregation comes in.

Setting up the Exception Aggregation Key-Figure

To create the new Key-Figure that uses Exception Aggregation you need to:

Go to: Business Analytics – > Design Key Figures.

Press the New Button and below screen appears.

Define a new name for the Key-Figure, for instance “Unique TicketID”

In the Data Source you select the Data Source where you want to use the new Key-Figure

Keep the type as Calculated Key-Figure. This is important because Exception Aggregation only works for this type of Key-Figure.

Now press the Next button.

In below screen you can see you Counter Key-Figure on the left.

Drag it to the formula entry field as shown below.

After you have done this press Next again.

You will now see the Properties screen for the new Key-Figure. Here the Exception Aggregation is done.

In the drop down select value: “Counter for All Detailed values that Are Not Zero, Null or Error”

In the characteristic you select the field for which you want to count the unique values only, in our case TicketID.

After selecting the characteristic you are almost ready.

Press the Finish button to create the new Key-Figure.

Result

You can now select the new Key Figure in your Data Source. When you add the new Key-Figure to the Data Source, according the example we used in the beginning, it now displays as follows:

As you can see the Unique TicketID now only counts 2 TicketID’s. The Counter can still be used to calculate the amount of Sub-TicketID’s. These 2 Counters can now be used to calculate the average amount of Sub-Tickets per Ticket. You no longer need to remove values from your selections to get the correct totals you want to see.