In the last article we prepared a simple report returning all rows from SQL Server dataset (Part 4 – Creating report SSRS). It displays sales data in form of a simple table. We made a promise not to make the report too complicated but we still want to do one important thing. Report returning the table with all rows is not user friendly because it cannot be filtered. This article will focus on how to make a parameter/filter configuration in SSRS, that enables filters.
Setting Parameter for filter in SSRS
After last article, our report should look like the one in the picture (sorry about a czech labels – original post is written in Czech – it contains Year, Month, Product, Product category, Product subcategory, Revenue amount, Numer of orders)
Lets keep things not complicated. We want to use “Year” column as filtr in way user can to roll down a filter list and then select year.
Create dynamic SSRS Filter
Each filter contains some value selection. List of values can be created manually just by writing values. But this is not sexy at all. We want the report to be dynamic. 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.
Let’s 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] just a distinction of year column.
Configuring Parameter Definition in Reporting Services
Year filter dataset is ready, we can proceed to configuring the parameter. Parameters are accessible 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 SSRS Filter with Main Dataset
Now we have a filter dataset offering values dynamically. The final step we are going to do is to connect the parameter to our main dataset = table. But it is easy. Edit main dataset and add some code into SQL query section as shown below. It will limit the result to whatever is currently selected in the filter (@Rok is the name of our filter) [9].
Linking Parameter with a SQL query is defined in dataset properties – @ParameterName Click on card Parameters and link both values both values together as shown in the picture. [10]
Next part will be focused on Part 6 – Report deploy in SSRS
