SSRS Report Parameters

The SSRS Report Parameters allows the users to enter a specific value in the textBox, and then it will filter the Report data using the user-specified value. OR Report Parameters enables the users to Filter the SSRS Reports Dynamically.

This article will show you how to add Report parameters in SSRS or SQL Server Reporting Services with an example.

We will use the below-shown report to explain SSRS Report parameters in Reports. Please refer to the Table Report article to understand the creation of the SSRS Table report. If you observe the below screenshot, It was a normal Table report with Country, Full Name, Occupation, Yearly Income, and Sales Amount columns.

The below screenshot will show you the Shared Data Source and Dataset we used for this report.

SQL Command we used in the above screenshot is:

SELECT Geo.[EnglishCountryRegionName] AS [Country]
      ,Geo.[StateProvinceName] AS [State]
      ,Geo.[City]
      ,Cust.FirstName +' '+ Cust.LastName AS [Full Name]  
      ,Cust.EnglishEducation AS Education 
      ,Cust.EnglishOccupation AS Occupation
      ,SUM(Cust.YearlyIncome) AS YearlyIncome
      ,SUM(Fact.SalesAmount)AS SalesAmount 
FROM  DimCustomer AS Cust
  INNER JOIN
      FactInternetSales AS Fact ON 
 Cust.CustomerKey = Fact.CustomerKey
 INNER JOIN
     [DimGeography] AS Geo ON
   Cust.GeographyKey = Geo.GeographyKey
 GROUP BY Geo.[EnglishCountryRegionName]
         ,Geo.[StateProvinceName] 
	 ,Geo.[City]
	 ,Cust.FirstName
         ,Cust.LastName  
         ,Cust.EnglishEducation 
         ,Cust.EnglishOccupation 
  ORDER BY [Country]

Adding SSRS Report Parameters

This page explains the simple procedure for creating basic parameters that can apply to the SSRS table report. However, you can do a lot of things with parameters. The below list is some of the options.

• Allow Nulls as a Report Parameter value
• Allow Null Parameters using ISNULL Query
• Date Parameters with Calendar
• Display Parameter Value in a Report
• Hide Report Parameter
• Report Parameter with Default Value
• Report Parameter With Static Values
• Set Report Parameter as Optional using a Query
• Set Report Parameters Optional using IIF
• Use Report Parameters to Perform Wildcard Search
• Use Radio Buttons as Report Parameters
• Parameter Value to Show or Hide Table Columns
• Use Reports as Parameter Value

To add SSRS Report Parameters, Right Click on the Parameters Folder present in the Report Data tab will open the Context Menu to select Add parameters.. option.

Once you click on Add parameters.. option, a new window called Report parameter Properties is opened to configure the parameter properties in SSRS.

• Name: Please specify the valid Parameter name as per your requirement. Here, we defined it as CountryParameter.
• Prompt: The text you specified here will display as a label before the text box.
• Data Type: Country name is a Text data type, so we kept the default text unchanged

Click Ok to finish configuring the Report parameters in SSRS. Now, We have to apply filter conditions on the Dataset. Please refer to Filter at Dataset Level article to understand the creation of Filters.

Select the Dataset from the Report data tab, and right-click on it will open the context menu. Please select the Dataset Properties.. option from it.

In this SSRS report parameters example, we will display the records whose [Country Name] is equal to [Country Parameter] we just created. So, Please select the Country as Expression, Operator as Like Operator, and Value as the Parameter name.

If you find any difficult to write the parameter name, Please click on the fx button and select the parameter name from the GUI.

Click Ok to finish configuring the Filters at Dataset Level. Click on the Preview Tab to preview the SSRS Report Parameters Data.

The above screenshot shows that It is displaying a Blank Report with Empty Textbox on Top. To view the records, enter the Country Name in that Textbox and Press Enter.

For now, We are entering Germany as Country Name.

NOTE: If we know exactly the Parameter values (Country Names in this example), then there will be no problem. How about remembering 100 Employee Names or Product Names? In these situations, we must use Drop Down List, Multi-Value, Multiple, and Cascading Parameters.