0

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

Ian W
  • 385
  • 2
  • 10
  • 30

1 Answers1

0

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.

Hannover Fist
  • 10,393
  • 1
  • 18
  • 39
  • correct me if I'm wrong.. but doesn't SSRS evaluate both sides of the IIF and it gives you a divide by zero error anyway.. regardless of the IIF ? – Harry Jun 04 '19 at 23:54
  • In 2008R2 it did have that issue. Newer versions don't do that anymore. I think you can use `If` instead which is undocumented, but works in most versions. Or use `Switch` instead. – StevenWhite Jun 05 '19 at 16:48
  • In Hannover's answer this won't be an issue anyway as the divisor itself is calculated so as to never produce zero. – Alan Schofield Jun 05 '19 at 22:49
  • Thank you. The solution worked very well. As an extension of this question how do get a total of a column in a matrix please? – Ian W Jun 07 '19 at 18:48