Nested Iif expression...

Question

Post reply

Nested Iif expression...

latingntlman

SSCarpal Tunnel

Points: 4348
More actions
August 25, 2008 at 1:05 pm

#191813

I'm trying to figure out the syntax on an RS expression, but keep getting compile error msgs. Example below:
If Title value = "Director" OR Title value = "Mgr" AND IF SupervisorName value = "Jane Smith" THEN "Las Vegas" ELSE IF SupervisorName value = "John Doe" THEN "Ft. Laud" ELSE SupervisorName Value
ELSE
IF Title value <> "Director" AND Title value <> "Mgr" THEN
"No Manager"
What would be the RS syntax for a nested if statement?
thx,
John

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Adam Angelini SSCrazy Eights Points: 8548 More actions · Answer 1

try using the CASE/WHEN clause, it's well documented in BOL

toolman-352714 SSCrazy Points: 2495 More actions · Answer 2

John,

Try using SWITCH(). It looks at pairs of comma delimited conditions (first half of pair is check condition, second half is true result) and selects the result based on the first true check condition it encounters.

Here are your conditions organized into a SWITCH expression.

=SWITCH(

(Title value = "Director" OR Title value = "Mgr" ) AND SupervisorName value = "Jane Smith" ,"Las Vegas" ,

(Title value = "Director" OR Title value = "Mgr" ) AND SupervisorName value = "John Doe", "Ft. Laud" ,

(Title value = "Director" OR Title value = "Mgr" ), SupervisorName Value,

Title value <> "Director" AND Title value <> "Mgr", "No Manager"

)

[font="Comic Sans MS"]toolman[/font]
[font="Arial Narrow"]Numbers 6:24-26[/font]

latingntlman SSCarpal Tunnel Points: 4348 More actions · Answer 3

Never heard of the SWITCH function but I'll give it a go.

BTW, what's BOL?

thx fellas.

Adam Angelini SSCrazy Eights Points: 8548 More actions · Answer 4

BOL is books online (just the help documentation)

I had never heard of switch either.

toolman-352714 SSCrazy Points: 2495 More actions · Answer 5

Documentation for SWITCH() is here

http://msdn.microsoft.com/en-us/library/dft2z9yf(VS.71).aspx

VB functions in general here

http://msdn.microsoft.com/en-us/library/32s6akha(VS.71).aspx

Happy hunting!! 😀

[font="Comic Sans MS"]toolman[/font]
[font="Arial Narrow"]Numbers 6:24-26[/font]

jais.ghan-823166 Old Hand Points: 322 More actions · Answer 6

Hei,

Reporting server supports conditional statement 'iif' only...not if..

iif(Condition,value if true,vlaue if false)

gardenlady SSCommitted Points: 1974 More actions · Answer 7

RS itself, not SQL Server, writes in VB.Net syntax. If you know that, it makes life easier because you can search for help in Google or books. I use IIF too.

ppreston Mr or Mrs. 500 Points: 588 More actions · Answer 8

I believe you would want your syntax to look something like I have listed below;

=iif(Fields!title.value = "Director", "Las Vegas", "Fort Laud")

the "Fields!title.value" is the text box on your report that has the data you want to evaluate, Even though this is just looking at one value. You should be able to nest the conditional "IIF" statement to evaluate multiple statements. You can find the syntax in books-online under "Adding Conditional formatting".

Good Luck!

wernerlouwmail SSC Journeyman Points: 77 More actions · Answer 9

wernerlouwmail

SSC Journeyman

Points: 77

March 22, 2010 at 8:33 am

#1137617

Hey Toolman, I like that script lookup, very good!