Multiselect filters and parameters

[ookete]

Does anyone know if you can create multi-select report parameters? So instead of a user filtering by just "Product A" for example, you could select "Product A" OR "Product C" OR "Product F". This is like the difference between a combobox (single) and a multi-select listbox. I usually do this type of thing with my own custom interface, but I'd love to use the standard Reporting Services parameter interface for this project.

Thanks!

 

[scriv]

If I understand your question correctly, I believe that in the Design "Layout" window you need to go to "Report" on your menu bar. Click "Report Parameters". In the "Report Parameters" window click on "Add" and fill out the upper "Properties" portion of the window. Next look on the middle portion of the "window where "Available Values" are shown. If you are going to set up your parameter selections in the report, you should select the "Non-queried" radio button, then assign a "Label" and "Value" to your selection list. You may need to play around with it a bit to get it to work right.

 

[ookete]

scriv,

Actually, I've done that part. I have parameters where I have filled in "non-queried" lists and some where I have queried lists. What I want is the user to be able to select more than one of those values at a time when they run the report. So for example, let's say I have a Product parameter. In my Report Parameters, I check "non-queried" and fill in the list like so:

Label       Value
Product A   A
Product B   B
Product C   C
Product D   D

At runtime, the user should be able to select products A and C, or B and D or whatever they want and filter accordingly. I am looking for a "multi-select" parameter option that allows filtering on more than one value at a time.

 

[Catadmin]

Ookete,

The bad news is, I have yet to figure out how to choose multiple parameters in a Combo-box kind of way. The way RS has worked for me is that it only allows one choice out of the drop down list. It re-renders the report every time I make a choice on a parameter list, unless I leave an extra param without a default value that I have to choose each time.

The good news is, there may be a way around that. Since the report pops up in Visual Studio, you may be able to add a Combo-box to it. Not sure on that, though.

Another way would be to give the users a number of parameters to choose in advance. Like, User X could choose up to 3 products to view on or choose only a single product to view on or choose All Products to view on.

You would have to add an extra param. This param would have "Choose Single" or "Choose Three" or "Choose All" options. Then, based on the choice in this param, create a dataset with a Case or an If/Else statement that refers to the appropriate stored procedure (I did a nested If/Else - with 3 checks).

Choose All would trigger a proc that would do a "Select *" without conditions. Choose Single would trigger a proc that would do a "Select *" based on Param1. Choose Three would trigger a proc that would do a "Select * ... Where Col1 = Param1 or Col1 = Param2 or Col1 = Param3" kind of thing.

Does this make sense?

I'll check around and see if anyone else has managed combo boxes in RS. I'll post if I find anything.




Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[ookete]

Thanks Catadmin,

I am sorry to hear about the lack of multi-select, but I suspected as much. Also, thanks for the ideas.

What I end up doing is create a separate application as the report portal, and make some multi-select listboxes for the input parameters. Then when the user hits "View Report", I concatenate the selected items in the list into a comma-separated string... like "Product A,Product C,Product F". I pass this string to my stored procedure, parse out the elements and filter by them using an IN statement

SELECT OrderNumber
FROM Order
WHERE Product IN (<multi-select list>)

That way they can select one, two, or twenty and it behaves the same way. But I sure would love it if the built-in functionality of RS was able to understand multi-select parameters to start with.

 

[Catadmin]

Ookete,

Check out this link. It may help you.

http://www.15seconds.com/issue/041027.htm

I just found it when looking for Combo boxes. I'm reading the part where it has a calendar control added into it. That part might help you with the multi-select (Adding the ReportViewer.dll into the toolbox). Not sure what other options it gives. If you can't find it at the path listed, you'll have to open the ReportViewer.sln in Visual Studio, build the solution and then you'll find the .dll in the %SysRoot%\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\Samples\Applications\ReportViewer\vb\obj\Debug path.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[Catadmin]

Okay, so my above idea bombed out. However, I just discovered that multi-select is supposedly coming out in a future release of RS.

Someone suggested, in a post on Microsoft's page, that a text box can be enabled which takes a user-typed, comma delimited list character value which can be inserted in an IN() function in the query. Another suggestion was:

...if you have some SQL
skills you can create a function in SQL Server to take a comma separated
list and return a table for use in a query.
Check this out:

http://www.windowsitpro.com/Article/ArticleID/26244/26244.html?Ad=1

Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???