We prepared simple report in previous part (Part 4 – Creating report SSRS). It displays sales data in form of a simple table displaying results of dataset which we also prepared in previous parts. We made a promise not to make the report too complicated but we still should do one thing. Report showing only the table is not much comfortable for the user in a sense that it cannot be filtered. This article will focus on how to make a parameter configuration in SSRS, that enables result filtration.

Setting Parameter in SSRS

After last exercise, our report should look like the one in the picture:

Lets not make the assignment complicated. We will want only 1 filter above “Year” column. We will also want user to be able to roll down a menu offering year selection.

Creation of Dataset for SSRS Filter

Each filter contains some value selection. List of values can be created manually just by writing down for example year 2015 and 2016. But this is not suitable at all. We want the report to be maintenance-free. It may happen that you take care of tens of reports in your company and if you do not have these filters dynamic, it will take a lot of time to take care of each report and update its values.

Lets therefore create a dynamic dataset which will load list of all possible values while filtering the report. Remember the part of our miniseries with dataset. We used script ad to define dataset [1]. SQL query for dataset (Filter year) will logically look like this [2]. The advantage is obvious, shall the sale happen in year 2017, filter will automatically offer it.

SSRS report tutorial - dataset, sql script
[1] Dataset definition
SSRS report tutorial - date filter sql dataset
[2] SQL query for filter dataset





Copy the query and create new dataset in the report [3], [4]

[3] New dataset for the filter
[4] This is how it should look like






Parameter Definition in Reporting Services

In the moment when we have ready the dataset for Year filter, we can proceed to configuring the parameter. Parameters are established from Report data card. Select Parameters, then select Add parameter [5]. Name the parameter on the next screen and select Integer data type [6]. Click on the Available Values in the nect step and choose Get values from a query [7]. Finally confirm the parameter configuration. After clicking on Preview, report requires us to fill in filter year [8].

Nastavení parametru v SSRS
[5] New Parameter
[6] Parameter data type configuration








[7] Connectiong parameter to dataset
[8] Result after successful parameter configuration








Connecting Filter with Main Dataset

Successfully configuring parameter in report does not mean that the main dataset reacts to parameter and returns Sales by product. Binding must be configured. It is not hard at all. Just edit main dataset. Type into SQL query’s WHERE clause simple requirement. It will limit the result to whatever is currently selected in the filter.

You can easily link to the parameters in Reporting services SQL query. Parameter of SQL query is defined via @NazevParametru [9]. Then click on card Parameters and bind both values as shown in the picture. [10]

[9] Connecting parameter with main dataset
[10] Finalizing binding of main dataset to parameter






And it is done. SSRS parameter configuration is complete. Report should react to filter without any problems.

Nastavení parametru v SSRS

Next part will be focused on Deploy of SSRS Report to Report server so users can easily browse through it –  Part 6 – Report deploy in SSRS

Rate this post

Ing. Jan Zedníček - Data & Finance

My name is Jan Zedníček and I work as a freelancer. I have been working as a freelancer for many companies for more than 10 years.
I used to work as a financial controller, analyst and manager at many different companies in field of banking and manufacturing. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums.
I am trying to summarize all my knowledge on this website not to forget them and to put them forward to anyone.

Leave a Reply

Your email address will not be published. Required fields are marked *