Solved

SSRS Divide By Null #Error

Posted on 2011-09-23
6
669 Views
Last Modified: 2012-05-12
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
Comment
Question by:dk04
6 Comments
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 36591172
Need to check Isnothing before Sum.

as like

=IIF (Isnothing(Fields!SalesComp.Value),0,Fields!SalesComp.Value)
0
 
LVL 22

Expert Comment

by:Thomasian
ID: 36591242
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
 
LVL 25

Expert Comment

by:TempDBA
ID: 36592117
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:dk04
ID: 36593058
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
 

Accepted Solution

by:
dk04 earned 0 total points
ID: 36593516
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
 

Author Closing Comment

by:dk04
ID: 36890316
The attached code worked and removed the error.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now