SSRS Divide by Zero Error

Question

I am getting a divide by zero error in my report and seem to be missing something as to why. The data is below:

-- Sample Data  
  
CREATE TABLE Table1  
    ([period] int, [state] varchar(2), [company] varchar(9), [num_costs] int, [costs] varchar(8))  
;  
      
INSERT INTO Table1  
    ([period], [state], [company], [num_costs], [costs])  
VALUES  
    (201905, 'ST', 'company_1', 130, '42000'),  
    (201906, 'ST', 'company_1', 55, '20000'),  
    (201907, 'ST', 'company_1', 275, '88000'),  
    (201908, 'ST', 'company_1', 43, '14000'),  
    (201909, 'ST', 'company_1', 4, '1300'),  
    (201910, 'ST', 'company_1', 90, '27000'),  
    (201911, 'ST', 'company_1', 25, '3949.5'),  
    (201912, 'ST', 'company_1', 261, '7000.44'),  
    (202001, 'ST', 'company_1', 333, '78000.5'),  
    (202002, 'ST', 'company_1', 166, '45578.5'),  
    (202003, 'ST', 'company_1', 18, '4600'),  
    (202004, 'ST', 'company_1', 57, '1000.5'),  
    (202005, 'ST', 'company_1', 53, '15001.6'),  
    (202005, 'ST', 'company_2', 64, '20057.72'),  
    (202006, 'ST', 'company_2', 92, '35501.6'),  
    (202007, 'ST', 'company_2', 94, '2015'),  
    (202008, 'ST', 'company_2', 10, '1073.7'),  
    (202009, 'ST', 'company_2', 14, NULL),  
    (202010, 'ST', 'company_2', 0, '55')  
;  

I am using the following expression to calculate the average costs per period (note, I also tried Fields!num_costs.Value = 0):

=IIF(Fields!num_costs.Value <= 0 OR IsNothing(Fields!costs.Value)  
        ,Nothing   
        ,Fields!costs.Value / Fields!num_costs.Value  
)  
  

My problem occurs with company_2 in period 202010. The expression doesn't seem to be filtering the 0 value in num_costs but it catches the prior NULL value in costs. Below is an example output. Any suggestions on how to modify the expression?

Answer 1

Hi Kottn,
@Kottn ，
It is interesting that this issue should be fixed in newer version of SSRS. I test exact same expression and data in my text environment (SSRS2019 developer edition. And I remember vaguely that this is already fixed in SSRS2017). There is no error appearing.

I think you could try the work around expression like this :

=IIF(Fields!num_costs.Value > 0 ,Fields!costs.Value / Fields!num_costs.Value,Nothing )  

Or:

=iif(Fields!num_costs.Value=0,0,Fields!costs.Value/iif(Fields!num_costs.Value=0,1,Fields!num_costs.Value))  

Regards,
Joy

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet ----Hot issues October