🪄SSRS (& Paginated Reports): Report Parameters (Second Magic Class)

Introduction

One of the advantages of SQL Server Reporting Services (SSRS) is that once  a query available, in just a few minutes one can display the data into a report. This post shows how to add parameters to a report. For exemplification is used the same view from the first post of the series.

As prerequisite for this post one needs to have ideally the Reporting Services Extension installed. 

Identifying the Parameters

The first step is deciding which parameters are required or make sense to add to the report. The parameters together with their type can be documented in a list similar to the one below:

Parameter list

SSRS differentiates between five types of parameters:  Text, Boolean, Date/Time, Integer and Float. 

Reviewing the Source Query

Before adding the parameters to the report is recommended to look at the source query to check whether it allows the efficient use of parameters. Based on the above list of parameters the standard view needs to be modified as follow (the new columns are marked with a 'new' comment):

-- Individual Customers view
ALTER VIEW [Sales].[vIndividualCustomer] 
AS 
SELECT p.[BusinessEntityID]
, p.PersonType PersonTypeId--new
, CASE p.PersonType 
	WHEN 'SC' THEN 'Store Contact'
    WHEN 'IN' THEN 'Individual (retail) customer'
    WHEN 'SP' THEN 'Sales person'
    WHEN 'EM' THEN 'Employee (non-sales)'
    WHEN 'VC' THEN 'Vendor contact'
    WHEN 'GC' THEN 'General contact'
 END PersonType --new
, p.[Title]
, p.[FirstName]
, p.[MiddleName]
, p.[LastName]
, p.[Suffix]
, pp.[PhoneNumber]
, pnt.[Name] AS [PhoneNumberType]
, ea.[EmailAddress]
, p.[EmailPromotion]
, at.[AddressTypeID] --new
, at.[Name] AS [AddressType]
, a.[AddressLine1]
, a.[AddressLine2]
, a.[City]
, sp.StateProvinceCode --new
, [StateProvinceName] = sp.[Name]
, a.[PostalCode]
, sp.CountryRegionCode -- new
, [CountryRegionName] = cr.[Name]
, p.[Demographics]
FROM [Person].[Person] p
    INNER JOIN [Person].[BusinessEntityAddress] bea 
    ON bea.[BusinessEntityID] = p.[BusinessEntityID] 
    INNER JOIN [Person].[Address] a 
    ON a.[AddressID] = bea.[AddressID]
    INNER JOIN [Person].[StateProvince] sp 
    ON sp.[StateProvinceID] = a.[StateProvinceID]
    INNER JOIN [Person].[CountryRegion] cr 
    ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
    INNER JOIN [Person].[AddressType] at 
    ON at.[AddressTypeID] = bea.[AddressTypeID]
	INNER JOIN [Sales].[Customer] c
	ON c.[PersonID] = p.[BusinessEntityID]
	LEFT OUTER JOIN [Person].[EmailAddress] ea
	ON ea.[BusinessEntityID] = p.[BusinessEntityID]
	LEFT OUTER JOIN [Person].[PersonPhone] pp
	ON pp.[BusinessEntityID] = p.[BusinessEntityID]
	LEFT OUTER JOIN [Person].[PhoneNumberType] pnt
	ON pnt.[PhoneNumberTypeID] = pp.[PhoneNumberTypeID]
WHERE c.StoreID IS NULL;

Preparing the Source Query

Based on this, the source query can be defined as follows:

-- Customer Addresses
SELECT SIC.Title   
, SIC.PersonType   
, SIC.FirstName   
, SIC.LastName   
, SIC.AddressType   
, SIC.AddressLine1   
, SIC.City   
, SIC.PostalCode   
, SIC.CountryRegionName   
, SIC.PhoneNumber   
, SIC.EmailAddress   
FROM Sales.vIndividualCustomer SIC 
WHERE CountryRegionName = 'Germany'
  AND PostalCode = '42651'
ORDER BY SIC.FirstName  
, SIC.LastName   
, SIC.AddressLine1   

Note:
When preparing a source query for your report is important to limit the number of the records returned to reduce the waiting time until the data are displayed and minimize the traffic to the database. 

