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?

Answer 1

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.

Answer 2

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)

Answer 3

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

Answer 4

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