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 - sqlservercentral.com/articles/Report+Development/121619 — Chris Albert
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
This has been asked and answered many times. My favorite solution is to use If instead of IIf. See stackoverflow.com/q/5471817/2033717 stackoverflow.com/q/10432714/2033717 stackoverflow.com/q/19189132/2033717 — StevenWhite

Ross Bush Ross Bush · Accepted Answer · 2017-04-07T19:10:45

This should work for null and 0.

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

Avoid divide by zero in SSRS expression

1 Answers