CODEWITHSUNDEEP
reporting-servicesnull
simon_s
simon_s

Reputation: 35

SSRS - IIF error in Report Builder

I am having an issue with a divide by zero error that I have half way worked through.

Basically I need (EstimatedValuePlanned - EAC) / EstimatedValuePlanned

I have the following, however I am still getting #Error on some:

= IIF (Fields!EstimatedValuePlanned.Value = 0 , 0, 
Fields!EstimatedValuePlanned.Value - Fields!EAC.Value) 
/ 
SUM(Fields!EstimatedValuePlanned.Value)

I have changed the code around several times but I still either get Error or NaN

Thank you

Upvotes: 1

Views: 7036

Answers (2)

Alan Schofield
Alan Schofield

Reputation: 21683

IIF will always evaluate both parts of the function, so when SUM(Fields!EstimatedValuePlanned.Value) is zero you will get the error even though that's not what will be returned.

Try using a SWITCH statement. SWITCH stops once an expression returns True.

Something like this

= SWITCH(
SUM(Fields!EstimatedValuePlanned.Value) = 0 , 0, 
True, (Fields!EstimatedValuePlanned.Value - Fields!EAC.Value) / SUM(Fields!EstimatedValuePlanned.Value)
)

The True expression simply acts like an else

UPDATE WITH SAMPLE I created a new report added a dataset and set the dataset query to be the following (just to generate some dummy data).

DECLARE @t TABLE (EVP float, EAC float)

INSERT INTO @t VALUES
(1,2),
(2,3),
(5,4),
(0,2),
(1,0),
(0,0)

SELECT * FROM @t

I then added a table, set the first to columns to be EVP and EAC respectively and set the 3rd column to an expression as follows.

=SWITCH (
Fields!EVP.Value = 0, 0
, True, (Fields!EVP.Value - Fields!EAC.Value) / Fields!EVP.Value
)

The report design looks like this.

enter image description here

And when it's run this is the result....

enter image description here

Try replicating the above steps and see if you can get it to work like this first then review difference to your report.

Upvotes: 2

simon_s
simon_s

Reputation: 35

I got it to work with: 

=IIF  (Fields!EstimatedValuePlanned.Value = 0 , 0, 
Fields!EstimatedValuePlanned.Value - Fields!EAC.Value) 
/ 
IIF(Fields!EstimatedValuePlanned.Value = 
0,1,Fields!EstimatedValuePlanned.Value)

Thank you both

Upvotes: 2

Related Questions