1

I have the following equation - for basic division on a report to get percents.

ex_re_ct/p_ct = some value %

My equation needs to take ex_re_ct and divide it by p_ct. However in some cases p_ct has no value (it's NULL) and since i'm dealing with totals in the SSRS report I can't do this in SQL.

So i wrote the following equation -- 

=iif(   IsNothing((Sum(Fields!p_cnt.Value, "group1_v")))
        ,0
        ,((Sum(Fields!ex_re_ct.Value, "group1_v"))/(Sum(Fields!p_cnt.Value, "group1_v")))
    )

When i do this. (5 being a dummy value in the equation). It works without issue. But when i put the value in to divide it generates an #Error where there is null data. 

=iif(   IsNothing((Sum(Fields!p_cnt.Value, "group1_v")))
        ,0
        ,((Sum(Fields!ex_re_ct.Value, "group1_v"))/5)
    )

please advise.

Elizabeth
  • 719
  • 1
  • 14
  • 27
  • See comment #19190408 – Dale K Nov 29 '18 at 20:01
  • @DaleBurrell Sorry to be dense but how do I find comment #19190408 – Elizabeth Nov 29 '18 at 20:03
  • Just add `#19190408` to the URL :) – Dale K Nov 29 '18 at 20:04
  • Okay, i saw the comment. I still feel a little dense about the logic. If the divisor is = 0, then return 0, otherwise just do the division. Or if the divisor is Nothing (isnull) return 0 and if its not do the division. why do i need to have to Iifs. – Elizabeth Nov 29 '18 at 20:06
  • 1
    `SSRS doesn't actually short circuit an IIF statement` means that SSRS evaluates both possible results even though it only uses one of them. Therefore you have to add protection in both results. – Dale K Nov 29 '18 at 20:08
  • @DaleBurrell that is both limiting and frustrating, whats the point of all the checks. So what I have to say is if its zero, return zero. else check again if its zero, and return zero and then if its not divide by the divisor – Elizabeth Nov 29 '18 at 20:09
  • Agreed - scripting in SSRS can be very frustrating - if only it was in C# :) – Dale K Nov 29 '18 at 20:11
  • @DaleBurrell so I did something like this and I still get an error `=iif( IsNothing((Sum(Fields!p_cnt.Value, "group1_v"))) ,0 ,iif(IsNothing((Sum(Fields!p_cnt.Value, "venue"))),0,((Sum(Fields!ex_re_Ct.Value, "venue"))/(Sum(Fields!p_cnt.Value, "venue"))))` ) I'm not sure if its readable. But I still get an #error – Elizabeth Nov 29 '18 at 20:15
  • 1
    You need the `IIF` on the divisor i.e. `Sum(Fields!p_cnt.Value, "venue"))` to return a valid divisor e.g. `IIf(IsNothing(Sum(Fields!p_cnt.Value, "venue")), 1, Sum(Fields!p_cnt.Value, "venue"))` – Dale K Nov 29 '18 at 20:20
  • @DaleBurrell got it, and fixed it. Thank you for all your help. – Elizabeth Nov 29 '18 at 20:21

0 Answers0