Providing filters to report users.

This article demonstrates providing dropdown choices to users to use as filters on the report.

First I’ll start with two sprocs, one to populate choices for the filter, the other to populate the report based on the user’s choice in the filter. .

Now I’ll create a new report, and drag a table unto the designer.
When prompted to create a dataset, I’ll name it and create a new datasource, for the query type: Stored Procedure and select the sproc which populates the ds based on my filter choice.

Also, I can use the query designer to test my sproc by providing a value for the @country parameter.

By clicking on parameters, I’ll see that it detected my @country parameter from the sproc.

Next I’ll access my @country parameter on the report pane and open it to change the prompt given to the user to “Enter Country.”

To populate the filter with choices to the user can simply select one from a drop down, I’ll create a new dataset and use my other sproc to populate it with all possible choices for countries.

Returning to my @country parameters, I’ll configure it to populate its choices based off of my new dataset and second sproc.

Also, I don’t want the user staring at a blank report when it first loads, so I’ll have the filter select ‘USA’ by default.

Next, I’ll drag fields from the dataset unto the design surface to populate it.
Finally, I’ll add a header to the report and in it I’ll place a textbox.
For the textbox I’ll use an expression to display the currently selected filter.

Now my report is ready to view.

Download Report
(right-click, Save As)