Definition of SSRS Parameter
It states that when a User can specify a particular number in the textbox, the SSRS Report Parameters will filter the report data based on the user-specified value. In other terms, Report Parameters allow the user to filter SSRS Reports automatically. The ability to offer a parameter with multiple values is one of the features of SQL Server Reporting Services.
Introduction to SSRS Parameter
SQL Server Reporting Services (SSRS) uses parameters to make presentations more interactive. Parameters can be used for anything from query requirements to Tablix controls to regulating the appearance of entities on a report. They can also be generated from a set of constant values or the outcomes of a data set query. Any interactive reporting tool relies heavily on parameters.
The report’s parameters allow people to communicate with it. We can pass the parameters in four different ways.
- Parameters that were requested
- Parameters that have been cascaded
- Parameters with multiple values
- Parameters that have not been queried
The query does not return parameter values from the database in this scenario. This means that the parameter values are typed in by hand.
How to Use SSRS Parameters?
Enabling the users to choose one, couple, many, or even all values from a list at execution is a terrific way to give them freedom. It even features a built-in method for quickly selecting and deselecting all possible options. Once we plan to publish a single report that several locations or departments can use, this is a common scenario that would profit from it.
The following are cases of report parameters:
- To choose the report data by selecting required parameter values.
- To Change Report Appearance – Using expression-based attributes, employ parameters to change report appearance, such as conditionally hiding report items and conditionally changing text color.
How to Create SSRS Parameter?
If a user copies or saves the report with the parameter shown in the header, the parameter choices taken at the beginning are maintained. This creates value to an account and makes it easier for others to grasp – especially if it’s a more detailed report with multiple parameters. Reports are easier to create using parameters, and the user experience is more functional and adaptable.
Let us make a parameter that takes a user-specified value from a textbox and uses it to filter the report data by Class. Then, when we select Add parameters, a Report Parameters Properties window will appear, wherein we must provide the parameters’ details, which are as follows.
Name: Supply a suitable parameter name.
Prompt: type a short message that will appear as a label in front of the text area.
Because the class name is a Text data type, leave the data type at default Text.
SSRS Parameter Add Reports
Here we shall create an example on Order Wise Sales Report
Step 1: Creating a Datasets
The Query list is given here:
Step 2: When we execute the query, the result is here. Here is the order details table:
To make this above table into a relational, we must write a question as below, and the preview list is given here:
Step 3: Next, move on to datasets and fill the question and the dataset name, respectively: In this scenario, we will add an order filtering parameter. Change the query to this: Double-click the dataset and change the query to this:
Step 4: Let’s see a Step-by-step Implementation of Parameters in a Report.
Adding a table is given here:
Step 5: Adding Parameter values with available values. It’s difficult for users to recall which values are acceptable for a report; report developers frequently present a list of options to choose from. Go to the Known Values page for the parameter. As indicated in the screenshot, specific values tell the parameter to select a deal, and also, the option is set to None sometimes, which means that no list is presented.
Step 6: We’ll make a new parameter the same thing we handled before: right-click the parameters directory and select Add New Parameters. The last step is to check the parameters listing out the options.
Now when we preview the report, we can see a list of countries from which to choose, as shown below:
Multivalue SSRS Parameter
Within the parameter list, one can select several options. Both queried and non-queried reports can have multi-valued elements. The multi-value parameter allows us to pass one or more values to the information in addition to the input data. It also has a “Select All” feature for selecting all model parameters. In SSRS, we’ll now develop an example of a multi-value parameter.
We should first facilitate multiple values for a parameter to allow randomized, multiple-value selections in its drop-down menu.
Right-click on the parameter inside the Parameters folder in the Report Data window of the report and choose Parameter Properties.
-Select Allow multiple values from the drop-down menu.
-Click the OK button.
Next, ensure that the “Allow multiple values” check option is selected in the parameter attributes.
Click preview to list the country location list. For example, Albania, Algeria, and Andorra would be the parameters. Then, within the report’s dataset, we have to tell the query to choose rows in which the nation is one of them. This is done with an IN operator, and the parameter must be delimited in parenthesis:
Right-click on the report’s dataset in the Report Data window and select Dataset Properties.
To the query, add a WHERE clause like this: WHERE COUNTRY IN (@Country)
Several of the reports we produce in the future will also include more. Either the dataset will contain numerous predicates in the WHERE clause, or variables will be used to customize how the report appears.
Conclusion
We learned how to use parameters in various ways in this post, including a list of available values from a query, multiple selections, defaults, and much more. As a result, we’ve focused on the primary point: parametrized reports give us greater flexibility and improve the user experience. Multiple value parameters are a powerful resource in SSRS; utilizing one with an object filter is very simple if one knows how and where to implement the parameter values.
Recommended Articles
This is a guide to SSRS Parameter. Here we also discuss the definition, introduction, and how to use and create SSRS Parameter along with multi-value. You may also look at the following articles to learn more –
