SSRS IIF Statement with LOOKUP

Question

User-2112117666 posted

It is my IIF statement

=IIF(Year(Fields!Date.Value) <= 2015 AND Month(Fields!End_Date.Value) <= 02 AND Lookup(Fields!Project.Value,Fields!Project.Value,Fields!company.Value, "ds_Projects")="ABC", "Available", "No")


when i run report i get #ERROR in my table cells but when i run my expression without Lookup Fields it run good... but lookup fields are necessary plz help me

Answer 1

User-830595639 posted

Hi,

Your expression looks correct to me, just check wheather your Lookup is running fine independently or not, if its run fine and then  also your expression gives error then try to put your lookup expression in a textbox and use this text box in your original expression.

I hope this will help.

Answer 2

User-2112117666 posted

Hi,

Thanx for reply...Nishantg

my Lookup function running well,.. but i am new in ssrs plz can u explain your answer which about textbox... plz

Answer 3

User-2112117666 posted

 in fact i am using two IIF statement with "OR" operator in my single expression and both IIF have lookup functions... when i delete my one IIF statement then it work good but when i run both IIF together in single expression it is not working... i think now you are understanding my problem

=IIF(Year(Fields!Date.Value) <= 2015 AND Month(Fields!End_Date.Value) <= 02 AND Lookup(Fields!Project.Value,Fields!Project.Value,Fields!company.Value, "ds_Projects")="ABC", "Available", "No")

OR
IIF(Lookup(fields!a.value, fields!a.value, fileds!bill.value, "ds_projects")="xyz", "available", "No")

Answer 4

User-830595639 posted

Ok, so if both of your expression are running fine separately then try to do like this.

1. take two different text box in your report. 

2. Put both expression separately in both text boxes.

3. Then write a final expression like below

IIF(ReportItems!Textbox1.Value ="ABC" OR ReportItems!Textbox2.Value = "XYZ","Available", "No")

Here Textbox1 and Textbox2 are the name of the text boxes.

Hope this will help you.