Link to home
Start Free TrialLog in
Avatar of dbaSQL
dbaSQLFlag for United States of America

asked on

SSRS - Identify the source of the 'Attempted to divide by zero' error

Report works without error in SQL Server/SSRS v2005.
Report fails with the error below when run in SQL Server/SSRS v2012.

An error has occurred during report processing. (rsProcessingAborted)
The Sort expression for the grouping ‘table3_Details_Group’ contains an error: Attempted to divide by zero. (rsRuntimeErrorInExpression)

I uploaded it into VS, and this is the error that I receive when I attempt to preview the report in VS:
The processing of SortExpression for the tablix 'table3' cannot be performed. The comparison failed. Please check the data type returned by the SortExpression.

So... I am thinking that at least one side of one of my calculations is being returned as NULL or 0, and this may be the cause of the divide by zero error.  My problem is that I cannot identify which calculation is causing the problem.  

I have attached a screenshot of the report in design mode.  The table I have encircled in red is table3, and you can see all of the calculations.  I want to ask if a SSRS Expert can tell me how to identify the source of the divide by zero error.
district-summary-rpt-design-mode.png
Avatar of dbaSQL
dbaSQL
Flag of United States of America image

ASKER

I've just thought that the better question may be how to prevent the divide by zero error, when the conditions exist in the data that will cause it.  You know, a better way to form the calculations like what I've pasted below, so that IF there is a zero or NULL, it will not produce the divide by zero error.  I've pasted a couple calculations below.   Any suggestions?

=(1-SUM(Fields!TotalActivity.Value)/SUM(Fields!PreviousYearTotal.Value))*-1
=SUM(fields!TotalActivity.Value)-SUM(Fields!PreviousYearTotal.Value)
SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi dbSQL,
Created one rdl for you for proof of concept.

Hope it helps!
Report11.rdl
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dbaSQL

ASKER

Hi angelIII!  Thank you!  I've tried your suggestion, but it does not seem to like my nullif as is.  My error is below.  I am unsure exactly how to address this.

The Value expression for the textrun 'textbox39.Paragraphs[0].TextRuns[0]' contains an error: [BC30451] 'nullif' is not declared. It may be inaccessible due to its protection level.

Pawan, I am unable to upload the rdl until tomorrow, but I will review your suggestion as well.
Avatar of dbaSQL

ASKER

I am reading that NULLIF is a SQL function, and I need a VB function in the expression.  I've found reference to what Pawan said about IIF referencing both sides of the logic before passing them to the function -- regardless of the boolean.  And then I read that instead of NULLIF, SSRS VB expressions I should use IsNothing -- but, this is also not going to workaround any divide by zero conditions.

To workarond the divide by zero, I believe I need to use the double IIF, but I am not 100% sure this is the right way to do it.  When SUM(Fields!PreviousYearTotal.Value) = 0, I want to divide by 1 instead, throw away the result, and just return Nothing as my result.  Again, I am not sure if this is the correct approach.   angeliii or pawan?  Your thoughts?  Will this work?  

=IIF(SUM(Fields!PreviousYearTotal.Value) <> 0,
    (1-SUM(Fields!TotalActivity.Value)/SUM(Fields!PreviousYearTotal.Value))*-1,
    Nothing
    )


But -- is that not only putting the safety in on one half of the construct?  Is the same effort not required for both sides of the equation?

=(1-SUM(Fields!TotalActivity.Value)/SUM(Fields!PreviousYearTotal.Value))*-1
=SUM(fields!TotalActivity.Value)-SUM(Fields!PreviousYearTotal.Value)
Avatar of dbaSQL

ASKER

Nope.  Using that in the two expressions produces the error below.  Still working it.

The processing of SortExpression for the tablix 'table3' cannot be performed. The comparison failed.  Please check the data type returned by the SortExpression.
Have you tried my suggestions..
Avatar of dbaSQL

ASKER

I have just downloaded the xml and uploaded to VS as an rdl, however it is not clear to me how your expressions can be used in my scenario. These are the expressions I am trying to adjust to workaround the divide by zero error:

=(1-SUM(Fields!TotalActivity.Value)/SUM(Fields!PreviousYearTotal.Value))*-1
=SUM(fields!TotalActivity.Value)-SUM(Fields!PreviousYearTotal.Value)
Avatar of dbaSQL

ASKER

Pawan, any suggestions?
Avatar of dbaSQL

ASKER

This problem persists, but has taken a lesser prioirty.  I can have it closed, if necessary, and come back to it when I am able to give it more focus. I was unable to get around the problem with either of the Expert's given suggestions.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dbaSQL

ASKER

The problem persists, but I don't want to leave the inquiry open indefintely.  I thank you each for your input, and I also agree with Thunder --- this should be resolved in SQL before going into SSRS, but I have not yet been able to do so.  Regardless, thanks everyone for your time.