This SSRS article shows how to create a report parameter and perform a wildcard search to filter the table data with an example.
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 Report Parameter wildcards 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
WHERE Prod.EnglishProductName = @Product
GROUP BY Cat.[EnglishProductCategoryName], SubCat.[EnglishProductSubcategoryName],
Prod.EnglishProductName, Prod.Color
ORDER BY OrderDate
SSRS Report Parameter Wildcard Search
Right-click on the Parameters folder and choose the Add Parameter option to open the following window. Add Product as name and Enter product Name as prompt text.
Double-click on the CatDSet (primary dataset) and go to the Filters tab to add a filter. We have chosen Product Name as an expression and Like Operator. Next, click the ƒx button to add a parameter value combined with the * operator on both sides. Here, * acts as the wildcard. Click OK to close the Expression and Dataset Properties windows.
=“*” & Parameters!Product.Value & “*”
The final report of SSRS Parameter Wildcard Search is.
In the report preview, if we type Bike, it returns all the Product name that contains Bike at any position.
When I type Mountain, it returns all the rows whose product name contains Mountain.
To get the products that end with Bikes, change the expression as below and type Bikes
=“*” & Parameters!Product.Value
Products Start with Bikes
=Parameters!Product.Value & “*”
If you want to do it at the query level, you can concat the parameter value with the % symbol on both sides. So, create a new report with the below DataSet code and design table to see the preview.
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 Prod.EnglishProductName = '%' @Product + '%'
GROUP BY Cat.[EnglishProductCategoryName], SubCat.[EnglishProductSubcategoryName],
Prod.EnglishProductName, Prod.Color
ORDER BY OrderDate