0

(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.

Holonet
  • 63
  • 1
  • 9
  • Can you provide your *actual query* in your question please, or at least the actual where clause you want to have? – sasfrog Jul 24 '15 at 06:57
  • Okay. I can't think of a way to do this using one multi-select parameter off the top of my head. How many comparisons are there, and is it always a fixed number? – sasfrog Jul 24 '15 at 09:10
  • It's about 20 comparisons, and no, not a fixed number. It's checking for data that was entered in multiple fields, to see if there are discrepancies in particular steps. All the data per document is in one row...so essentially I'm selecting a single row and looking for differences in whichever column(s) the user chooses (each field that is keyed has a different column for someone else keying it). Might be doable with some ugly pivoting, but I'm fairly certain that would kill both my brain and the performance hehe. – Holonet Jul 24 '15 at 16:53

3 Answers3

1

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.

Hannover Fist
  • 10,393
  • 1
  • 18
  • 39
  • That's helpful...had not thought of that, and it's mostly right on for what I'm trying to do, but one problem. I'm using OR between all my combos. This would work if I were using AND, but I want it to show when any of the selected combos are true, as opposed to all of them. As it is, if they don't select a single parameter, it would be like saying WHERE col A <> col B OR charindex(etc...) = 0, meaning it would give me everything. – Holonet Jul 25 '15 at 02:49
0

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 :)

Community
  • 1
  • 1
Holonet
  • 63
  • 1
  • 9
0

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

= JOIN(Parameters!multivalue parameter.Value,",")
Erik Godard
  • 5,930
  • 6
  • 30
  • 33
user4329715
  • 1