SSRS Report Parameter Static Values

This SSRS article shows how to use the static values in the report parameter with an example. For instance, if there are few items to filter the dataset, specify them in the parameter default values instead of getting them from the query. It increases the report performance.

Right-Click on the Datasets folder to create a new DataSet. The screenshot below shows the data set we use for this example of SSRS report parameter static values.


The Sql query that we used above SSRS example is:

SELECT Cat.[EnglishProductCategoryName] AS Category, 
       SubCat.[EnglishProductSubcategoryName] AS SubCategory, 
       Prod.EnglishProductName AS ProductName, 
       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
WHERE [EnglishProductCategoryName] = @Category
GROUP BY Cat.[EnglishProductCategoryName], SubCat.[EnglishProductSubcategoryName],
		Prod.EnglishProductName, Prod.Color

If you observe the above query, we have used the where condition with the @Category parameter.

SSRS Report Parameter Static Values

The below screenshot shows the available columns in a DataSet1 and the automatically created Category Parameter. Next, to create a table report, right-click on the empty space, select insert, and choose the Table option.

Insert a table

We have designed a simple table report of sales by product category and formatted the font and colors.

Table Report

The report preview shows the text box to enter the category.

Report Preview

When I enter Accessories, it shows all the products belonging to the Accessories.

Sales by category

In the Adventure Works database, there are three product categories. It makes no sense to create another dataset for getting those three product categories. Instead, we can write those categories inside the report parameter in these scenarios.

Double-click or right-click and choose the @Cateogry Parameter Properties option to open the Report Parameter Properties window.

SSRS Report Parameter Static Values Properties window

Go to the Available Values tab, select the specify values option, and then click the add button to add value to the @Category parameter. 

SSRS Report Parameter Static Values Available values

Let me enter all three product categories, and these are the SSRS Report Parameter Static Values.

Enter SSRS Report Parameter Static Values Manually

Now, if you go to the report preview, you won’t see any text box to enter the country name. By default, it displays a drop-down box to choose from those available static values.

Report Preview

Select Accessories to view them.

SSRS Report Parameter Static Values Preview

Similarly, try clothing as well.

Preview of SSRS Report Parameter Static Values