CODEWITHSUNDEEP
reporting-servicesparameterswhere-clause
Holonet
Holonet

Reputation: 63

SSRS Parameter values in WHERE clause

(edited to clean up)

I have an SSRS report with a multi-value parameter. What I need is for each value of the parameter to correspond to a condition in the "where" clause.

So my query is like this:

select stuff,...

WHERE

column A != column B OR
column C != column D OR...

just like that all the way down. It's to give results only if there's a difference between different pairs of columns.

So hope I'm describing it well. So, for example, if the above is my where clause, I want the parameter to have values like this:

"Difference between A & B" "Difference between C & D"

and be able to select multiple...so only the ones the user selects are incorporated.

EDIT - PARTIAL SOLUTION **********************************************

Ok I have the logic, thanks to the right direction from Hannover Fist, I came up with this:

WHERE
col 1 <> CASE WHEN CHARINDEX("Where1",@Parameter) = 0 THEN col 1 ELSE col 2 END OR  
col 3 <> CASE WHEN CHARINDEX("Where2",@Parameter) = 0 THEN col 3 ELSE col 4 END

...etc.....

This way, if the parameter is not selected, that part of the where clause looks for results where that column is not equal to itself--so none, of course...

However, one problem remains. This works in SSRS if I only choose 1 of the parameter values, but if I choose more than one, I get an error "charindex function requires 2 to 3 arguments." >-(

I deployed it and it spat something slightly different:

Argument data type nvarchar is invalid for argument 3 of charindex function

Something about SSRS's handling of this is flapdoodle, but I'm not sure what.

Upvotes: 0

Views: 3722

Answers (3)

user4329715
user4329715

Reputation: 1

When you are passing the multivalue parameter to dataset join the multivalue parameter with expression

= JOIN(Parameters!multivalue parameter.Value,",")

Upvotes: 0

Holonet
Holonet

Reputation: 63

Got it.
As edited above, this code works (helpful nudge in this direction from Hannover Fist):

WHERE  
col 1 <> CASE WHEN CHARINDEX("Where1",@Parameter) = 0 THEN col 1 ELSE col 2 END OR
col 3 <> CASE WHEN CHARINDEX("Where2",@Paremeter) = 0 THEN col 3 ELSE col 4 END

etc...

And the parameter issue is new to my experience, but put simply (which is not done often--hence my difficulty :-P), SSRS includes the commas between parameter values (which makes sense since we can use an IN statement such as WHERE column IN (@Parameter)

So that is why it complained when I selected more than one. Incorporating the excellent selected answer here:

Passing multiple values for a single parameter in Reporting Services

Solved the rest of the problem :)

Upvotes: 0

Hannover Fist
Hannover Fist

Reputation: 10880

I'm a little fuzzy as to how you're trying to do this.

How about having your WHERE clause in your SQL and use your parameter to determine whether that part of the WHERE is used? This may not be exactly what you need but should give you an idea of what I mean.

WHERE (ColA <> ColB or CHARINDEX("Where1", @Parameter) = 0)

Your Parameter would have Where1 as one of the selections (for the Value - the Label could be more descriptive). If it's chosen, the CHARINDEX result would not be 0 and the ColA <> ColB condition would need to be true to show that record.

Repeat for each parameter/WHERE combo you need.

Upvotes: 1

Related Questions