none
SSRS Conditional IIF Statement

    Question

  • I am trying to get images to display based on two conditions.

    The first condition will always be the same, it is the second condition that would determine which image to display.

    I tried using the following:

    =IIF(Fields!ItemPrice.Value > 0 AND Parameters!Loc.Value = "1" OR Parameters!Loc.Value = "2","Img01")

      IIF(Fields!ItemPrice.Value > 0 AND Parameters!Loc.Value = "3","Img02")

    That of course, didn't work.

    So, I tried using the SWITCH function. But when I use this, I get the red X. 

    =SWITCH (

    Fields!ItemPrice.Value > 0,
    Parameters!Loc.Value = "1" OR Parameters!Loc.Value = "2","Img01",
    Parameters!Loc.Value = "3","Img02"
    )

    My image source is set to embedded and the images are in the images folder under the report data. I made sure I could see the images so I tried a simple IIF statement and the images display, just not under the conditions I would like them to.

    =IIF(Fields!ItemPrice.Value > 0, "Img01", "Img02")

    Any ideas?

    Wednesday, December 12, 2012 11:56 PM

Answers

  • Hello Hog,

    Hope you are trying these assigning of image expression under background image properties of a textbox or any other control.

    if not please assign the expression under : text box properties > background image > value >

     

    IF statement :

    =IIF(Fields!Id.Value > 0 AND (Parameters!LOC.Value = "1" OR Parameters!LOC.Value = "2"),"TNlogo",
    IIF(Fields!Id.Value > 0 AND Parameters!LOC.Value = "3","Officelogo",""))

     

    same expression in Switch statement:

    =switch(Fields!Id.Value > 0 AND (Parameters!LOC.Value = "1" OR Parameters!LOC.Value = "2"),"TNlogo",
    Fields!Id.Value > 0 AND Parameters!LOC.Value = "3","Officelogo")

     

     

    please feel free to revert if any help needed still, thank you


    - Arun Gangumalla, Please mark as helpful or answered if it resolves your issue to help others in finding solutions easily.

    • Proposed as answer by Arun Gangumalla Thursday, December 13, 2012 10:33 AM
    • Marked as answer by HOG MobN Thursday, December 13, 2012 4:47 PM
    Thursday, December 13, 2012 6:02 AM

All replies

  • It may not evaluate correclty the first part: Fields!ItemPrice.Value > 0 AND Parameters!Loc.Value = "1" OR Parameters!Loc.Value = "2

    Try breaking out each conditional check:

    IIF ( Price > 0,
          IIF ( Loc = 1 OR Loc = 2 ,
                 <image1>,
                 IIF ( Loc = 3, 
                        <image2>,
                        <Image Default>) , 
                <Image Default>),
          <Image Default> )


    Todd C - MSCTS SQL Server 2005, MCITP BI Dev 2008 Please mark posts as answered where appropriate.


    • Edited by Todd C Thursday, December 13, 2012 2:33 AM
    Thursday, December 13, 2012 2:28 AM
  • Hello Hog,

    Hope you are trying these assigning of image expression under background image properties of a textbox or any other control.

    if not please assign the expression under : text box properties > background image > value >

     

    IF statement :

    =IIF(Fields!Id.Value > 0 AND (Parameters!LOC.Value = "1" OR Parameters!LOC.Value = "2"),"TNlogo",
    IIF(Fields!Id.Value > 0 AND Parameters!LOC.Value = "3","Officelogo",""))

     

    same expression in Switch statement:

    =switch(Fields!Id.Value > 0 AND (Parameters!LOC.Value = "1" OR Parameters!LOC.Value = "2"),"TNlogo",
    Fields!Id.Value > 0 AND Parameters!LOC.Value = "3","Officelogo")

     

     

    please feel free to revert if any help needed still, thank you


    - Arun Gangumalla, Please mark as helpful or answered if it resolves your issue to help others in finding solutions easily.

    • Proposed as answer by Arun Gangumalla Thursday, December 13, 2012 10:33 AM
    • Marked as answer by HOG MobN Thursday, December 13, 2012 4:47 PM
    Thursday, December 13, 2012 6:02 AM
  • Small correction to your expression:

    =IIF((Fields!ItemPrice.Value > 0 AND (Parameters!Loc.Value = "1" OR Parameters!Loc.Value = "2")),"Img01", IIF((Fields!ItemPrice.Value > 0 AND Parameters!Loc.Value = "3"),"Img02","")

    Regards,Eshwar.


    --Please use Marked as Answer if my post solved your problem and use Vote As Helpful if the post was useful.



    Thursday, December 13, 2012 6:10 AM
  • Here's how you could use the Switch:

    =IIF(Fields!ItemPrice.Value > 0,
    	Switch(
    		Parameters!Loc.Value = "1" OR Parameters!Loc.Value = "2", "Img01",
    		Parameters!Loc.Value = "3", "Img02"
    		True, "default image here"
    	)
    	, "what if price is lower than zero, no image? Then put Nothing or empty string here"
    )

    See Adding an Else to your Switch for details on the trick with the Switch statement.


    MCITP SQL Server 2008 (DEV); MCTS SQL Server 2008 (BI, DEV & DBA)


    Thursday, December 13, 2012 7:39 AM
  • The switch function worked, however, I couldn't get the report to deploy when I was using the text box/background image. I resorted to a standard image. 

    Thanks!!!

    • Edited by HOG MobN Thursday, December 13, 2012 4:49 PM Wanted to say thank you.
    Thursday, December 13, 2012 4:49 PM