Filtering a Parameter with a Long List of Values in SSRS

Filtering a Parameter with a Long List of Values in SSRS

I've been developing quite a few reports in Reporting Services lately (as you may have noticed from the blog). While showing one of the reports to my user, my user asked me to field he wanted me to add to the report had a very long list of values to it. Reporting Services gives parameters only the option of a drop down list. I knew that using a drop down list on a field which had so many values in it would mean that my user most of the time wouldn't find the value he's looking for. Even more so, my user was looking for something more in line with what he had for the very same attribute in his report builder, which was a list object that also enabled him to filter just the members of the attribute that had names or numbers he was looking for (the attribute had its name and code showing in its description):

So I admit I was a bit stumped as what to do. My boss sat with me the day after and we talked about the report and came up with a suggestion "Ella, you use one parameter to filter on another parameter in that report (giving only the existing months for the year chosen). Why not use the same sort of logic here? You could use one parameter for him to filter on the code or name of the attribute and in another parameter that relies on it, just give him the values that have the string he was looking for. Go see if it can work". Well Hamada, it works! So thank you for the suggestion :)

I'll demonstrate what I did with an SSRS report on Adventure Works DW.
Adventure Works has a long list of products in it. What if we wanted to give our user a report about the sales of all those products, but also a chance to filter just on specific products?
First I defined a data set that simply showed all the data I wanted (including the product name field). I created a matrix with groups on it, seeing to it that the matrix headers would show wherever you would scroll in the report.
I added two parameters to the report. The first parameter would have no data attached to it, and would simply exist to filter on the product name if the user chooses to do so:

The default value for the parameter is null, if the user wants all of the products.

The second parameter has two datasets which it relates to. The first dataset gives the user all the product names which have part of the string he looked for or all of the products if he doesn't want to filter. The second dataset for this parameter defines that the default value for it is all of the products:

This parameter I also defined as Multi Value in case the user wants all the list of products containing the string he was looking for, and not just a single product.
I added the parameters to my original dataset so that it would filter correctly and we get the original report with all the products at first:

Or a filtered parameter with a shorter list of values to choose from:

Which gives us the filtered related matrix:

So yes, I didn't create a list object instead of a dropdown object, but I think the solution is quite simple for filtering a long list of value and it gives all the functionality needed. Most important of all - my user likes it!

Finally, I'm enclosing the report with the parameter I made in SSRS for your viewing pleasure: ReportParameter