Reporting services and IIF

Question

Post reply

Reporting services and IIF

Lizzy Gradidge

Right there with Babe

Points: 796
More actions
July 13, 2007 at 4:04 am

#177440

I have the following IIF statement converted from an Excel IF in a textbox expression for a Report field .
= IIF(Fields!Hazard.Value >=19 AND Fields!Hazard.Value <=76
AND
(Fields!Complience.Value <=11),"Excellent", "OOH OOH")
It works fine in Excel (the syntax is different) but in the reporting services report if the Complience .value = 15 it evaluates to True "Excellent" which is wrong!!
If the Complience .value is =0 it evaluates true " Excellent" which is correct or If the Complience .value is = 25 or 30 or 40 or 50 it evaluates as false "OHH OHH" which is also correct.
The complience field is a calculated field the sum of four other field values.
I have The following nested IIF working correctly
= IIF((Fields!Hazard.Value >=0
AND Fields!Hazard.Value <=19 AND(Fields!Complience.Value<41)),"Sat1",IIF((Fields!Hazard.Value >=19 AND Fields!Hazard.Value <=64) AND((Fields!Complience.Value>11 AND Fields!Complience.Value <36)),"Sat2", IIF((Fields!Hazard.Value >=64 AND Fields!Hazard.Value <=79) AND(Fields!Complience.Value<21),"Sat3", IIF((Fields!Hazard.Value >79 ) AND((Fields!Complience.Value>11 AND Fields!Complience.Value <16)),"Sat4","uughhh"))))
I can not for the life of me work out why the first simple IIF will not work
Can any of you advise me

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

steve smith-401573 SSChampion Points: 10889 More actions · Answer 1

You wrote:

I have the following IIF statement converted from an Excel IF in a textbox expression for a Report field .

= IIF(Fields!Hazard.Value >=19 AND Fields!Hazard.Value <=76

AND(Fields!Complience.Value <=11),"Excellent", "OOH OOH")

-- just a shot in the dark here, but why are there parentheses around the Fields!Complience... clause? What happens when you remove them?

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 2

taking steve's question a little further, maybe you intended the parentheses to be around the Hazard value checks, like

= IIF((Fields!Hazard.Value >=19 AND Fields!Hazard.Value <=76)

AND Fields!Complience.Value <=11,"Excellent", "OOH OOH")

Steve.

Lizzy Gradidge Right there with Babe Points: 796 More actions · Answer 3

Thanks guys, but unfortunatly neither removing all the extra parentheses nor just using them around the Hazard value checks make it work.

John McC Hall of Fame Points: 3410 More actions · Answer 4

If you bracket "everything" does it work?

= IIF(((Fields!Hazard.Value >=19 AND Fields!Hazard.Value <=76)

AND

(Fields!Complience.Value <=11)),"Excellent", "OOH OOH")

-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy:

Lizzy Gradidge Right there with Babe Points: 796 More actions · Answer 5

Bracket "everything" does not work either.

I have just noticed that the other nested IIf

= IIF((Fields!Hazard.Value >=0 AND Fields!Hazard.Value <=19 AND(Fields!Complience.Value<41)),"Sat1",IIF((Fields!Hazard.Value >=19 AND Fields!Hazard.Value <=64) AND((Fields!Complience.Value>11 AND Fields!Complience.Value <36)),"Sat2", IIF((Fields!Hazard.Value >=64 AND Fields!Hazard.Value <=79) AND(Fields!Complience.Value<21),"Sat3", IIF((Fields!Hazard.Value >79 ) AND((Fields!Complience.Value>11 AND Fields!Complience.Value <16)),"Sat4","uughhh"))))

also evaluates incorrectly (false) for the same values that is

Hazard= 35 and Complience = 15

Is there a property setting that the field needs in order for it to recognise it as a number ? It seems very odd that these expressions work for every value except when Complience = 15

steve smith-401573 SSChampion Points: 10889 More actions · Answer 6

What happens when you compare (Fields!Complience.Value + 0) > 11, for a value of 15?

Karma-343206 SSCrazy Points: 2104 More actions · Answer 7

Hi,

Not much comfort here i'm afraid.

I built a table in Access, 2 columns Long Int Hazard and Compliance

I then entered the following data

**TBL_TEST**
Hazard	Compliance
30	5
20	15
77	5
77	15

I then built a query

SELECT IIf([Hazard]>=19 And [Hazard]<=76 And [Compliance]<=11,"Excellent","OOH OOH") AS A, TBL_TEST.Hazard, TBL_TEST.Compliance

FROM TBL_TEST;

**Query1**
A	Hazard	Compliance
Excellent	30	5
OOH OOH	20	15
OOH OOH	77	5
OOH OOH	77	15

If i understand the criteria correctly then this is working.

K.

Jason Coleman SSCrazy Points: 2187 More actions · Answer 8

Try reversing the statement

= IIF((Fields!Hazard.Value <19 AND Fields!Hazard.Value >76)

AND Fields!Complience.Value >11,"OOH OOH","Excellent" )

to see if that gives a different result.

Can't believe that its just throwing errors on the number 15 though. Are there other combinations that you've spotted which have caused the statement to throw incorrect results?

Lizzy Gradidge Right there with Babe Points: 796 More actions · Answer 9

I am truly grateful for all your help and now feel guilty that I have been trying to solve the wrong issue

The Compliance field is a calculated field which adds the values of the previous four Score Fields. This evaluates correct each time .

I have finally figured out why this IIF does not work and gives bizzare results.It is because the Result field is unable to read the value of the Compliance field. Somehow it only evaluates the addition of first two numbers and not all four numbers .

Below are the expressions for each field

Complience

=Fields!SafetyScore.Value + Fields!StructuralScore.Value + Fields!ManagementScore.Value + Fields!EcoliScore.Value

Result

= IIF(Fields!Complience.Value <=76,"Excellent", "OOH OOH")

If I change the expression in the Result field to

=Fields!Complience.Value

I get a value of

Fields!SafetyScore.Value + Fields!StructuralScore.Value

Anyone know why it does this?

I have tried all combinations of brackets in the expression in the complience Field it still adds up correctly but the Result field returns just the value of the first two scores.

(I can be forgiven for assuming the problem was with the IIF can't I?)

Any Ideas on a work around?

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 10

Not sure why it would be doing that but you could always try i) casting the data field to ensure it's the correct type [e.g. CInt(field) ]; or ii) possilby using a function (assuming a SQL source) like ISNULL() or COELESCE(). Using either (or both) of these could ensure you're not going to be trying to add strings to numbers and also that you're not getting nulls. Not sure it will make a difference as I would have thought the VB under the covers would have done implicit conversions.

On a debuging front, have you tried removing parts of the compliance equation until it returns you more than just A + B.

Steve.

Jason Coleman SSCrazy Points: 2187 More actions · Answer 11

What does the following expression return?

Fields!ManagementScore.Value + Fields!EcoliScore.Value

As stevefromoz says, remove parts of the eqn and check the evaluated result.

Karma-343206 SSCrazy Points: 2104 More actions · Answer 12

As this is a report can the calculation not be done in the base query rather than in a control on the report and then the results field could be bound to the field in the query?

K

David Bienstock SSC Rookie Points: 47 More actions · Answer 13

try organizing it so you can use the SWITCH command so it won't be nested...

like Switch(condition1,value1, condition2,value2...)

Lizzy Gradidge Right there with Babe Points: 796 More actions · Answer 14

Oh I am an idiot

I was using both fields in the dataset & controls in the report.

Consequently one was using the field expression (which only added the first two scores ....ooooppps?) and another was using the expression in the control textbox.

I have now put all the calculated fields into the dataset and only used the controls in the report for the IIF expression.

Its all working beautifully now

Thanks a million for your help

Lizzy