SSRS Tutorial Part 5 – Configuring Parameter for Filter in SSRS – Reporting Services

Last modified date:

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

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody or Sberbank and I also used to be a bond program manager in Unicapital Investment group. 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 (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

Category: SSRS - Reporting Services

About Ing. Jan Zedníček - BI Developer, Finance controller

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody or Sberbank and I also used to be a bond program manager in Unicapital Investment group. 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 (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.