Parameters allows the users to control the report data, it filters the report dataset based on value provided to parameter using a text box.
In general, when user enters a specific value in the textBox then, SSRS filters the Report data based on value provided by user.
Lets see the step by step implementation of parameters in SSRS.
You can see, below is the sample report named RptProductDetails.
Lets see the the preview of report as shown below.
[Also Read: Multi-value parameters in SSRS]
Lets create a parameter, that will take a user specified value through textbox and filter the report data for Class.
Once you click on Add parameters.., you will see a Report Parameters properties window opens, here you have to provide a details for parameters which are as follows.
- Name: specify the valid Parameter name, we have specified PramClass.
- Prompt: specify a short text that will display as a label before the text box.
- Data Type: as class name is a Text data type so keep data type as it is, set to default Text.
You can leave remaining options set to default for now, and click on OK button.
Once you click on OK button, you will see a parameter is created inside the Parameters folder. You can also see the parameter in report Preview.
As parameter has been created but report data will not filter yet. You can see when you enter a value in text box for class and then click on View Report button, it does not filter the report data for specified class value you provide.
So to filter the data report data based on parameter value, you need to apply a filter condition on Dataset.
Lets go to Datasets folder, click on Dataset, here we have a dataset named Dataset1 then select Dataset Properties from context menu.
Once you select a Dataset Properties, a DataSet Properties window opens.
Now, in Filters tab, then click on Add button
Once you click on Add button, it asks you to provides a filter conditions.
As we want to filter report data for class whose value is going to be provided by user through parameter, so in expression option, we select a Class.
In Operation option as we want to filter the report data for class value which are exactly matched with user specified value so we keep the operation as default that is = (equal).
In Value option, specify a value that will be taken through parameter, just click on expression icon (fx).
You will see a Expression window opens, now in category section click on Parameters.
Once you click on Parameters you will see the list of all the available parameters in Values selection, as we have created only one parameter that is Class so it displays only class parameter there in Values section.
Now to providing a expression to values just double click on Class parameter, and you will see it automatically creates an expression for value.
After that just click on OK button, now you get back to Dataset Properties window, there you can see now filter condition are specified.
Next, just click on OK button.
Now go to report preview to check the implementation of parameter, and filter the report data for class value L.
You can see, it filters the report data for class value L.
Lets create one more parameter that will filter the report data for Name. This time report data is filtered for Name using a LIKE operator.
For example if user provides a parameter value for Name parameter as “Re” than it filters the report data for all the Name values whose contains text “Re”.
Again, we will create a new parameter same as did earlier, go to parameters folder right click and select Add New Parameters..
After that, right click on dataset, then select DataSet Properties.
After that, a Dataset Properties window opens, now we will provide a filter conditions.
Now, Click on Add button, to add filter condition options.
Once you click on Add button, after that select select expression as Name, operation we select like as we want to filter report data for partial match.
Now in Value option, click on expression icon (fx), you will see a Expression window opens.
Now click on Parameters in Category section, you will see it displays all the available parameters that you have created in values section.
Double click ParamName parameter, and it creates an expression for value automatically as shown below.
Now click on Ok button,
After clicking on OK button, you get back to Dataset Properties window, there you can see conditions are defined to filter the dataset based on Name.
Next, click on OK button. After that, go to report preview to check the implementation of Parameters.
As you know that now we have two parameters one for class, and second for Name.
These parameter filters report data where user specified value L for class exact match with Class in dataset and Name contains a text Red.
Lets filters the report data for Class H, and Name which contains text Fra.
And you can see, it filter the report data where class is H and Name contains text Fra.
Also Read..
Multi-value parameters in SSRS
Create a SSRS Tabular Report / Detail Report
Keep row header visible while scrolling down, or on each page
Add alternate row background color
