SSRS Nest IIF

Hi there,
I have to admit i really do struggle with nested iff statements within ssrs.
Please can someone help me?

I have a field i want to check if it is ‘Non Brittle’ , then I want to check if a boolean field is true, then rather than showing 1, i want it to show Pass.

Sounds simple right?

Well, not for me it seems…i hate nests!
So this is the statement:

Iif(Fields!AttrDesc.Value =“Non Brittle”, Iif(Fields!ResultValue.Value = “1”, “PASS”,
Iif(IsNumeric(Fields!ResultValue.Value), Val(Fields!ResultValue.Value), Fields!ResultValue.Value)

The output appears to be applying the PASS text to the result, rather than just completely replacing it.

:

Help would be much appreciated as always

Thanks!

Hi Carla,

Yes, nested IIFs are a pain. I try to do them in pieces. First I write the condition and hardcode the values:

IIF(Fields!AttrDesc.Value  = “Non Brittle”, "Brittle", "Brittle")

Test it and if works, I’ll do some refactoring by getting the easy stuff out of the way first. So let’s say I want the value blank for all the Brittle values:

IIF(Fields!AttrDesc.Value <> “Non Brittle”, "Brittle", "Non Brittle")

Then I write the condition for Pass:

Iif(Fields!ResultValue.Value = "1", "PASS","FAIL")

I’ll put these in two separate fields while testing, just to make sure each is working. If so, I replace my “Non Brittle” string with the expression.

IIF(Fields!AttrDesc.Value <>  "Non Brittle", Iif(Fields!ResultValue.Value = "1", "PASS","FAIL"))

It also helps me to write out the outcome in my native language, just to keep my eye on the prize. :wink:

FWIW, after hitting post, I had to fix it about five times…

2 Likes

Thanks so much for explaining, ive run it and get an error:
image

Can you post the expression as it is now?

Oh wait, I deleted the true part in there!

IIF(Fields!AttrDesc.Value <>  "Non Brittle", "Brittle", Iif(Fields!ResultValue.Value = "1", "PASS","FAIL"))

ok i managed to remoe the error by removing a bracket - re run the report and it still shows 1/pass

So this nest is part of a bigger nest so to speak - heres the full expression - yours you helped with with is the last & onwards:

=Iif(Fields!AttrIdent.Value = “Result”, Iif(Fields!ResultValue.Value = “99999”, “”,
Iif(IsNumeric(Fields!ResultValue.Value), Val(Fields!ResultValue.Value), Fields!ResultValue.Value)), “”)

&
Iif(Fields!AttrDesc.Value =“Ozone Resistance No Cracks”, Iif(Fields!ResultValue.Value = “1”, " / PASS",
Iif(IsNumeric(Fields!ResultValue.Value), Val(Fields!ResultValue.Value), Fields!ResultValue.Value)), “”)

&

IIF(Fields!AttrDesc.Value <> “Non Brittle”, Iif(Fields!ResultValue.Value = “1”, “PASS”,“FAIL”),"")

You need a second FALSE for the first IIF

IIF(Fields!AttrDesc.Value <> “Non Brittle”, [This is First IIF TRUE] IIF(Fields!ResultValue.Value = “1”, " SECOND IIF TRUE"," SECOND IIF FALSE"), “FIRST IIF FALSE”)

1 Like

And describe what you’re trying to do.

BTW, sometimes when I have a really gnarly (do people say that anymore?) condition, I create a VB Function and use regular If…Then…endif…else code. :wink:

@Mark_Wonsil - How would you know if that is “working”? The only thing that “tests” is if the AttrDesc is a string (or can be compared to a string).

@carla - I’m not sure you need to do the conversion of Fields!ResultValue.Value. And even if you did, the snippet
Iif(IsNumeric(Fields!ResultValue.Value), Val(Fields!ResultValue.Value), Fields!ResultValue.Value)
checks to see if ResultValue is a number, and if it is then use the Val() function. The VAL() function is typically used to convert a string to a number. If ResultValue is a number, why would you try to convert it again?

nope still cant get it to work,

What the other iff nests are doing is checking if the fields have other values, like if is was a 99999 then it shouldnt show, if its a ozone resistance then do same as what were trying to achieve now. The fields are part of the inspection results attributes and so could have any type of value.

Working here meant syntax errors.

1 Like

Try the following first:

IIF(Fields!AttrDesc.Value <> “Non Brittle”,  "'" & Fields!AttrDesc.Value & "'", “IS Non Brittle”)

That will confirm what AttrDesc actually is. I added the "'" to before and after so you can be sure there are no leading or trailing spaces. If there was a trailing space, then the output would be:
'Non Brittle ' (note the space between “Brittle” and the single quote). And that would not be equal to the string "Non Brittle"

Also, if you’re copying text right from this page, sometimes the double quotes will be converted to fancy quotes. Like the difference bewteen:
"text" and “text”

1 Like

this resulted in:

Now add the second condition to the TRUE section of the first IIF

IIF(Fields!AttrDesc.Value <> "Non Brittle",  
  IIF(Val(Fields!ResultValue.Value) = 1, "PASS", Fields!ResultValue.Value),
"IS Non Brittle")

Note that I comverted ResultValue to a number and then compared it to the number 1, instead of the string "1".
Also, to be sure we understand the desired logic, does the following table agree with what you want?

AttrDesc       ResultValue      Output
=============  ===============  ============
"Non Brittle"  "1"              PASS
"Non Brittle"  1                PASS
"Non Brittle"  "0"              0
"Non Brittle"  0                0
"Non Brittle"  "2"              2
"Non Brittle"  2                2
"Brittle"      X               (whatever X is)
"Fragile"      X               (whatever X is)

Thanks so much for your time on this

I used your statement and i get :

What i need is if a AttrDesc = Non Brittle and the Result value = 1 then show PASS like your table shows. :slight_smile:

I think you want:

IIF(Fields!AttrDesc.Value = "Non Brittle",  
  IIF(Val(Fields!ResultValue.Value) = 1, "PASS", Fields!ResultValue.Value),
"")

That will show either PASS or the value of ResultValue (when it is not 1), when AttrDesc is Non Brittle

If AttrDesc is anything but Non Brittle then the field will be blank.

edit

If that is the extent of what you need, then you could with

IIF(Fields!AttrDesc.Value = "Non Brittle" AND Val(Fields!ResultValue.Value) = 1, "PASS", "")

[quote=“ckrusen, post:16, topic:78119”]
That will show either PASS or the value of ResultValue (when it is not 1), when AttrDesc is Non Brittle
[/quote] Correct

[quote=“ckrusen, post:16, topic:78119”]
If AttrDesc is anything but Non Brittle then the field will be blank.
[/quote] Incorrect - i want it to show the value for that attrdesc

In below screenshot of the report you can see the attrdesc can be a number of different descriptions, only when it is a Non Brittle do i want it to convert this boolean from a 1 to show PASS

Does that make sense?

That should give you:
image

edit

Did you want that 55 to appear in row for G1 Tear N/M?

also, added = to the expression to show that it is the whole expression and not just one part of it)

Yes i do, this is a test result you see, associated with the g1 test

So incredibly complicated its a nightmare - thankyou for helping me