Avoid divide by zero in SSRS expression

Question

I am working on a report that is attempting to find a percentage. Initially my code would look like:

= (Fields!Margin.Value) / (Fields!TotalSales.Value) * 100

However the problem is that in some scenarios TotalSales = 0.00 so this is giving me the error. I am not proficient in VBS. How do I do a NULLIF type function to avoid this?

Check out this post - http://www.sqlservercentral.com/articles/Report+Development/121619/ — Chris Albert, Apr 07 '17 at 18:32
Thank for the article. Although I don't think this works because instead of simply changing the value in the text field, I need to make sure that the formula used doesn't encounter zero. This just changes the output and doesn't change the value prior to calculation, if I'm understanding the writeup correctly — Joe Resler, Apr 07 '17 at 18:49
This has been asked and answered many times. My favorite solution is to use `If` instead of `IIf`. See http://stackoverflow.com/q/5471817/2033717 http://stackoverflow.com/q/10432714/2033717 http://stackoverflow.com/q/19189132/2033717 — StevenWhite, Apr 07 '17 at 22:33

score 3 · Accepted Answer · answered Apr 07 '17 at 19:10

3

This should work for null and 0.

=IIF(Fields!TotalSales.Value <> 0 , (Fields!Margin.Value) / (Fields!TotalSales.Value) * 100 , 0)

answered Apr 07 '17 at 19:10

Ross Bush

14,648
2
32
55

Avoid divide by zero in SSRS expression

1 Answers1

Linked