OBIEE 11g – Grand total percentage as a row

I had a peculiar requirement from the client today. The client wanted a simple sub-report which had the Status as a measure column and the measure to be displayed in one row. Something like this:

This was pretty simple but the real issue was when the user also wanted a Percentage of the Statuses as a new row. If it was a new column, I could have made a new column, edited the formula to SUM(“MeasureColumnName”) and changed the data format to percentage. Simple… But to show it as a new row was a problem.

My first thought was to make the report by combining results based on union.

I just went ahead and merged the same report twice. I had to choose the same columns again to do it. In the second copy though, I changed the formula of the measure to SUM(“MeasureColumnName”).

Apart from that, I added a new column with hard-coded values ‘Total’ and ‘TotalPercentage’ (more on this below). Now when we see the report, we get something like –

As you can see above, the TotalPercentage row has the sum of the Call Count. We just not need to show this value as a percentage. For that, we can change the formula again and now say

(“MeasureColumnName”/SUM(“MeasureColumnName”))*100

This will give us the percentage as shown below.

Now the problem is to show the % sign and show the values in decimal format. The tricky part here is that we just need to show the TotalPercentage row as percentage values and this was the main reason why I created such a column in the first place.

What we do now is use conditional formatting on the measure column (the result column and not the individual columns in the merging reports).

And Voila –

We can now hide the CreatedBy column and apart from few glitches (100 not shown as percentage and the Country name repeating), I have got what the client wanted.