SQLTeam.com | Weblogs | Forums

Report Builder/SSRS IIF inside a SWITCH

sql2008r2

#1

Hello all!
I'm trying to add some text to a form's Term Of Service agreement field. Currently, we have 2 TOS that are generated based on a given parameter. There's some slight differences between the laws for both countries which is why it's split up this way. We want to expand on the existing logic to add more text IF these criteria are met.

To give you an idea of what it looks like:

=SWITCH(Fields!Origin_Country.Value, "DataSet1") Like "CAN",
" PRINTS CANADIAN VERSION OF T.O.S" 
& vbCrLf &
"More Text...",   --comma separating the =SWITCH Objects.

**Want to add IF statement here to expand on Canadian TOS based on Commodity**

(Fields!Origin_Country.Value, "DataSet1") like "USA",
" PRINTS US VERSION OF T.O.S"
& vbCrLf &
"More Text...")   --This is the closing bracket for =SWITCH.

**Want to add the same IF Statement here to expand on US TOS based on Commodity**.

The additional logic i want to bring will check multiple parameters. I realized i can't use IN and will either have to add a Join(", ") somewhere to delimit it, or will have to write multiple IF Statements with the same True result. Either way is fine.

I basically want to add something like :

=IIF(Fields!commodity_code in ('OPT1','OPT2'.'OPT3'), " ADD THIS THIS TEXT BELOW THE T.O.S", " " )

I want the False result to either not bring in any text at all or just add a space character if no other option.

I tried playing around with this for an hour or so and kept getting different errors...


#2

Got this expression to work!

So I was simply placing commas in the wrong place and probably messed up the syntax the first few tries too...

So it looks like this now:

=SWITCH(First(Fields!Origin_country, "DataSet1") like "USA",
"***USA Version of TOS****"
& vbCrLf & "More text Here"
&vbCrLf &
IIF(First(Fields!cmd_code.value, "DataSet1") like "PRO*", "USA PRODUCE TEXT", IIF(First(Fields!cmd_code.value, "DataSet1") like "SEAFOO*", "USA PRODUCE TEXT", " " )),

First(Fields!Origin_country, "DataSet1") like "CAN",
"***CANADIAN Version of TOS****"
& vbCrLf & "More text Here"
&vbCrLf &
IIF(First(Fields!cmd_code.value, "DataSet1") like "PRO*", "CDN PRODUCE TEXT", IIF(First(Fields!cmd_code.value, "DataSet1") like "SEAFOO*", "CDN PRODUCE TEXT", " " ))
)