In this blog you will learn how to show a "Total" for a calculated column where the calculation has to be done after the aggregation. This is a typical challenge that we face in most of the reporting tools as this is not something that the underlying database cannot generate. Whatever aggregation defined in metadata layer (aka universe) does not hold good for the "Total" if the aggregation is not a standard aggregation like Sum, Avg. etc.
Problem Statement: A “Total” value has to be shown with all the other Dimension values on the chart. The measure involved cannot be totaled using simple aggregation like sum, avg etc. as the measure itself is division of two other measures. For example, percentage of Sales of Electronics vs Total Sales displayed by quarter and then a "Total" for the year.
At the face of it seems to be a simple functionality that can be achieved by enabling the “Show Totals” as true in the chart properties.
The above solution works when you have a simple aggregation as defined in source or it fits in one of the below definitions
The above solution does not work when you have a calculated column which should be “Calculated after Aggregation “. Example if you have two measure “A” and “B” and a third column “C” = “A” / “B”. In this case the Total for the third column “C” should be calculated as SUM(“A”) / SUM(“B”) and not SUM(A1/B1 + A2/B2 ... ) . A1, A2 being values of A for different dimension values.
However, the system does a sum of all rows after calculating the formula which gives incorrect result. There is no way to force the system to do “Aggregation” before “Calculation”
Example – Let us look at sample data before we get into the workaround.
Create a calculated Column in Universe and made “Projection Function” as SUM
In Design Studio created a Query and edited the initial view which looks as below. See the “Total”. It adds up the values and this is what will show on chart id “Show Totals” is Enabled
Graph
As you can see in the above graph, this is not what we expect.
The Solution (Workaround) : This is a multi-step process that I layout below step by step
Voila!! – This is the result you wanted.
Conclusion:
You can use this trick on Measures as well. The system only checks that number of dimensions and measures should be same on both the combined queries. You can also have different filters on both queries in case that is needed.
You can achieve similar result when directly connecting Design Studio to HANA (And Create a Calculated Column as “A” / “B” ) as in this case the Total Calculation happens after aggregation. However, you cannot change the text - “Total Result”. See below. In the workaround you can show whatever text you want Total / Average / YTD etc.
I have given a simple example to illustrate. Think about other nonstandard aggregation like Count(distinct) etc.
Thanks
Problem Statement: A “Total” value has to be shown with all the other Dimension values on the chart. The measure involved cannot be totaled using simple aggregation like sum, avg etc. as the measure itself is division of two other measures. For example, percentage of Sales of Electronics vs Total Sales displayed by quarter and then a "Total" for the year.
At the face of it seems to be a simple functionality that can be achieved by enabling the “Show Totals” as true in the chart properties.
The above solution works when you have a simple aggregation as defined in source or it fits in one of the below definitions
- As Projection Function in Universe (Delegated projection Function is not supported in design studio)
- Or “Calculate Total As” as defined in Design Studio Edit Initial View
The above solution does not work when you have a calculated column which should be “Calculated after Aggregation “. Example if you have two measure “A” and “B” and a third column “C” = “A” / “B”. In this case the Total for the third column “C” should be calculated as SUM(“A”) / SUM(“B”) and not SUM(A1/B1 + A2/B2 ... ) . A1, A2 being values of A for different dimension values.
However, the system does a sum of all rows after calculating the formula which gives incorrect result. There is no way to force the system to do “Aggregation” before “Calculation”
Example – Let us look at sample data before we get into the workaround.
Create a calculated Column in Universe and made “Projection Function” as SUM
In Design Studio created a Query and edited the initial view which looks as below. See the “Total”. It adds up the values and this is what will show on chart id “Show Totals” is Enabled
Graph
As you can see in the above graph, this is not what we expect.
The Solution (Workaround) : This is a multi-step process that I layout below step by step
- Create a dummy Dimension in Universe – Name it Total and give a value ‘Total’. At this time do not worry about the validating the SQL as it will give an error.
- In Universe properties make sure that – “Allow use of union, intersect and minus operators’ is enabled. Then publish the universe.
- In Design Studio Edit the query and click on “Show/Hide Combined Queries Panel”
- Towards the bottom click on Insert Combined Query
- Next click on Query # 2. You will notice that the Result objects are copied but not the filters. Also make sure the “Union” is the join between two queries.
- Go ahead and add the same filters that you have in Query # 1. Replace the Dimension “Cal Year Quarter” with the dummy Dimension “Total”
- Edit Initial View. Now you can see a Dimension value of “Total” appear with the correct value.
- Graph – It will show two “Total”
- Make the “Show Totals” in Chart properties as false
- Final Graph
Voila!! – This is the result you wanted.
Conclusion:
You can use this trick on Measures as well. The system only checks that number of dimensions and measures should be same on both the combined queries. You can also have different filters on both queries in case that is needed.
You can achieve similar result when directly connecting Design Studio to HANA (And Create a Calculated Column as “A” / “B” ) as in this case the Total Calculation happens after aggregation. However, you cannot change the text - “Total Result”. See below. In the workaround you can show whatever text you want Total / Average / YTD etc.
I have given a simple example to illustrate. Think about other nonstandard aggregation like Count(distinct) etc.Thanks
- SAP Managed Tags:
