Creating Customer-Focused SSRS Reports with Parameters

This article talks about how to create a basic customer-focused report using SQL Server Reporting Services (SSRS).

The article also highlights the importance of utilizing the full potential of the SSRS reporting technology by using parameters in SSRS reports – for example, to meet business specifications.

The main focus of this article is to prepare the readers who already have a T-SQL background to create SSRS reports for customers. In this way, the customers can run these reports based on their desired input values rather than on default values provided by report developers.

About Report Parameters

Let us first get familiar with report parameters in general.

Microsoft Definition (SSRS 2016 and Later)

A report parameter provides a way to choose report data, connect related reports together, and vary the report presentation.

Simple Definition

Report parameters help end users to run reports based on their input values.

In other words, report parameters ask the end user to provide values for some defined characteristics so that the report can be ran based on these user-supplied values.

Example

A simple example of a report parameter would be in a report which shows total sales for a particular year based on the value provided by the end user.

In this example, year is the report parameter which can either have a default value or a value specifically provided by the report’s end user.

In other words, this report is more like a dynamic report since it produces different results based on different parameter values provided at run time rather than at design time.

SSRS Report With Parameters Overview

Types of Report Parameters

According to the Microsoft documentation, a report parameter can have one of the following types:

  1. Boolean (True of False)
  2. DateTime (Any valid date and time)
  3. Integer (Any valid whole number)
  4. Float (Any valid number with potential decimal place)
  5. Text (Any text)

How Report Parameters can be created

As per the Microsoft documentation, report parameters can be created in one of the following ways:

  • Automatically
  • Manually

Automatically

  1. When the query that runs behind the report in the form of a data set already contains parameters
  2. When a shared data set is added to a report which already contains parameters

Please note that, in this article we will specifically focus on the automatic method of creating report parameters.

Manually

You can add report parameters manually in the Report Data Pane.

Design vs Published Version

Please remember that the report parameters are saved in a rdl report file at the design time. However, when the report is published, the report parameters are handled differently and are no longer bound to a part of the report file.

This helps business users to directly change report parameters after the report gets published to the report server.

Creating an SSRS report

Pre-requisites

This article assumes that you have a basic knowledge of T-SQL scripts and are also familiar with the basics of report design using one of the following tools:

  1. SQL Server Data Tools (SSDT)
  2. Report Builder
  3. 3rd-Party Report Building Tools such as dbForge Studio for SQL Server

I strongly recommend you to have your report server configured at this point to publish the reports quickly, although this is not mandatory to follow the instructions in this article.

Please read my article SSRS Reports Development in Simple Words to get a general understanding of how to create a simple SSRS report.

Setup the sample database

First of all, we’ll need a database that will be used as the data source for the SSRS report.

Please create and populate a sample database called SQLDevBlogV5 as follows:

Business Requirements and Analysis

Statement

Suppose you have just received a business requirement as follows:

As a business user, I want to view a report on all technical articles for a specified year”

Preliminary Analysis

This business requirement clearly states that the end user of the report would like to input the year value at run time so that they can view the report on all articles for the specified year.

This leaves us no option except to create an SSRS report with a parameter so that the end user will be able to supply the year at run time and see the report.

Please try to look for keywords such as specified year in the business requirement – this gives you a hint that a report with a year parameter is required.

Create an SSRS Database Report with Parameters

Let’s begin by creating a Reports Server Project in SQL Server Data Tools (SSDT).

If you are interested in seeing a detailed walkthrough of how to create a basic SSRS report, then please go through my article SSRS Reports Development in Simple Words.

Create a New Report Server Project

Open Visual Studio and create a new Report Server Project called Articles Report with Parameters Project under the Business Intelligence template:

New Report Server Project

Add a New Shared Data Source

As with any other SSRS report project, we need to create either a shared or embedded data source to let the project know from where we are sourcing data for the report.

Right-click Shared Data Sources under Articles Report with Parameters, click Add New Data Source and establish a connection with the sample database (SQLDevBlogV5):

Add New Shared Data Source

Design a Dataset (Report) Query with Parameters

Once the data source has been setup, it is time to design your T-SQL query with parameters.

In order to design and test a parameterized query, you need to declare a year variable to be used in T-SQL with the help of the WHERE clause.

Open a new query window and run the following script against SQLDevBlogV5 to make sure it pulls the desired data from the database:

Now, run the query to see the results:

Viewing Articles By Year

T-SQL script with a variable works well, so we can exclude the variable declaration and initialization and move on with only the following part of the script remaining:

Please remember that this dataset query is going to generate the parameter automatically.

Add a new Dataset including Year

Right-click Shared Datasets under the project node and click Add New Dataset. Then, add a new dataset DSet_ArticlesByYear and enter the query in the corresponding input field:

Add New Dataset

Configure the Dataset Parameter

Now, click the Parameters section, set Data Type to integer, and Click OK.

Configure The Dataset Parameter

Add a new report with a parameter

Right-click Reports, choose Add, and then click New Item… as follows:

Add New Report With Parameter 1

Enter the report’s name (‘ArticlesByYearReport’) and Click Add:

Add New Report With Parameter 2

Have a look at the blank report we just created:

Viewing A Blank Report

Configure the Report Data Pane

Go to the Report Data Pane which appears as soon as the report is created, right-click on Data Sources, and Click Add Data Source…

Using the Shared Data Source

Name the data source DS_ArticlesByYear, select the shared data source we created earlier in this project, and Click OK:

Data Source Properties

Right-click the Report Data pane, click Add Dataset…, choose the Use a shared dataset option, and Click OK:

Dataset Properties

Add Page Header

Right-click the report design surface, click Insert, and then click Page Header:

Add Page Header 1

Next, right-click on the page header section, click Insert, then click the text box and write “Articles By Year Report”:

Add Page Header 2

Add a Table, Drag & Drop the Fields

Right-click the report’s design surface, click Insert, and then click Table as follows:

Insert Table

Next, drag the fields from the Dataset onto the table one-by-one and make the headers bold from the toolbar menu as follows:

Drag And Drop Fields

Run the report

Let’s do some more formatting before we run the report.

Click Preview, enter 2019, and click View Report:

Run The Report

Congratulations! You have successfully created an SSRS report with a parameter to show all articles written by the authors based on the year value provided at run time by the business user.

Things to do

Now that you are familiar with SSRS development fundamentals, please try the following:

  1. Create an SSRS report with the Category name as a parameter
  2. Create an SSRS report with the Author name as a parameter
  3. Keeping the example in this article in mind, please create one SSRS report with the student name as a parameter and another report with the course name as a parameter based on the sample database named TechnicalTraining mentioned in this article

Haroon Ashraf

Haroon's deep interest in logic and reasoning at an early age of his academic career paved his path to become a data professional.

He began his professional life as a computer programmer more than 10 years ago working on his first data venture to migrate and rewrite a public sector database driven examination system from IBM AS400 (DB2) to SQL Server 2000 using VB 6.0 and Classic ASP along with developing reports and archiving many years of data.

His work and interest revolves around Database-Centric Architectures and his expertise include database and reports design, development, testing, implementation and migration along with Database Life Cycle Management (DLM).
Haroon Ashraf