April 12, 2010 at 1:51 pm
I've got an expression where I'm checking for the value of the sum of 4 sales fields, so if it's zero then return 0, else divide, but it seems that whenever all the sales fields have zero amount it returns the error. Below is my expression:
=iif(Sum(Fields!Sale_60Days.Value)+Sum(Fields!Sale_90Days.Value) +
Sum(Fields!Sale_120Days.Value) + Sum(Fields!Sale_180Days.Value) + Sum(Fields!Sale_365Days.Value) = 0, 0,
Sum(Fields!Sale_60Days.Value)/(Sum(Fields!Sale_60Days.Value)+Sum(Fields!Sale_90Days.Value) +
Sum(Fields!Sale_120Days.Value) + Sum(Fields!Sale_180Days.Value) + Sum(Fields!Sale_365Days.Value)))
I've tried it this way too:
=iif(Sum(Fields!Sale_60Days.Value)+Sum(Fields!Sale_90Days.Value) +
Sum(Fields!Sale_120Days.Value) + Sum(Fields!Sale_180Days.Value) + Sum(Fields!Sale_365Days.Value) >0,
Sum(Fields!Sale_60Days.Value)/(Sum(Fields!Sale_60Days.Value)+Sum(Fields!Sale_90Days.Value) +
Sum(Fields!Sale_120Days.Value) + Sum(Fields!Sale_180Days.Value) + Sum(Fields!Sale_365Days.Value)),0)
When I run the sproc from the Data tab it returns the sales values as 0.00 if there's no sales because I need dollars and cents on my report.
Does anyone see what I may be doing wrong and how to fix it?
thx,
John
April 12, 2010 at 2:06 pm
Just so you know why the IIF doesn't work: IIF is a function. SSRS reads the entire statement kind of from the inside out. It sees the division operation before it sees the IIF conditions. If the divider is zero, it still sees the zero even though you've conditioned it away.
Two ways to deal with the divide by zero. My preference is #1.
1. Use Custom Code instead of IIF. In the Code tab/window of Report Properties, enter the following:
Public Function DivideBy(ByVal Exp1, ByVal Exp2)
If Exp2 = 0 Then
DivideBy = 0
Else : DivideBy = Exp1 / Exp2
End If
End Function
Then use =code.DivideBy(value1,value2)
instead of =IIF(value2 = 0, 0, value1/value2)
OR
2. Use nested IIF to correct divide by zero errors. (From Brian Welker's weblog)
Info: People often ask how to avoid divide by zero problems in their Reporting Services reports. Let's say you have a textbox that calculates profit margin via the expression:
=Fields!Price.Value / Fields!Cost.Value
This works fine unless a value in the Cost field is zero (and is not an integer or double), in which case you will get '#error' displayed in your report. This is because a divide by zero with a decimal field will throw an exception. This exception is caught by the report processing engine and #error is displayed. If you would rather have a string like "N/A" instead of #error, you might think about creating a Reporting Services expression using the IIf function:
=IIf(Fields!Cost.Value = 0, "N/A", Fields!Price.Value / Fields!Cost.Value)
But when you preview the report, you still see #error in your report. What gives?
If you take a look at the IIf function description in the Visual Basic documentation, you will see the following:
As part of preparing the argument list for the call to IIf, the Visual Basic compiler calls every function in every expression. This means that you cannot rely on a particular function not being called if the other argument is selected by Expression.
This means that even through the value for cost is zero, the error will still be generated. So how do you work around this? You have to force the expression evaluation to avoid the division with a nested IIf:
=IIf(Fields!Cost.Value = 0, "N/A", Fields!Price.Value / IIf(Fields!Cost.Value = 0, 1, Fields!Cost.Value))
The nested IIf is evaluated first so that the divide by zero can be avoided but is not used by the outer expression if it is zero.
posted at: http://blogs.msdn.com/bwelcker/archive/2006/09/26/772650.aspx
HTH
[font="Comic Sans MS"]toolman[/font]
[font="Arial Narrow"]Numbers 6:24-26[/font]
April 12, 2010 at 2:09 pm
I think you can use a NullIf function in SSRS, but I'm not certain. If so, wrap that around the denominator, and wrap an IsNull around the whole operation. Division by null = null.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 14, 2010 at 9:13 am
It turns out I was casting the sales field in my sql code as Money and it was being passed as 0.00, so I removed the cast and it fixed the calc in SSRS. However, thanks Toolman, I'll save the public option for later use b/c I think it's a better way and will come in handy down the road.
Regards,
John
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply