Can I reference a param in my Query Where statement? 1

[dianemarie]

Hello, in Crystal, we often drop records conditionally in the record selection by referencing a parameter. Example:

If {Include Canceled Orders?} = "No" then {ordhist.cnx} = 1 else {ordhist.cnx} = 0

Any guidance in how I would go about duplicating this scenario in SSRS? (Allowing the user to select via a parameter whether or not to include canceled orders on the report.) Thank you.

 

[ThomMenzies]

You need to use the IIF statement:

IIF(Expression,TruePart,FalsePart)

Thus, in your Crystal example:

IIF({Include Canceled Orders?}="No",1,0)

 

[dianemarie]

Thanks for getting back but I'm not sure we're on the same page. In Sql Reporting I don't believe I can do any kind of If/Then/Else (or iif) statement in the dataset query. We do a lot of conditional record selection, based on a user's input through a parameter as described in previous post for showing canceled orders (or not showing them).

I'm trying to figure out how to transfer this functionality to SQL reporting. Is this where I would create two additional datasets? One that includes canceled orders and one that doesn't? Again, any guidance on this would be appreciated.

 

[andrea96]

You can do a CASE function in the WHERE clause of your query. Here is a simple example; yours may be more complex, depending on what you are doing.

WHERE (CASE [i]parameter[/i]
	WHEN [i]condition[/i] THEN 1 ELSE 0
       END) = 1

 

[dianemarie]

Hi Andrea, I think this is what I need but I'm having trouble. Why do you have =1 at the end of your statement, do I need that?

This is what I have but it isn't working. Can you see what the problem is? @CNX is the parameter.

(CASE @CNX
WHEN 'Y' THEN oh.cnx is not null ELSE oh.cnx is null
END)

I'm getting: Incorrect syntax near the word "is". Not sure if I'm not doing the Is Not Null correctly, or if it's some other problem with the Case statement. Thanks for your help.

 

[dianemarie]

Hey, I got it to work! (Which always surprises me.) I put it in the Select part of the query (after researching CASE in Help):

(CASE @CNX
WHEN 'Y' THEN oh.cnx ELSE ' '
END) as cnx,

Thanks for pointing me in the right direction Andrea.

 

[andrea96]

You're welcome.
The =1 at the end was just something I was using to test for a true or false.