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.

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. 3^rd-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:

-- Create the SQLDevBlogV5 sample database
CREATE DATABASE SQLDevBlogV5;
GO


USE SQLDevBlogV5;

-- (1) Create the Article table in the sample database
CREATE TABLE Article (
  ArticleId INT PRIMARY KEY IDENTITY (1, 1)
 ,Category	VARCHAR(50)
 ,Author VARCHAR(50)
 ,Title VARCHAR(150)
 ,Published DATETIME2
 ,Notes VARCHAR(400)  
)

GO

SET IDENTITY_INSERT [dbo].[Article] ON
INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (1, N'Development', N'Atif', N'Introduction to T-SQL Programming ', N'2017-01-01 00:00:00', NULL)
INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (2, N'Testing', N'Peter', N'Database Unit Testing Fundamentals', N'2017-01-10 00:00:00', NULL)
INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (3, N'DLM', N'Sadaf', N'Database Lifecycle Management for beginners', N'2017-01-20 00:00:00', NULL)
INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (4, N'Development', N'Peter', N'Common Table Expressions (CTE)', N'2017-02-10 00:00:00', NULL)
INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (5, N'Testing', N'Sadaf', N'Manual Testing vs. Automated Testing', N'2017-03-20 00:00:00', NULL)
INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (6, N'Testing', N'Atif', N'Beyond Database Unit Testing', N'2017-11-10 00:00:00', NULL)
INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (7, N'Testing', N'Sadaf', N'Cross Database Unit Testing', N'2017-12-20 00:00:00', NULL)
INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (8, N'Development', N'Peter', N'SQLCMD - A Handy Utitliy for Developers', N'2018-01-10 00:00:00', NULL)
INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (9, N'Testing', N'Sadaf', N'Scripting and Testing Database for beginners ', N'2018-02-15 00:00:00', NULL)
INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (10, N'Development', N'Atif', N'Advanced Database Development Methods', N'2018-07-10 00:00:00', NULL)
INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (11, N'Testing', N'Sadaf', N'How to Write Unit Tests for your Database', N'2018-11-10 00:00:00', NULL)
INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (12, N'Development', N'Peter', N'Database Development using Modern Tools', N'2018-12-10 00:00:00', NULL)
INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (13, N'DLM', N'Atif', N'Designing, Developing and Deploying Database ', N'2019-01-01 00:00:00', NULL)
INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (14, N'DLM', N'Peter', N'How to Apply Database Lifecycle Management  ', N'2019-02-10 00:00:00', NULL)
SET IDENTITY_INSERT [dbo].[Article] OFF

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:

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):

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:

-- View Articles by year using the Year variable

DECLARE @Year INT -- declare the year variable to be used in the query
SET @Year=2018 – initialise the year variable 

-- View articles based on the year value (2018)
SELECT * FROM dbo.Article WHERE year(published)=@Year

Now, run the query to see the results:

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:

SELECT * FROM dbo.Article WHERE year(published)=@Year

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:

Configure the Dataset Parameter

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

Add a new report with a parameter

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

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

Have a look at the blank report we just created:

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…

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

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

Add Page Header

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

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

Add a Table, Drag & Drop the Fields

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

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

Run the report

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

Click Preview, enter 2019, and click View 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