none
Iif with % in background expression

    Question

  • Hi guys, what can I use instead of

    =iif(fields!ParentTable.value like "Fact%","Orange",
    "Yellow")

    for a background setting? 

    I want all the rows orange in case of table calle Fact* and yellow in the other case. Is there a way to do this? How can i take over like % in SSRS?

    Thanks

    Thursday, October 24, 2013 4:08 PM

Answers

  • Hi!

    Here are a couple of ways:

    1. It appears in your example that you want to set the Fill setting to Orange for any ParentTable.Value that begins with the string "Fact".  If so, =iif(Left(Fields!ParentTable.Value,4) = "Fact", "Orange","Yellow) will work.
    2. A second way, more bullet-proof:  =iif(Instr(Fields!ParentTable.Value,"Fact") <> 0, "Orange","Yellow") will work.  This method will locate the string "Fact" anywhere in the ParentTable.Value.  The value of Instr will return the ordinal position of the start of the string "Fact" if it appears in the search string.  It will return zero if not found.  If the position of Fact must start in the first position, change the 0 to 1 in the Instr method.

    Good luck!


    DJAnsc

    Thursday, October 24, 2013 7:24 PM

All replies

  • You can do it using * like below

    =iif(Fields!ParentTable.Value Like "Fact*","Orange","Yellow")

    ---------------------------------------------------

    Please mark as answered if a post solves your problem and vote if you find it helpful.


    --sIbu

    Thursday, October 24, 2013 4:16 PM
  • I already tried but it returns nothing. Nor error nor orange...all yellow
    Thursday, October 24, 2013 5:28 PM
  • You can check if the wildcard is working by replacing "Fact*" with "*" and run your report. I am sure it will set the background color to Orange for all rows. So you probably need to double check your data as the expression works fine when I checked. 

    =iif(Fields!ParentTable.Value Like "*","Orange","Yellow")

    ---------------------------------------------------

    Please mark as answered if a post solves your problem and vote if you find it helpful.


    --sIbu

    Thursday, October 24, 2013 6:38 PM
  • Hi!

    Here are a couple of ways:

    1. It appears in your example that you want to set the Fill setting to Orange for any ParentTable.Value that begins with the string "Fact".  If so, =iif(Left(Fields!ParentTable.Value,4) = "Fact", "Orange","Yellow) will work.
    2. A second way, more bullet-proof:  =iif(Instr(Fields!ParentTable.Value,"Fact") <> 0, "Orange","Yellow") will work.  This method will locate the string "Fact" anywhere in the ParentTable.Value.  The value of Instr will return the ordinal position of the start of the string "Fact" if it appears in the search string.  It will return zero if not found.  If the position of Fact must start in the first position, change the 0 to 1 in the Instr method.

    Good luck!


    DJAnsc

    Thursday, October 24, 2013 7:24 PM