Reporting Services - Parameters AND Filters nugget

I've just watched a video nugget from an exam video pack made by CBT Nuggets.

It's quite good (I've watched 3 of the 15 videos so far so my opinion is based on those 3) if you are preparing for a SQL Server BI exam and you don't have real life experience in e.g. Reporting Services. I did not :)

Based on Video2 which describes Report Parameters and Filters you can use in a report. I don't go into the details since it's straightforward. You can use a Parameter to limit the results on the database side and Filters to do the same on the client side. Of course it's better to use Parameters but sometimes it's not possible.

One other advantage of Parameters is that you can provide an empty text field or a drop down list to the user thus limiting the dataset:

 

I wondered if you can do the same with filters... because the nugget shows only how a developer can create a filter in the designer. But I think it's just as useful to create interactive filters as interactive parameters.

I googled it but no results (though I have to admit I didn't try it too hard :))

So you can do it in two steps:

  1. create a parameter (yes, not a filter!): Report Data pane, right click parameters, Add parameter. Give a descriptive name, you can give available and default values if you want (for a drop down list you must) and press OK.
  2. by creating a filter (right click on the dataset, Dataset Properties)
    • choose Filters
    • for the Expression field, pick the column you want to use as a filter
    • for Value click the fx sign right from the field that lets you specify an expression
    • in the new window choose Parameters in the lower left part (under Category title) and double click onto the parameter name on the right.
    • OK and OK

Try your report in Preview mode and it should work now! If not feel free to flick me an email to martinisti@gmail.com!

Published Monday, February 22, 2010 4:44 AM by MartinIsti

Comments

No Comments
Powered by Community Server (Commercial Edition), by Telligent Systems