This recent question in SQL Server Central prompted me to write a more detailed article on this.
When working with groups and percentages in SSRS, it is important to be aware of a couple of points.
Firstly “Weighted Averages”
Secondly “Divide By Zero and the IIF Function”
For example using a report that looks somewhat like this:
Region Revenue($) Profit($) Profit%
-AUSTRALIA 1,017,500 145,400 [14.3%]
NSW 250,000 29,000 12%
VIC 240,000 31,000 13%
QLD 225,000 26,000 12%
WA 110,000 22,000 20%
SA 70,000 19,000 27%
ACT 45,000 6000 13%
NT 40,000 9000 23%
TAS 37,500 3400 9%
You can use this SQL to generate the fake dataset:
SELECT ‘AUSTRALIA’ AS Country, ‘NSW’ AS State, 250000 AS Revenue, 29000 AS Profit
SELECT ‘AUSTRALIA’ AS Country, ‘VIC’ AS State, 240000 AS Revenue, 31000 AS Profit
SELECT ‘AUSTRALIA’ AS Country, ‘QLD’ AS State, 225000 AS Revenue, 26000 AS Profit
SELECT ‘AUSTRALIA’ AS Country, ‘WA’ AS State, 110000 AS Revenue, 22000 AS Profit
SELECT ‘AUSTRALIA’ AS Country, ‘SA’ AS State, 70000 AS Revenue, 19000 AS Profit
SELECT ‘AUSTRALIA’ AS Country, ‘ACT’ AS State, 45000 AS Revenue, 6000 AS Profit
SELECT ‘AUSTRALIA’ AS Country, ‘NT’ AS State, 40000 AS Revenue, 9000 AS Profit
SELECT ‘AUSTRALIA’ AS Country, ‘TAS’ AS State, 37500 AS Revenue, 3400 AS Profit
So here Australia is at the COUNTRY level group and within this group are the various major Austrlian States and Territories.
I’ve moved the Country group into the same column as the State detail level because this condenses the number of columns required and makes a more useful drill down where you can collapse the group on Australia
Normally when you have detail level data in SSRS and you group it, an aggregate such as SUM() would be applied.
Where each of the states has the in its Revenue field:
and in its profit field
Wheras in the Country Group row it would have
Percentage profit is equal to Profit / Revenue, displayed as a percentage.
In the case of the the Country group ,however, the SUM(Profit / Revenue) is not appropriate.
You might think that averaging these by using AVG( Profit / Revenue) is the answer but this presents its own problems.
Its fairly easy to see that 145400/ 1017500 is 14.3%, but you can’t get to this by averaging the percentage for the States.
Try it for yourself, add up all of the percentages for the states and then divide by 8 (the number of states) and the result is 16.0 .
Note that the rounding has nothing to do with the problem.
The problem with percentages is that you can’t aggregate them except in very particular situations which makes it not robust enough to ever to do it.
Directly averaging the percentage at the group level will not work as you might instinctively think.
For mathematical reasons the concept of weighted averages comes into play. This is fairly common in Finance and Economics calculations but not often used elsewhere.
The only situation where it is OK to ignore the weighting is when all of them carry the same weighting (i.e. when each State’s Revenue is identical).
E.g. if you look at these two states, the mistake is giving them both equal weighting in the average, or 1/8 of the total for AUSTRALIA
NSW 250,000 29,000 12%
SA 70,000 19,000 27%
The 12% for NSW should carry much more weight than the 27% for SA because it’s a much higher proportion of the total revenue and therefore more important.
There are 2 rows that make up the group, but the percentage of each carries a different weighting. The real answer is 14 % because the 24/150 carries a higher weighting than the 4/50.
The expression you put in the group percentage field needs to sum the lost and sum the total independently.
=SUM(Fields!Profit.Value) / SUM(Fields!Revenue.Value)
The second problem here is to avoid dividing by zero. Mathematically, dividing by zero results in Infinity. I don’t have an infinite amount of webspace to discuss the concept of infinity so I’ll promptly move, suffice to say depending on the usage, infinity either displays as “Infinity”, “NaN” or it throws an error and the report will break.
Going back to the percentage expression at the State (detail) level:
Using an expression for the profit percentage would look something like:
=Fields!Profit.Value / Fields!Revenue.Value
Then you would format the text box as a percentage.
In this example it is kind of silly to think of a situation where profit would have a value and revenue would be zero, however, it is quite feasible for both profit and revenue to be zero. 0/0 still presents the same infinity problem so it needs to be considered. There are certainly other real world examples where it would be very common for the denominator in the expression to be zero. A useful programming axiom is that if a situation is possible, no matter how remote, chances are it will happen and come back to bite you if you haven’t catered for it.
If you are lucky and revenue is never zero then that basic expression above will always work, but you aren’t lucky so you need to wrap it in an IIF function.
This is the robust formula required at the Detail level
“If the Revenue is zero, just display zero otherwise calculate the percentage by dividing profit by revenue. ”
= IIF(Fields!Revenue.Value = 0
IIF(Fields!Revenue.Value = 0
This is the robust formula required at the COUNTRY group level. Note the situation it is guarding against is when the Total Revenue for the country is zero.
If at least of of the state revenue values is more than zero then you are safe and you won’t see the infinity error propogate up to the Country row. However, assume you are not lucky and just do it like this all the time:
=IIF(Fields!Revenue.Value = 0
SUM(IIF(Fields!Revenue.Value = 0,1,Fields!Revenue.Value))
Warning! You might think that the second IIF function in that expression is unecessary but it is purely there to cater for this feature what I would call a bug but is apparently a design feature. All parts of the IIF will always be evaluated whether they are used or not in a particular result row.
This is why the first option in the innermost IIF is 1.
This option will never be evaluated for a real result, so it will never divide by that 1. It just tricks the pre run expression evaluator into not throwing a divide by zero error.
If the revenue is actually zero, the first part of the first IIF will be the one that determines the result in the cell.