Tutorial Gateway

Create SSRS Report using Stored Procedure

by

In this article we will show you, How to Create SSRS Report using Stored Procedure with example. This is one of the frequently asked question by SSRS followers. In order to demonstrate the same, we are going create a Stored procedure on Adventure Works DW database.

Below screenshot will show you the data that we are going to use in this stored procedure:

TIP: If you want to create a custom Stored Procedure on your own then I suggest you to refer SQL Stored Procedure article that we mentioned in SQL Server Tutorial.

Create SSRS Report using Stored Procedure 1

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

SQL Query

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

Create SSRS Report using Stored Procedure 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 SSRS Report using Stored Procedure. Below screenshot will show you the Data Source and Dataset we are going to use for this Clustered Column Chart Report.

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

Create SSRS Report using Stored Procedure 3

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

Create SSRS Report using Stored Procedure 4

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

Create SSRS Report using Stored Procedure 5

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

Create SSRS Report using Stored Procedure 6

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

Create SSRS Report using Stored Procedure 7

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

Create SSRS Report using Stored Procedure 8

By Clicking on the empty 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 data set to chart data values, and Product Color in category group. I suggest you to refer Create Column Chart in SSRS article to understand the creation of Clustered Column chart

Create SSRS Report using Stored Procedure 9

Click on the Preview button to see the report preview.

Create SSRS Report using Stored Procedure 10

From the above screenshot you can see that, we created a 3D Clustered Column chart using the SP_ProductSales stored procedure. Let me Format the chart a bit for classy look. Remember, we already explained the Formatting options in Formatting Column Chart article so, Please refer the article to understand the steps.

Create SSRS Report using Stored Procedure 11

Thank You for Visiting Our Blog