Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

SSRS Parameters and Their Labels

Parameters allow report developers to create dynamic reports in Reporting Services. Almost all of my reports have at least one parameter that is passed into a stored procedure in order to filter the results returned to the report. Typically, I pass the “value” assigned to the parameter. However, every once in a while, I have a use for the "label" assigned to that parameter. In this article, I am going to show you an example of how to use the label assigned to the parameter.

 

For example - let’s say that we want to have a drop down list box containing each of the state numbers and state names from a fictitious table called #ParameterLabel. Use the script below in the Step-by-Step section to load the available values for the parameter and use the label property on the report.

 

Step By Step walk through

 

Step 1: Create a dataset named GetStateNames and set the query text as in figure 1

CREATE TABLE #ParameterLabel (StateID INT, StateName VARCHAR(25))

 

INSERT INTO #ParameterLabel VALUES (1, 'Florida')

INSERT INTO #ParameterLabel VALUES (2, 'Kentucky')

INSERT INTO #ParameterLabel VALUES (3, 'Georgia')

INSERT INTO #ParameterLabel VALUES (4, 'California')

INSERT INTO #ParameterLabel VALUES (5, 'Ohio')

 

SELECT * FROM #ParameterLabel

 

Figure 1: DataSet Properties

Dataset Properties

 

Step 2: Create a new parameter called StateName by right clicking the Parameters folder and selecting Add Parameter as shown in figure 2 below.

 

Figure 2: Report Parameter Properties

Report Parameter Properties

 

Step 3: Set the available values to get the results of the dataset GetStateNames with the StateID mapped to the Value and the StateName mapped to the Label as shown in figure 3.

 

Figure 3: Available Values for Parameter

Report Parameter - Available Values

 

Step 4: Add a textbox to the report, right click it and set the expression to the below value

="The label of the state that you selected was " & Parameters!StateName.Label

 

Step 5: Preview the report and select Florida. You should have something similar to the results in figure 4 below (less the picture of me on the left and all the extra formatting of course).

 

Figure 4: Parameter Label Example

Parameter Label Example

 

If you would like to download this sample, you can download it HERE!

 

Since I am writing this in order to help others and if you have found this article helpful, please rate it below. If you would like to provide constructive criticism to help me provide the information that you would like to see, I would love to hear that as well!

 

 

Until next time, “keep your ear to the grindstone” – Good Will Hunting

 

 

 

Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant – Pragmatic Works Consultants

Email: bmcdonald@pragmaticworks.com | Blog: BI Developer Network

Convert with DTS xChange  | Develop with BI xPress  | Process with TaskFactory | Document with BI Documenter

 

Print
Categories: Reporting Services
Tags:
Rate this article:
No rating

briankmcdonaldbriankmcdonald

Other posts by briankmcdonald

Please login or register to post comments.