Generating the Report

Creating a report is straightforward in SSRS because the 'Report Wizard' takes care of everything in the background. 

Generated report with the Report Wizard

  After testing the report, right click on the created dataset (Dataset1) in design mode and change its name to be meaningful (e.g. Addresses).

Dataset properties

   In this step I prefer transforming the query into a formula because it allows more flexibility in handling the parameters dynamically. Click on the formula and replace the query with the following text: 

 = " -- Customer Addresses " & vbCrLf
 & " SELECT SIC.Title    " & vbCrLf
 & " , SIC.PersonType    " & vbCrLf
 & " , SIC.FirstName    " & vbCrLf
 & " , SIC.LastName    " & vbCrLf
 & " , SIC.AddressType    " & vbCrLf
 & " , SIC.AddressLine1    " & vbCrLf
 & " , SIC.City    " & vbCrLf
 & " , SIC.PostalCode    " & vbCrLf
 & " , SIC.CountryRegionName    " & vbCrLf
 & " , SIC.PhoneNumber    " & vbCrLf
 & " , SIC.EmailAddress    " & vbCrLf
 & " FROM Sales.vIndividualCustomer SIC  " & vbCrLf
 & " WHERE CountryRegionName = 'Germany' " & vbCrLf
 & "   AND PostalCode = '42651' " & vbCrLf
 & " ORDER BY SIC.FirstName   " & vbCrLf
 & " , SIC.LastName    " & vbCrLf
 & " , SIC.AddressLine1 " & vbCrLf

This can be obtained pretty easy by using a formula in Excel like below:

Preparing the dynamic query statement

Formula: = "& "" " & A1 & " "" & vbCrLf "

Note:
In previous versions of reporting environment there must be an empty space after each vbCrLf except the last line, otherwise an error will be shown and the report can't be used at all. When the empty space misses is tricky to find the line where this happened. To validate this one can put the mouse at the end of each line and see whether it lands after the vbCrLf or after an empty space.

Preparing the Queries for Dropdowns

Before adding the parameters it might be a good idea to prepare the queries for the dropdowns. For this let's consider the simplest parameters: 

-- Address Types
SELECT 0 Id
, '(All)' Name
UNION ALL
SELECT AddressTypeID
, Name
FROM [Person].[AddressType]
ORDER BY Id

The query has two parts, one made from the database query to which is added a row marking the '(All)' scenario in which the parameter is actually ignored, and thus all the records for this parameter will be shown. This is typically the default value. 

SSRS has another solution for showing all the values, though I prefer this approach. 

In all the dropdown queries I prefer to use the same names ('Id', respectively 'Name'), independently on how the attributes are called, as they make the purpose explicit. Because the data type of the parameter is integer and is a reference key, the default value can be 0 or also -1 if 0 values are available in the dataset. 

For Text parameters one can use the empty string as parameter, like in the below example for Country Codes: 

-- Countries
SELECT '' Id
, '(All)' Name
UNION ALL
SELECT CountryRegionCode Id
, Name 
FROM [Person].[CountryRegion]
ORDER BY Id

Adding the Parameters

To add a Parameter right click on 'Parameters' within the 'Report Data' and then klick on 'Add Parameter'. This will open the 'Report Parameter Properties' which by default looks like this:

Report Parameter Properties

Each parameter type requires different settings to be considered. 

Defining Freetext parameters

Customer Name, Postal Code and Phone Number are Text parameters shown in Freetext controls and all three are setup similarly (see Parameter List above): 

General settings for text parameters

Defining Dropdowns with Fix Values

The General setting for the Person Type are similar with the ones considered for Freetext, however it has a set of predefined values which can be added in the Available Values as follows: 

Available Values

The default value can be provided into the 'Default Values' section. Just click on 'Add' and enter an empty space:

Default Values for fix dropdown

Defining Dropdowns with Dynamics Values

Country parameter is defined similarly, however as Available Values is selected the 'Get values from a query' option. The Countries dataset is selected as source while 'Id' and 'Name' are provided as below: 

Available Values for dynamic dropdown

Same can be done also for the AddressType parameter having the AddressTypes datasource as source. 

After adding all the parameters and choosing the order in which they should be displayed, the Report Data section will look like this:

Report's parameters

Note:

When designing parameters one should consider also the best practices for avoiding the poor design of parameters.

Adding the Parameters to the Query

For this the query needs to be added as follows: 

= "-- Customer Addresses " & vbCrLf
 & " DECLARE @Country as nvarchar(3) = '" & Parameters!Country.Value & "'" & vbcrlf 
 & " DECLARE @CustomerName as nvarchar(100) = '" & Parameters!CustomerName.Value & "'" & vbcrlf 
 & " DECLARE @PostalCode as nvarchar(15) = '" & Parameters!PostalCode.Value & "'" & vbcrlf 
 & " DECLARE @PhoneNumber as nvarchar(25) = '" & Parameters!PhoneNumber.Value & "'" & vbcrlf 
 & " DECLARE @AddressType as int = " & Parameters!AddressType.Value & vbcrlf 
 & " DECLARE @PersonType as nvarchar(3) = '" & Parameters!PersonType.Value & "'" & vbcrlf 
 & " SELECT SIC.Title   " & vbCrLf
 & ", SIC.PersonType   " & vbCrLf
 & ", SIC.FirstName   " & vbCrLf
 & ", SIC.LastName   " & vbCrLf
 & ", SIC.AddressType   " & vbCrLf
 & ", SIC.AddressLine1   " & vbCrLf
 & ", SIC.City   " & vbCrLf
 & ", SIC.PostalCode   " & vbCrLf
 & ", SIC.CountryRegionName   " & vbCrLf
 & ", SIC.PhoneNumber   " & vbCrLf
 & ", SIC.EmailAddress   " & vbCrLf
 & " FROM Sales.vIndividualCustomer SIC " & vbCrLf
 & " WHERE 0=0 " & vbCrLf
 & IIf(Parameters!Country.Value<> "", " 		  AND SIC.CountryRegionCode = @Country", "") & vbcrlf 
 & IIf(Parameters!CustomerName.Value<> "", " 		  AND (SIC.FirstName  LIKE '%' + @CustomerName + '%' OR SIC.LastName  LIKE '%' + @CustomerName + '%')", "") & vbcrlf 
 & IIf(Parameters!PostalCode.Value<> "", " 		  AND SIC.PostalCode = @PostalCode", "") & vbcrlf 
 & IIf(Parameters!PhoneNumber.Value<> "", " 		  AND SIC.PhoneNumber LIKE '%' + @PhoneNumber + '%'", "") & vbcrlf
 & IIf(Parameters!PersonType.Value<> "", " 		  AND SIC.PersonTypeId = @PersonType", "") & vbcrlf
 & IIf(Parameters!AddressType.Value<> 0, " 		  AND SIC.AddressTypeId = @AddressType", "") & vbcrlf 
 & " ORDER BY SIC.FirstName  " & vbCrLf
 & ", SIC.LastName   " & vbCrLf
 & ", SIC.AddressLine1   " & vbCrLf

For each report parameter is defined a SQL parameter in the header with the appropriate data type and length, and a constraint within the WHERE clause. The parameter will be considered only if a valid value was provided. One has to take into consideration the type of the parameter. 

Testing the Report

If everything worked correctly the report will run without problems:

Testing the report

For testing the report it makes sense to choose the parameters one by one from the general to the more particular case. For example for a first test I would choose 'Germany' as Country, then in a second run i would choose one of the values from the resulting dataset, e.g. 'Arthur' as Customer Name, while in a thirst run I would pick a value for the Postal Code. This approach allows minimizing the volume of time required for testing. 

Troubleshooting

Errors in formulas are sometimes difficult to troubleshoot especially when the report refuses to run. The SQL errors are easier to troubleshoot into Management Studio (SSMS). Just copy the query in SSMS and replace the added formatting.

Errors into the formula require unfortunately more time, however most of the errors I had were related to the missing empty space, or forgetting to close a quote or incorrect formatting.