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?

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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)

Answer 5

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!!!