Solved

SSRS Divide By Null #Error

Posted on 2011-09-23
6
717 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 

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

Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

691 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