Avoid Divide By Zero Error on SSRS

By | 20 בפברואר 2012

SSRS functions (as all functions) doesn't like number divided by zero. But, SSRS function behaves weird when they meet an expression that can lead to divide by zero error.

A common report will show the amount of sale compare the target sales. To check if the sales amount meets the target we will divide the sale amount by the target. If one of the store managers on our network haven't entered the planned sale amount yet, it will be zero (on the null) and will cause an error. To keep our report (and gauge) clean, we will try a simple error handling.

For example I used the database ContosoRetailDW. You may download this database from here. I think this database is simpler than AdventureWorks and I prefer to make my demos on it.

I update the target of Amsterdam store and made it zero:

update [dbo].[FactSalesQuota] set SalesAmountQuota=0 , SalesQuantityQuota=0 where storekey=240 /* Contoso Amsterdam Store */and ScenarioKey=2 /* Target */

After that I built the query that will be used by the report. The query show sales compare to target for five stores.

 


select
    StoreName
    , sum(SalesAmount) as SalesAmount
    , sum(SalesAmountQuota) as SalesAmountQuota

 

from (
SELECT
      s.StoreName
      , SalesAmount
    , 0 SalesAmountQuota
  FROM dbo.FactSales f inner join dbo.DimStore s on f.StoreKey=s.StoreKey
  where s.StoreKey between 240 and 245 /* 5 Stores Only, To keep the report simple */

 

union all

 

 select s.StoreName
    , 0 as SalesAmount
    , SalesAmountQuota
 from dbo.FactSalesQuota F inner join dbo.DimStore s on f.StoreKey=s.StoreKey
 where f.ScenarioKey=2 /* Budget */
 and   s.StoreKey between 240 and 245
) H
group by     StoreName

I had to use the Union operator instead of join to connect the quota table into the sale table because there could be a store that sold something but doesn't have quota yet and there could be a store that has a quota defined but hasn't sold anything yet.

The Query returned those results:

I have build a report with the expression Amount Sales / Quota to show how well did we meet our goals.

image

After running the report I got a #Error on Amsterdam store. image

I decide to change the expression and add the IIF condition to help SSRS deal with the divide by zero that make his life harder. I wanted just to tell it, if the quota is zero, don't bother to calculate, just show empty cell. It looks like:

= iif(Fields!SalesAmountQuota.Value=0,"", Fields!SalesAmount.Value /

 

Fields!SalesAmountQuota.Value)

To my surprise, nothing changed.

After I spent some research time I found that SSRS like so much to calculate numbers that it does so even if it's not needed.

In an IF condition expression there are two expressions that only one of them should be shown up, depend on the condition. However, SSRS tries to calculate both the shown expression and the expression that it should not show. In our example, the IIF check and see that sales quota = 0 and that empty cell ( " " ) should be seen . But since it does calculate all the expressions, it also tries to calculate the expression on the FALSE part of the IIF. At that point it again makes the divide by zero error and fail the whole expression as error divide by zero. To avoid that error you should write the formula with two IIF conditions:

=  iif(Fields!SalesAmountQuota.Value=0,"",Fields!SalesAmount.Value
/iif(Fields!SalesAmountQuota.Value=0,1,Fields!SalesAmountQuota.Value))

Now, all calculations on the expression can be calculated without error. Let's check:

The first IIF:

Quota = 0 –> put "" (empty cell)

Quota <> 0 –> Sale / Quota if Quota <> 0 or Sale / 1 if Quota = 0 . At this point, unlike SSRS we know that quota <> 0 (it was checked on the first IIF) but we have to write it like that to make sure that SSRS will be able to calculate all parts of the IIF while quota = 0.

image

I think this is not the most efficient way to calculate expressions. Unfortunately SQL Server 2012 reporting services work on the same way.

I have opend a Microsoft connect feedback on that topic. You may vote for it here:

https://connect.microsoft.com/SQLServer/feedback/details/725800/ssrs-iif-function-calculate-both-true-and-false-part-of-the-iif-expresion

Adv

 

Share

כתיבת תגובה

האימייל לא יוצג באתר.