Get instant live expert help with SQL
“My expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Enter your problem description Our Experts are available now

Your message must be at least 40 characters
Select your SQL Environment:
Your message must be at least 40 characters
By submitting this form you agree to Got It's privacy policy.

Parametrized reports in SSRS – Querychat

Parametrized reports are a default in today’s world of reporting. It enables the user to browse through the data more easily and to filter only the subset of the data they need at the moment.

In order to explain the usage of parameters in reporting, we will first display a report for a Students database table without a parameter being used, and afterwards we will create one report parameter to present how easier it is to manipulate with data by using report parameters.

Table: Students

Table: Departments

Example of non-parametrized SSRS report

In the picture below, a report that displays data from Students table is presented.

Figure 1: Example of non-parametrized report

Here we can see that all data from table Students is displayed.

Imagine we have 1000 records in the table, and we want to generate a report that displays only Students from particular department. In that case, we would lose a lot of time for selecting the ones that belong to the particular group. Making a report parameter called Department ID will solve our problem, and we will get the result in a much shorter period of time.

Creating a parameter in SSRS report

Parameters related to a report reside in Parameters folder which can be viewed in Report Data pane.Therefore, in order to create a report parameter, we right-click on Parameters folder in Report Data pane and we click “Add Parameter…”.

Figure 2: Parameters folder

Now we define the parameter in the following way:

          1. General tab

                    a. Define the name of the parameter

                    b. Define the prompt of the parameter

                    c. Define data type of the parameter

                              i. If we want a parameter to allow blank values, we check this checkbox.

Note: The checkbox “Allow blank value” will be enabled only in case when “Data type” of parameter is “Text”. In all other cases, is will be disabled.

                              ii. If the parameter allows null values, we check “Allow null values” checkbox

                              iii. If the parameter allows multiple values, we check “Allow multiple values” checkbox

                    d. Define parameter visibility

                              i. “Visible” – if we want the parameter to be visible when publishing a report so users can define it’s value

                              ii. “Hidden” – if we want the parameter to be hidden when publishing a report (in this case the value of the parameter is already defined in a report definition and can be overriden on report URL)

                              iii. “Internal” – if we want the parameter to be hidden when publishing a report (in this case the value of the parameter is already defined in a report definition and can NOT be overriden on report URL)

We have defined this tab for our parameter as displayed in the Picture3.

Figure 3: Defining General tab information

          2. Available Values tab

                    a. We select which values we want our parameter to have

                              i. None – if we want the user to enter the parameter value

                              ii. Specify values – if we want to predefine the values of the parameter

                              iii. Get values from a query – if we want to set parameter values from a dataset. In our case, we want our parameter to have values from Departments table, so in case some departments get added/deleted/updated from the table, we don’t have to do anything about it in our report, but just to retrieve the changes as they are made. That is why we choose this option.

                    b. We select a dataset which data will be used as parameter value. In our case it is DepartmentsDS dataset

                    c. We select value field. In our case is the ID. This value will not be shown in parameter drop-down list, but will be used for data retrieval from database

                    d. We select label field. In our case it is the Name. This value will be shown in parameter drop-down list.

Figure 4: Defining Available Values tab information

          3. Default Values tab

               The difference between having or not having a default value for a parameter is the following:

                    a. If we don’t have a default value assigned, when we run a report, a drop-down list will contain the following: <Select a Value>. It means that we have to select a value for this parameter before generating a report.

                  Figure 5: No default value defined for a parameter

                    b. If we want to have an initial value already assigned to a parameter when opening a report, we define here that default value for the parameter by specifying the value manually, or getting the value from the query. In our case, we will again get the value from the DepartmentsDS dataset.

Figure 6: Defining Default Values tab information

When we run the report now, first value from DepartmentsDS dataset will be displayed in parameter drop-down list.

Figure 7: Default value defined for a parameter

          4. Advanced tab

We choose default values in this tab, since they are mostly used when creating standardized SSRS reports. These options are set in complex professional environments which, and as such will not be discussed here.

Figure 8: Advanced tab information

After we have gone through all these steps, we click on OK and report parameter will be created.

Figure 9: Created report parameter in Parameters

Next step is to connect the parameter to the dataset we want to filter based on it.

Connecting a parameter to a dataset and producing final parametrized report

The steps are the following:

          1. Open the dataset that should be connected to the parameter report, and add the parameter to query

Figure 10: Adding parameter to a query

          2. Define added parameter in Parameters tab of a dataset

                    a. In a Parameter Name field, add the same parameter name as the one added in query

                    b. In Parameter Value field, select the name of the report parameter previously created

Figure 11: Connecting report parameter to a query parameter

After this step, we click OK, run the report and see the following:

Figure 12: Report presentation with default parameter value

As we can see, a report is generated based on default report parameter value, which we have set to be the first row of Departments table. If we want to see for example all student from Automatics department, we simple change the report parameter value to Automatics and click on “View Report button”, and data on the report will be updated.

Figure 13: Report presentation with chosen report parameter value

Instant livechat to an Expert!

Most of the time, the problem you will need to solve will be more complex than a simple method. If you want to save hours of research and frustration, try our live SQLExpert service! 

Our SQLQuerychat Experts are available 24/7 to answer any questions you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Leave a Reply

avatar
  Subscribe  
Notify of
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc