SSRS Nest IIF

Epicor ERP 10
21

So the full expression now with your section at end:

=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” AND Val(Fields!ResultValue.Value) = 1, “PASS”, “”)

results in a 1pass being output

image
image971×87 4.58 KB

22

If you want the ResultValue when the AttrDesc is not Non Brittle, then try

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

Its definately the rest of the expression causing a problem now i know that. As if i just use yours, its works…
It needs to only show values where the AttrIdent is a result which is what the first part of the expression is doing

24

Okay … I see the big picture now. The result of the expression is actually 3 expressions concatenated together.

The first is:

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

the second is:

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)), "")

and the third:

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

You are getting the 1 because the first is evaluating to a 1.

My guess would be that

  1. AttrIdent = Result is true, leading to ResultValue = 9999 being tested.
  2. ResultValue = 1 (or possibly "1")
  3. Regardless of IsNumeric(Fields!ResultValue.Value) being true or not, ResultValue will be displayed.
25

Yes youve just been working on the last part.

So what is happening is that all AttrIdent types are now showing a value on right hand side, where as before it would only show a value if it was an AttrIdent of type Result

image
image1035×488 55.9 KB

26

so you need to add that condition to the 2nd and 3rd parts

2nd:

IIF(Fields!AttrIdent.Value = "Result" AND 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)), "")

3rd:

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

Are there conditions where the more than one of the 3 parts results in a something other than "", such that you want to combine them together? If not (meaning only one of the three should evaluate to something), it should be just one big nested IIF()

27

What you effectively have is:

=IIF(Fields!AttrIdent.Value = "Result", 
  THEN IIF(Fields!ResultValue.Value = "99999", 
    THEN "",
    ELSE IIF(IsNumeric(Fields!ResultValue.Value), 
      THEN Val(Fields!ResultValue.Value), 
      ELSE Fields!ResultValue.Value
      ENDIF)    // closes IsNumeric() iif
    ENDIF),     // closes ResultsVal = "99999" iif
  ELSE ""
  ENDIF)        // closes AttrIdent iif
& 
IIF(Fields!AttrDesc.Value ="Ozone Resistance No Cracks", 
  THEN Iif(Fields!ResultValue.Value = "1", 
    THEN " / PASS", 
    ELSE IIF(IsNumeric(Fields!ResultValue.Value), 
      THEN Val(Fields!ResultValue.Value), 
      ELSE Fields!ResultValue.Value
      ENDIF)      // Closes IsNumeric() iif
    ENDIF),       // closes ResultValue = "1" iif
  ELSE ""
  ENDIF)          // closes AttrDesc iif 
&
IIF(Fields!AttrDesc.Value = "Non Brittle" AND Val(Fields!ResultValue.Value) = 1, 
  THEN "PASS", 
  ELSE ""
  END IF)  // closes AttrDesc.Value = "Non Brittle" iif

(note that is pseudo code with THEN, ELSE and ENDIF thrown in there to make it more readable. That code is not syntax correct)

Now that I see the " / PASS" in the second section, I’m guessing that is supposed to be concatenated to a result from the first section. So that you get output like: 5 / PASS

I’m also guessing that the third section is the new addition. and that it should be the only part that displays when AttrDesc is Non Brittle. That correct?