SSRS Report using Stored Procedure with Parameter

This SSRS article shows how to create a report using a stored procedure with the parameter value. For instance, if the client provides only stored procedures to design reports and if it accepts the parameter value, then we can use this method.

Right-click on the Datasets folder to create a new DataSet. The below screenshot shows the report data set that we use for this SSRS Stored Procedure with Parameter example.

• Create a Report using the Stored Procedure.
• Create a Report using Stored Procedure with Multi-Value Parameter.
• Table Report.

The Sql stored Procedure that we used in this SSRS example is:

USE [AdventureWorksDW2019]
GO
CREATE PROCEDURE [dbo].[SP_ProductCategorySales]
@Category VARCHAR(100)
AS
BEGIN
SET NOCOUNT ON;
	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 Cat.[EnglishProductCategoryName] = @Category
	GROUP BY Cat.[EnglishProductCategoryName], SubCat.[EnglishProductSubcategoryName], 
	Prod.EnglishProductName, Prod.Color	
END

If you observe the above Stored Procedure, it has a where condition with the @Category parameter. Go to the Fields tab to check the columns and also rename them if needed.

SSRS Report using Stored Procedure with Parameter

The below screenshot shows the available columns in the Stored Procedure 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 and formatted the font and colors.

If you go to the SSRS Report using Stored Procedure with Parameter Preview, it shows the text box to enter the category. As there are no default values, it shows an empty report.

If you enter Clothing, it shows all the product sales belonging to the Clothing.

Let me create another data set to bring the distinct product category names so we can use them as the available field of the @Category parameter.

SELECT DISTINCT EnglishProductCategoryName 
FROM DimProductCategory

Double-click @Cateogry to open the Report Parameter Properties window. Go to the Available Values tab, select Get values from a query option, and choose the DataSet, Vlaue, and label field using the dropdown.

Now, if you go to the report preview, you can see the stored procedure parameter value from the drop down.