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. Writing the below code inside the SSMS 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, 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
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. We have designed a simple table report of sales by product category and formatted the font and colors.
The report preview shows the empty text box to enter the category. Unless you enter the category, it shows the empty report. When I enter Accessories, it shows all the products belonging to the Accessories.
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.
Go to the Available Values tab, select the specify values option, and then click the add button to add value to the @Category parameter. Let me enter all three product categories, and these are the SSRS Report Parameter Static Values.
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. Select Accessories to view them.
Similarly, try clothing as well.
