0

I have a project that I need to add a param to that accepts multiple values. While this should sound pretty easy it is not working.

Here is the param: @queue_group varchar(100)

And here is how it is used: and g.extension in (@queue_group)

Now when I manually do it like so: and g.extension in ('700', '702') it works just fine. I have to make the numbers a string because of how the column was setup it has some string values in it such as *86 that breaks when I just do 700, 702.

Now I set up the param in SSRS giving it available values and allowing multiple select but every time it doesn't work because it can't find any results. The ultimate error is a divide by zero which is because it isn't finding any records.

How can I make SSRS mimic my manual behavior of '700', '702' which when I remove the param and enter that manually it runs just fine.

In SSRS I've tried Available Values CS = 700, CS = '700', CS = =Cstr(700) and CP = 702, CP = '702', CP = =CStr(702).

I've now tried setting up a table and using that as the defined values for the parameter. It now works when I select a single option and returns results. whenever I select 2 it returns me 0 results.

I'm at a loss.

James Wilson
  • 5,074
  • 16
  • 63
  • 122
  • Possible duplicate of [Parameterize an SQL IN clause](http://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause) – Lukasz Szozda Nov 30 '15 at 16:30
  • What is the parameter type in SSRS? – D Stanley Nov 30 '15 at 16:35
  • @DStanley It is set to Text. – James Wilson Nov 30 '15 at 16:38
  • Unless you have casing or trailing whitespace issues it should work as-is. SSRS injects the vales to the IN caluse as a comma-delimited list (meaning the suggested duplicate answer does not apply) – D Stanley Nov 30 '15 at 16:44
  • Are you declaring @queue_group in your query? – alejandro zuleta Nov 30 '15 at 16:45
  • @DStanley yeah I agree on the duplicate answer. I thought so as well but out of all of the ways I have tried none have given me results. Right now I am trying to set up a new table with the info and setting the column to varchar and see if that makes a difference if i grab the values from a table. – James Wilson Nov 30 '15 at 16:46
  • It is setup as a param, not declared anywhere else outside of the params. @alejandrozuleta – James Wilson Nov 30 '15 at 16:47
  • It's not a duplicate, there is SSRS specific functionality that allows multivalue parameters to be used in line with no special hoops to jump through. Check my proposed answer. – Randall Nov 30 '15 at 16:56
  • What is the **VarChar(100)** you mention? `@queue_group varchar(100)` It sounds like you are DECLAREing it in the query - though you say you don't (and shouldn't). For the available values, SSRS uses **double quotes** for strings - `="700"`. I don't understand the `CP =` part though - it sounds like you are using the **FILTER** tab. :s – Hannover Fist Nov 30 '15 at 17:17
  • @Randall I've updated the question. I can now get it to work if I just select a single option. As soon as I select 2 it fails. I select 700 it will return results for that queue, I select 702 it will return results for that queue. I select 700 and 702 and it returns me 0 results. – James Wilson Nov 30 '15 at 17:17
  • @HannoverFist I have to declare it as a parameter in the stored procedure. I don't have to use DECLARE for this, it simples needs to be put at the front of the stored procedure in the params section. The cp = is the available values i setup "cp" as the name "700" as the value. I wonder if the double quotes is messing it up somehow will have to try that in my stored procedure and see if it chokes. – James Wilson Nov 30 '15 at 17:19
  • @JamesWilson Yeah that goes right along with what I think is the problem, which is the issue being how SSRS is formatting your text string. – Randall Nov 30 '15 at 17:25

1 Answers1

1

SSRS can support multi-value parameters without much extra work so long as the dataset is in ssrs.

Here is a link to the tech net on it: https://technet.microsoft.com/en-us/library/aa337396(v=sql.105).aspx

What happens is that SSRS passes the whole query to the sql compiler, this means that the replacement of the parameter with the multi-value string happens before compile. So I don't think that's the problem, because If it were an error in the way the multi-value param was presenting the data to the compiler it would not be a divide by zero.

Instead I'm thinking this has to do with how the parameter is populated. You have the param set to a text field, so people are expected to enter their own values. However SSRS handles the formating for a text field before it's sent to compile, and I'm guessing that's what tripping you up.

If you enter "402,407" without the quotes into the param at compile time it will be transformed into:

and g.extension in ('402,407')

That would not return any results. If this is just a report you use, you could enter: "402','407" without the double quotes and at compile time it would transform it too:

 and g.extension in ('402','407')

The better solution would be to create a data set for the param, set the parameter to use that data set as it's list of available values, and check the multi-value property for the param. That way you would select multiple values from a drop down, and the user wouldn't have to mess with the string formatting.

Randall
  • 1,441
  • 13
  • 19
  • I would think this should just work, but yeah I'm guessing SSRS is trying to be smart about it and really messing it up. I tried the dataset option and it works the same way. One value selected returns results but multiple values selected returns 0. I even went in an fixed all of the divide by 0 errors just to rule that out. I think at this point I may just have to duplicate the report 5 times and manually put in the values.. Which seems ludicrous. – James Wilson Nov 30 '15 at 17:31
  • Yeah that is odd, so you set up the parameter to use the values from a dataset and then set the parameter to be multivalue, but when you select multiple values from the parameter drop down it gets weird results. does it work if you change the parameter value to be an integer? – Randall Nov 30 '15 at 17:38
  • 1
    Yep I tried that as well. Right now I set up two params queue_group1 and queue_group2, changed queue_group2 to allow null. And now if I select one for group1 and none(null) for group2 it works. and likewise if I select something for group1 and group2 it works. Think I might have to leave it this way. Better than duplicating it 5 times i think. Sometimes I love SSRS and sometimes....... – James Wilson Nov 30 '15 at 17:46