Using outer Group Totals to calculate inner Group Percentages in SSRS 2008R2

Question

I am working on a UK Profit and Loss Report in SSRS 2008R2 and am struggling with the percentage calculations

Here is an example to explain my question

                 Detail     
Group1  Group2  Invoice Number  Value   %
Sales Total                       810   
        Uk Sales                  150   
                 UK964423          50   
                 UK452872         100   
        European Sales            450   
                 dkkmalk          200   
                 dkf682           250   

        Rest of World Sales       210   
                 USA12353         100   
                 CHIN25410        100   
                 AFGAN14422        10   

Variable Costs                    455   56%
        Material                  200   25%
                 Sand             150   
                 Steel             50   
        Wages                     225   28%
                 Basic Pay        175   
                 Overtime          50   
        Other Production Costs     30    4%
                 Packaging         20   
                 Consumables       10   

The percentage of 56% for the Variable costs is calculated as Variable Costs divided by Sales total (455/810). The Material percentage is similar Material total divided by Total Sales (200/810) and so on for Wages and Other Production Costs

How do I achieve these calculations please. In most cases I can get the nominator by such a formula in the group header as:

=sum(iif (GroupFieldName.Value="Variable Costs",FieldValue.Value,nothing)

But the denominator of Total Sales I can not seem to calculate!

Any suggestions welcome please, please bear in mind I would want to "future proof" the formulas / code solutions for SSRS2015 and SSRS 2017

Thanks in advance

Answer 1

Your expression isn't working correctly due to the grouping. The grouping separates your FieldValues so your SUM is only getting the total of the group.

The expression should use the SUM of your field for the group divided by all the values in your dataset.

=SUM(Fields!FieldValue.Value) / SUM(Fields!FieldValue.Value, "Dataset1")

Due to SSRS checking for divide by zero error, you might need to check for that possibility.

=IIF(SUM(Fields!FieldValue.Value, "Dataset1") = 0, 0, SUM(Fields!FieldValue.Value) )
/ 
 IIF(SUM(Fields!FieldValue.Value, "Dataset1") = 0, 1, SUM(Fields!FieldValue.Value, "Dataset1")

With the IIFs, if the SUM is 0 the calculation is 0/1 which is 0 and avoids the Divide by Zero error.