Create SSRS Report using Stored Procedure

We will see how to create SSRS reports using Stored Procedure with example.

I have used the example from AdventureWorksDW2017 database.

Create Stored Procedure.

use AdventureWorksDW2017;

Go

IF OBJECT_ID ( 'SP_Product', 'P' ) IS NOT NULL  

    DROP PROCEDURE SP_Product; 

GO

Create Procedure dbo.SP_Product

As

Begin

SELECT Prod.Color,

       Prod.EnglishProductName AS ProductName,

       Fact.SalesAmount,

          Fact.OrderQuantity,

       Fact.TotalProductCost,

       Fact.TaxAmt

      FROM DimProduct AS Prod

         INNER JOIN FactInternetSales AS Fact ON Prod.ProductKey = Fact.ProductKey

End

Go

Execute the Stored Procedure:

Execute dbo.SP_Product

Create SSRS Report using Stored procedure.

Add new report and setup the connection string to AdventureWorksDW2017

Add new dataset and select the query type as Stored Procure.

Select the store procedure name from drop down list. Here we need to select Sp_Product.

Navigate to Fields tab. You can see all the fields from Stored procedure are populating correctly.

Add a table and map all the fields with table fields. Do the formatting well.

Run the report, You can see the  output with desired columns.

Watch the video here: