dbaSQL
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. (rsRuntimeErrorInExpressio n)
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
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. (rsRuntimeErrorInExpressio
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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].T extRuns[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.
The Value expression for the textrun 'textbox39.Paragraphs[0].T
Pawan, I am unable to upload the rdl until tomorrow, but I will review your suggestion as well.
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!PreviousYearTot al.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!PreviousYe arTotal.Va lue) <> 0,
(1-SUM(Fields!TotalActivit y.Value)/S UM(Fields! PreviousYe arTotal.Va lue))*-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!TotalActivi ty.Value)/ SUM(Fields !PreviousY earTotal.V alue))*-1
=SUM(fields!TotalActivity. Value)-SUM (Fields!Pr eviousYear Total.Valu e)
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!PreviousYearTot
=IIF(SUM(Fields!PreviousYe
(1-SUM(Fields!TotalActivit
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!TotalActivi
=SUM(fields!TotalActivity.
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.
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..
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!TotalActivi ty.Value)/ SUM(Fields !PreviousY earTotal.V alue))*-1
=SUM(fields!TotalActivity. Value)-SUM (Fields!Pr eviousYear Total.Valu e)
=(1-SUM(Fields!TotalActivi
=SUM(fields!TotalActivity.
ASKER
Pawan, any suggestions?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
=(1-SUM(Fields!TotalActivi
=SUM(fields!TotalActivity.