Create SSRS Report using Stored Procedure 11

Create SSRS Report using Stored Procedure

In this article, we will show you how to create an SSRS Report using Stored Procedure with an example. It’s one of the frequently asked questions by SSRS followers. To demonstrate the same, we are going to create a Stored procedure on the Adventure Works DW database.

The below screenshot shows the data that we are going to use in this SSRS stored procedure:

TIP: If you want to create a custom Stored Procedure, visit the Stored Procedure article in SQL Server.

Source Table

Now, Let me write query to create a Stored Procedure on Adventure Works DW:

USE [AdventureWorksDW2014]
GO

IF OBJECT_ID ( 'SP_ProductSales', 'P' ) IS NOT NULL   
    DROP PROCEDURE SP_ProductSales;  
GO
 
CREATE PROCEDURE [dbo].[SP_ProductSales]
AS
BEGIN
      SET NOCOUNT ON;
	  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

OUTPUT

Stored Procedure Result 2

Create SSRS Report using Stored Procedure

In this example, we are going to create a 3D Clustered Column Chart to display the step by step approach to create an SSRS Report using Stored Procedure. The below screenshot will show you the Data Source and Dataset we are going to use for this SSRS Clustered Column Chart Report.

Here, we have to change the Query type from Text (which is the default) to the Stored procedure.

Choose Stored procedure as the dataset option 3

Next, Please select the required stored procedure name from the available list. From the below screenshot, you can see that we are choosing the one that we created earlier.

Choose the previously created stored procedure for this report 4

Lastly, please navigate to the Fields tab, and check whether we got all the fields from our stored procedure or not. Here you can add Calculated Fields also.

Create Report using Stored Procedure 5

In the SSRS report design, we can add a Clustered Column chart by dragging the Chart property from the Toolbox to Design space. Or right-click on the report designer to open the context menu. From the context menu, please select the Insert -> Chart option.

insert chart in to the report 6

Once you select the Chart option, a new window called Select Chart Type will be opened to select the required chart from the available once. For this example, we are selecting 3D Clustered Column Chart.

Create Report using Stored Procedure 7

Once you click on the Ok button, the 3D Clustered Column chart will display in the design region with dummy data.

3D Clustered Column Chart using Stored Procedure 8

Clicking on the space around the Clustered Column Chart will open the Chart Data window.

In this example, we will create a Clustered Column chart for the Total Product Cost and Sales Amount. So, Drag and drop them from dataset to chart data values and Product Color in the category group.

I suggest you to refer to Create Column Chart article to understand the creation of Clustered Column chart

Create Report using Stored Procedure 9

Click on the Preview button to see the report preview.

Create Report using Stored Procedure 10

From the above screenshot, we created a 3D Clustered Column report using the SP_ProductSales stored procedure in SSRS. Let me Format the chart a bit for a classy look.

Remember, we already explained the Formatting options in the Formatting Column Chart article. So, Please refer to the article to understand the steps.

Create SSRS Report using Stored Procedure 11