A while ago I had a request from a client for a report that forced me to invent a manner to create and manipulate the totals line, since the standard totals line didn’t cut it...
A simple version of the report in question can be described as follows; the client has a variety of customers. Every customer has a certain sales status in which the active ones are most interesting (obviously). Next to these, the inactive ones are still very interesting and the prospects are also worth looking at. So we have 3 groups of customers that will form the basis of the report.
Now, there are certain actions that need to be performed for each group of customers and the number of actions that can be performed for each group are predefined. Say, for ease of explanation, that an action is like a lunch with a representative for that customer. In the example report, the ‘# actions’ represent the total number of actions that have been performed for a customer in that sales status group. The ‘# target actions’ is the number of corporate lunches that should be performed for that client group, no matter who that customer is.
The client now requested to see a percentage that displays how many actions are carried out in response to the total of actions that has been set out for that specific customer group. So, looking back at the example, the number of actions set out (the target actions) to be achieved for the ‘active’ customers group is 10, while 8 actions were performed for a customer in that group. The percentage would be (8/10)*100, so 80% has been fulfilled for the active group of customers.
(sum("Fact Table"."# actions" by "Dim Customers"."customer status")/ "Dim Customers"."# target actions") * 100
Using the above formula for the ratio %, this yields:
Since the target actions are actually a dimension member and set at the customer group level, we need to summarize the actions for all the sales statuses in order to make the formula work. The formula works, except the grand total seems a bit off. But setting the aggregation rule to ‘server complex aggregate’ and thereby forcing it to be calculated by the BI server (as explained here) should solve this, right?
Not quite (it should be 95%)… It seems that for some reason OBI selects the first percentage, I honestly couldn’t figure out why, I assume my calculation for the action ratio % had something to do with it! But my report didn’t show the correct result for the total line and I had to find a way to create a custom total… A possible solution was to add a second query and use a union to match these total results to the rest of the report. Having worked with unions before, this was the last option I was willing to explore. After some experimenting, I found an approach using groups, something I haven’t really used before! Allow me to show you:
First, create a group called ‘Custom Total’ and add all the members from the ‘customer status’ field that make up the total needed in the report.
Now the report shows:
The ‘Custom Total’ group shows the same figures as the ‘Grand Total’ line. I could make a difference in the calculation if I was able to identify the ‘Custom Total’ row. That way I could manually provide the (correct) calculation to determine the action ratio percentage at the totals level.
Knowing we’re using a group, we can do a count distinct on the customer status to determine if we’re dealing with a single customer status or more than one, which would mean it’s the totals row. Using the following formula, we can determine the row we’re using:
count(distinct "Dim Customers"."customer status")
In our report:
Now that we can identify the ‘Custom Total’ element, the problem is nearly solved! The formula for the ratio % becomes slightly more complex, but the result is what we needed!
case when count(distinct "Dim Customers"."customer status") > 1 then ("Fact Table"."# actions"/sum("Dim Customers"."# target actions")) * 100 else (sum("Fact Table"."# actions" by "Dim Customers"."customer status")/"Dim Customers"."# target actions") * 100 end
When the custom total row can be identified, we can create conditional formatting so that the look and feel will be identical to the ‘Grand Total’ row. Notice that although I didn’t change anything for the build-in ‘Grand Total’ line, it’s behavior adapts that of the ‘Custom Total’. You can even delete the previously created group and just use the above formula, it’ll work. But this custom total can be useful when you need to link the total to a different report or color it’s background for some reason or whatever the customer wants… !