SQL Server Reporting Service: All You Need to Know about Parameterized Reports

Reports are the most common way to view the data that are stored in a database through an application. Reports help us to format data and place it in a simple and attractive format in front of the users.

But at the same time, the system should allow the users to dynamically choose the filter criteria while generating the report. Just providing a full dump of all the data present in the system to date will not only make the report heavy but would also make it slower and unusable.

This is where parameters come into play. The parameter allows the user to send specific filter criteria into the report and view it based on that filter. Parameters in a way allow the users to control the report output dynamically. Based on the number and types of parameters allowed, parameterized reports are of the following types.

Throughout the following couple of passages right now we will talk about these highlights in detail and see how we can utilize these highlights to make an SSRS report presentable and more attractive to the users.

Single value parameterized reports

Single parameter SSRS reports are reports which only accept one parameter as inputs. For all the examples in the write-up, we will be using tables from the Adventureworks2016 database.

Step 1

Let us first generate the query. We will be using the SalesOrderHeader table from the Adventureworks2016 database to generate the query and pass the customerid as a parameter from outside. The query will look like below.

Select * from [Sales].SalesOrderHeader where CustomerID=@custid

Step 2

Let us open a new SSRS project and add a new report to it.

Step 2

Add a data source into the report.

When the data source is included, it will look like below.

Step 3

Now it is time to add the dataset. Let us use the query above along with the parameter variable to create the dataset.

Once the dataset is created it will look like below.

Step 4

This would also create an entry in the parameter section like below.

Subsequently, it creates a separate section in the report area for entering the parameter values like below.

Step 5

To view the report we need to provide value in the custid textbox and click on submit. The result looks like below.

However, this kind of approach has a drawback. As you can understand if the user does not know the customer id, which is most unlikely that he would, it will be very difficult for him to use the report. He has to manually type in the desired customer id and click on the submit button to view the report. If a wrong customer id is entered in the custid textbox the report will not generate and throw up an error message and would ask the user to enter the customer id as shown below.

To avoid such confusion the alternative is to provide the user a dropdown for all the custid values from where the user can choose the desired value. Here is how it can be done.

Single value parameterized reports with dropdown

We will use the same basic query to create the report. Here it goes.

Step 1

We will be using the SalesOrderHeader table from the Adventureworks2016 database to generate the query and pass the customerid as a parameter from outside. The query will look like below.

Select * from [Sales].SalesOrderHeader where CustomerID=@custid

Step 2

Since this time we will be providing the user a drop-down to select the parameter values we need to generate a query for that as well. The parameter query will look like below.

Select distinct CustomerID from [Sales].SalesOrderHeader

Step 3

Add a data source into the report.

Read: SQL REPLACE() Function: A Step-By-Step Guide

Once the data source is added, it will look like below.

Step 4

We need to create two dataset one for the main report which will have parameter defined against the customerid and another to return the customerid values for the customerid parameter. Here is how we would define the two parameters.

The first one is for the main dataset for the report.

The next one will return the customerid records for the parameter dropdown.

Once it is done the dataset section of the report will look like below.

Step 5

Next, we need to setup the parameter to show the customerid as a dropdown. This is how we would do it.

• Go to the parameter properties of the specific parameter.

• Select the available value option.

• Select Get values from query radio button.

• Select the dataset name from the dataset dropdown and the field and label name from the field and label dropdown.

The whole configuration will look like below.

Step 6

If we now run the report we will see the parameter option has been changed to drop down and we can select the value from the dropdown instead of entering it in the text box as below.

Step 7

To permit the parameter dropdown to choose various qualities we have to check the Allow multiple values checkbox in the general tab of the parameter properties like below.

 

Also, we need to make a small change in the report query as well as below.

Select * from [Sales].SalesOrderHeader where CustomerID in (@custid)

Step 7

Once the following changes are made we can select multiple values in the parameter to generate the report as below.

Multivalue parameterized reports

Multiple parameter SSRS reports are reports which accept more than one parameter as inputs. 

Here is a step by step guide on how we can create it.

Step 1

We will be using the SalesOrderHeader table from the Adventurework2016 database to generate the query and pass the customerid as a parameter from outside. The query will look like below.

Select * from [Sales].SalesOrderHeader where CustomerID=@custid and SalesPersonID=@salesperson

Step 2

Since this time we will be providing the user a drop-down to select the parameter values we need to generate a query for that as well. The parameter query will look like below.

Select distinct CustomerID from [Sales].SalesOrderHeader

Step 3

Add a data source into the report.

Once the data source is added, it will look like below.

Read: How to use SQL Count() Aggregate Function

Step 4

We need to create two datasets, one for the main report which will have parameters defined against the customerid and another to return the customerid values for the customerid parameter. Here is how we would define the two parameters.

The first one is for the main dataset for the report.

The next one will return the customerid records for the parameter dropdown.

Once it is done the dataset section of the report will look like below.

Step 5

Next, we need to setup the parameter to show the customerid as a dropdown. This is how we would do it.

• Go to the parameter properties of the specific parameter.

• Select the available value option.

• Select Get values from the query radio button.

• Select the dataset name from the dataset dropdown and the field and label name from the field and label dropdown.

The whole configuration will look like below.

Step 6

Similarly, let us create the second dropdown for the Salesperson. For this also, we need to create a third dataset although the data source will remain the same.

The query for the second dataset is-

Select distinct SalesPersonID from [Sales].SalesOrderHeader

Step 7

The dataset once created will look like below

Step 8

We link the dataset with the second parameter as below.

Step 9

The final output will look like below.

As you can see there is a basic drawback in the design of the report. The user needs to know the exact combination of the customerid and corresponding salespersonid to generate the report properly. Otherwise, the report will show blank data like below.

To avoid such issues we use Cascading parameter reports.

Cascading Parameters in SSRS reports

Cascading parameters are types of parameters where the values shown in the parameter dropdown are dependent on each other. Here is the step on how we can make the parameters mentioned in the above report cascading.

Step 1

We need to define a parameter for the second dataset query like below.

SELECT DISTINCT SalesPersonID

FROM Sales.SalesOrderHeader where Customerid=@custid

Step 2

We then need to set the parameter property of the third dataset with the parameter property of the second dataset as below.

Read: View vs Table In SQL: A Comprehensive Insight You Have To Know

Step 3

Once this is done this would only show details of that salespersonid which is related to customerid selected in the first parameter dropdown as below.

Drilldown Reports

In an SSRS report having large volume data, it is always easier for a user to have a way to view only a part of the report at a time while the other section remains hidden. Drill down reports in SSRS allows just that. Here is a step by step guide on how to create a drill-down report.

Step1

We will be using the same SalesOrderheader query as below.

Select * from [Sales].SalesOrderHeader

This time for generating the report we will be using the report wizard.

Step 2

Create a new report project and select Report Server Project Wizard as below.

Step 3

The following screen comes. Click next.

Step 4

Create a connection string.

Step 5

Create the query.

Step 6

We will select tabular format of report

Step 7

Next, we will select the fields. We will select the Customerid as the group by option.

Step 8

In the next step, we need to enable the drill-down option.

Step 9

Click on finish and the report will be generated.

Step 10

The resulting report will look like below. There will be a plus sign against each sales order id. When you click on the plus sign it will show the details.

On clicking the plus sign the output will be like below.

Summary

In the above write-up, we have discussed different types of parameters and also about drill-down reports. This was just an introduction to what is available as far as these topics are concerned. Each of the above-mentioned topics has a lot of detailed features that need to be addressed individually.

FaceBook Twitter Google+ LinkedIn Pinterest Email