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
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
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
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
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: firstname.lastname@example.org | Blog: BI Developer Network
Convert with DTS xChange | Develop with BI xPress | Process with TaskFactory | Document with BI Documenter