SSRS: Dropdown Filters

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. .

1-dropdown-filters

2-dropdown-filters

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.

3-dropdown-filters

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

4-dropdown-filters

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

5-dropdown-filters

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.”

6-dropdown-filters

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.

7-dropdown-filters

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

8-dropdown-filters

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.

9-dropdown-filters

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.

10-dropdown-filters

Now my report is ready to view.

11-dropdown-filtersDownload Report
(right-click, Save As)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s