Lazy evaluation in SSRS

Question

I'm using SSRS 2005 to produce a report, and one of the columns in my report is a simple mean calculation. I don't want to divide by zero, so for the textbox value I have put:

=Switch(Fields!Count.Value=0,0,Fields!Count.Value>0,Fields!Sum.Value/Fields!Count.Value)

This still evaluates the second expression.

And so does:

=IIF(Fields!Count.Value=0,0,Fields!Sum.Value/Fields!Count.Value)

I don't want my report to display errors. How can I overcome this issue?

Can you perform the calculation in the actual stored proc or sql statement that you are referencing? I try to avoid doing anything even a bit out of the ordinary in SSRS if I can do it in the SP itself somehow. — Charles Graham, Dec 23 '08 at 12:35
I also do this in the query where the query isn't used for other purposes (ie. it's embedded in the report). I just find it easier and less unpredictable. — Joel Mansford, Jun 30 '11 at 23:20

score 2 · Answer 1 · answered Dec 23 '08 at 14:13

Unfortunately because IIF is actually just a function, all arguments get evaluated before the function gets called (resulting in your divide by zero).

One way of embedding complex logic in an expression is to embed a function in the report. You can write a VB function to return whatever you like in the Code Tab of the Report Properties.

Public Function GetMeanValue(ByVal Sum as Decimal, ByVal Count As Int) As Decimal
    'your logic in plain old vb syntax here

End Function

In the Textbox Text expression property:

=Code.GetMeanValue(Fields!Sum.Value, Fields!Count.Value)

rao · Answer 2 · 2020-06-06T02:36:23.853

Here is solution that has always worked for me: Avoiding Divide By Zero Errors .

It gets tedious if the report has too many divisions. I prefer some vb code to do the division and handle division by zero. Not sure if too much of vb code would bring down the performance of the report.

Here is how to do it with vb Replaced he link with archive.org link. Here is the relevant content from that link:

Instead of dropping that calculation into all the textboxes that are doing division I go under Report >> Report Properties >> Code and drop in the following code:

Public Shared Function Divide(Num1 as double, Num2 as double) AS object
    IF ISNOTHING(Num2) Or Num2 = 0 Then
        Divide = "n/a"
    ELSEIF Num1 = 0 THEN
        Divide = 0
    ELSE
        Divide = Num1  / Num2
    END IF
End Function
And then in each of the textboxes on the report where I need to do division, I call the function like this:
=Code.Divide(1, 0)

score 1 · Answer 3 · answered Jun 13 '14 at 10:59

1

When checking for zeros, I always negate the expression like this:

=IIF(Fields!Count.Value<>0,Fields!Sum.Value/Fields!Count.Value,0)

This has the added benefit of avoiding the division in case the Count field is empty/null.

answered Jun 13 '14 at 10:59

Dan

10,480
23
49

score 1 · Answer 4 · edited Jul 13 '17 at 19:32

1

Try this:

=IIf(Fields!Count.Value = 0, 0, Fields!Sum.Value / IIf(Fields!Count.Value = 0, 1, Fields!Count.Value))

edited Jul 13 '17 at 19:32

underscore_d

6,309
3
38
64

answered Jan 16 '09 at 13:43

MBoy

704
1
7
18

Lazy evaluation in SSRS

4 Answers4