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.

DataSet

The Sql stored Procedure that we used above 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 column and also rename them if needed.

Check Columns or Fields

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.

Create a Table

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

Formatted table Report

If you go to the SSRS Report using Stored Procedure with Parameter preview, it shows the text box to enter the category.

Report Preview

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

SSRS Report using Stored Procedure with Parameter Preview

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.

Another dataSet

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.

SSRS Report using Stored Procedure with Parameter Available values

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

SSRS Report using Stored Procedure with Parameter Preview