SSRS - IIF 1

[Cobby1812]

Hi All,
I am trying to create a group based on certain informtation as shown below - the first group will create a different group header for each value it finds i.e it would create one if it found "Client Take on" one for "Legal" and one for "Offer Accepted"

What I want it to say is of its in this ("Client Take on","Legal","Offer Accepted" THEN "Accepted Offers/Legal"


However I cant seem to get this resolved. I cannot (alas) do in SQL as this is using a very old Lotus Notes Database to retrievethe information.

=IIF(Fields!Prospect_Stage.Value = "Client Take on" OR Fields!Prospect_Stage.Value = "Legal" OR Fields!Prospect_Stage.Value = "Offer Accepted", "Accepted Offers/Legal" ,
IIF(Fields!Prospect_Stage.Value ="Acceptance","Acceptance",
IIF(Fields!Prospect_Stage.Value ="Offer","Offers Made",
IIF(Fields!Prospect_Stage.Value ="Proposal", "Proposal",
IIF(Fields!Prospect_Stage.Value ="Survey","Survey",
IIF(Fields!Prospect_Stage.Value ="IOT", "IOT",
"99 - Other Stages"))))))

 

[kray4660]

Nested iif's can be difficult to work with. I believe there is a SWITCH function in SSRS. It has been a while since I looked. Are you getting an error message or is it just not working correctly?

 

[Cobby1812]

Hi Kray4460
No error just wrong result set - in this one IIF(Fields!Prospect_Stage.Value = "Client Take on" OR Fields!Prospect_Stage.Value = "Legal" OR Fields!Prospect_Stage.Value = "Offer Accepted", "Accepted Offers/Legal" I get three different Group Headings - all the correct name but only want one - I will have a look at Switch though thank you

 

[Cobby1812]

kray4660 you a star thank you that all worked perfectly