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.

 

 

 

 

 

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

 

 

 

 

 

 

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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]

 

 

 

 

 

 

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

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