Filtering a Parameter with a Long List of Values in SSRS

March 9, 2009

7 comments

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

Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

7 comments

  1. NancyJuly 28, 2009 ื‘ 22:54

    This looks very similar to something I am looking for. I have a report, from one entity I am pulling the data based on the Reference Number field, but I have a second entity created (MSCRM) for the products related to the Reference Number above, I created a list, but when I run the report it is displaying the list with false for the values which is true because there are not products associated with the field in question, but what I am looking for is that the list not show any values if there are none, do you have any suggestions?

    Reply
  2. Coy BernardoOctober 27, 2009 ื‘ 2:46

    Hey Ella,

    I liked your blog and I like the solution, but I did have an issue trying get this solution to work for me…

    In the first parameter that captures the text from the user, are there more things you need to do to pass the inputted text to filter the available values of the second parameter?

    I followed the screenshots you put up…but still couldn’t get it to work…any suggestions…your help would be greatly appreciated…

    (and your blog’s on sharepoint right? looks great…)

    Thanks,

    Coy

    Reply
  3. Ella MaschiachOctober 27, 2009 ื‘ 10:19

    Hi Coy,

    First of all, I deleted your email addresses from the comment text, so as to keep your privacy.

    I havenโ€™t added any additional data to the text passed from the first parameter to the second.

    I think that even better than following the screenshots would be to create an SSRS project and define for it a data source of Adventure Works. Next, download the report I created (available in the last row of the post), save it on your PC and add it to your SSRS project. Now, try to follow the screenshots. I think theyโ€™ll make more sense if you work on them against the original report and then try to implement the same idea for your personal report.

    And thank you for the compliment, but my blog is about BI (mostly SSAS and SSRS).

    Please feel free to check in again if youโ€™re having problems.

    All the best,

    Ella

    Reply
  4. valenMay 27, 2011 ื‘ 15:44

    hi Ella, is very interesting your post, I tested on my own DB, well, I tried VS my cube, against a dimention too big as products, te problem is that the report gets freeezee processing all the dimention before get the input parameter value, aparently load all the dimention before process the report :S do you have any sugestion?

    thanks!! ๐Ÿ˜€

    Reply
  5. Ella MaschiachJune 19, 2011 ื‘ 17:54

    Sorry Valen, but I’m stumped. Perhaps filter on a higher level (like the product line rather than the product themselves). Else, try your luck at the Analysis Services Forum or the Reporting Services Forum.

    All the best,
    Ella

    Reply
  6. Frikkie VenterApril 24, 2012 ื‘ 11:20

    Hi Ella

    Nice Solution, This has been a requirement for some time for me in SSRS, before your solution I used to switch over to something like Qlikview for report delivery.

    I do have a question though, my FILTERED parameter list is multi-value, so the user can input a string into the FILTERING parameter, and select a value from the subset returned in the FILTERED parameter.

    But the requirement is to be able to enter a string a second time, now the subset of available values obviously gets re-filled and the previous value that was selected gets discarded.

    Can you think of a way around this?

    Thanks

    Reply
  7. Ella MaschiachApril 29, 2012 ื‘ 16:29

    Hi Frikkie,

    I could think of two options:

    1. Leave the report as is and let the user run the filter with the one word he remembers (for instance โ€œtyreโ€). After seeing the relevant list he can retype the new name he wants (โ€œtube tyreโ€) and the list shows the new relevant values.
    2. You can add another parameter to check called โ€œProduct Name 2โ€ and update the value of the product to check both for the word in Product Name and in Product Name 2.

    Hope that helped,

    Ella

    Reply