• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 797
  • Last Modified:

SSRS Divide By Null #Error

I have the following expression in a report and thought the IsNothing would take care of null fields when dividing but it is not. I continue to get #Error for fields where Sum(Fields!SalesComp.Value)) have no value.

=IIF(IsNothing(Sum(Fields!SalesComp.Value)),0,
(Sum(Fields!Sales.Value)-Sum(Fields!SalesComp.Value))
/Sum(Fields!SalesComp.Value))
0
dk04
Asked:
dk04
1 Solution
 
Alpesh PatelAssistant ConsultantCommented:
Need to check Isnothing before Sum.

as like

=IIF (Isnothing(Fields!SalesComp.Value),0,Fields!SalesComp.Value)
0
 
ThomasianCommented:
Are you sure that you have a null value for the sum and not 0?

Try if this works
=IIF(ABS(Sum(Fields!SalesComp.Value))>0
,(Sum(Fields!Sales.Value)-Sum(Fields!SalesComp.Value))/Sum(Fields!SalesComp.Value)
,0)

Open in new window

0
 
TempDBACommented:
this is the error thrown because while summing the value, the sum function is throwing the error. The best part will be checking the data value of each row and considering it as 0 if no value is returned. You can do how PatelAlpesh\Thomasian has suggested above. You can also consider the backend to give the value of the field as 0 if the value is nothing i.e. while fetching data in the query\stored procedure itself.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
dk04Author Commented:
PatelAlpesh  Does not give me the else statement I need.

Thamasian the expression did not work. I still received the #error

The =IIF (Isnothing(SUM(Fields!SalesComp.Value)),0, does work provided I use a different else statement than what I need. For example, =IIF (Isnothing(SUM(Fields!SalesComp.Value)),0,
Fields!SalesComp.Value) works but the else statement is not what I need. I need ,(Sum(Fields!Sales.Value)-Sum(Fields!SalesComp.Value))/Sum(Fields!SalesComp.Value),0) or some variation to get the comp sales to work for those locations that actually have comp sales.
0
 
dk04Author Commented:
Thanks everyone for replying I ended up working it out two different ways.
=IIf(VAL(Sum(Fields!SalesComp.Value)) > 0,(VAL(Sum(Fields!Sales.Value))-(VAL(Sum(Fields!SalesComp.Value))))
/(VAL(Sum(Fields!SalesComp.Value))),0)

=IIf(Sum(Fields!SalesComp.Value)>0, 
(Sum(Fields!Sales.Value)-Sum(Fields!SalesComp.Value))/
IIF(SUM(Fields!SalesComp.Value)=0,1,SUM(Fields!SalesComp.Value)),0)

Open in new window

0
 
dk04Author Commented:
The attached code worked and removed the error.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now