This SSRS article shows how to use the parameters and display the user-selected parameter value inside a report. To do this, we have to add a Text Box and write an expression that calls the Parameter value.
Right-click on the Datasets folder to create a new DataSet. Writing the below code inside the SSMS Adventure DW database shows the data set we use for this Display Parameter Value in the SSRS Report example.
The Sql query that we used in this SSRS example is:
SELECT Cat.[EnglishProductCategoryName] AS Category,
SubCat.[EnglishProductSubcategoryName] AS SubCategory,
Prod.EnglishProductName AS ProductName, Prod.Color,
SUM(Fact.OrderQuantity) AS Orders, SUM(Fact.TotalProductCost) AS ProductCost,
SUM(Fact.SalesAmount) AS Sales, SUM(Fact.TaxAmt) AS Tax
FROM FactInternetSales AS Fact
INNER JOIN DimProduct AS Prod ON Fact.ProductKey = Prod.ProductKey
INNER JOIN DimProductSubcategory AS SubCat ON Prod.ProductSubcategoryKey = SubCat.ProductSubcategoryKey
INNER JOIN DimProductCategory AS Cat ON SubCat.ProductCategoryKey = Cat.ProductCategoryKey
GROUP BY Cat.[EnglishProductCategoryName], SubCat.[EnglishProductSubcategoryName],
Prod.EnglishProductName, Prod.Color
We have created another data set of Category Names to use as the available parameter values. The code for this CategoryNamesDSet DataSet is
SELECT DISTINCT [EnglishProductCategoryName]
FROM DimProductCategory
How to Display Parameter Value in SSRS Report?
Right-click on the Parameters folder and choose the Add Parameter option to open the following window. Add Category as name and prompt text.
Go to the available values tab and choose to get values from a query option. Next, select the CategoryNamesDSet data set we created earlier to get the distinct English Product Category names and choose the category as both the value and label field.
We have designed a simple sales and orders table report by product category and formatted the font and colors. Double-click on the primary data set CatDSet, go to the filters tab, and click the add button to add a filter.
Here, we have chosen the category column as an expression and clicked on the ƒx button beside the Value to write an expression. Next, please select the Category parameter by double-clicking it.
Click OK to close the Expression and DataSet properties window. Because of the filter, the report preview displays records whose product categories are equal to user-chosen @Category parameter values.
Right-click on the empty space, select Insert, and then the text box option. It will add a text box to the report.
Right-click on the text box and choose the expression option from the context menu. Within the expression, we had chosen the Category or double-clicked on the parameter category to display its value in the text box.
Now you can see the category inside a text box. Besides writing an expression, you can also drag @category onto the text box, which will do the same.
In the SSRS report preview, you can see the selected Clothing (parameter value) displayed inside a report.
Let me add a sample text to the text box. To write any text, you don’t have to edit the expression. Instead, perhaps type whatever you like within the text box, which will reflect the same. Here, we use the below expression
"The User Has Selected the "+ Parameters!Category.Value
The report review shows the random text we entered before and the category we chose.
