?
Solved

SSRS Divide By Null #Error

Posted on 2011-09-23
6
Medium Priority
?
733 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
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 

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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

765 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