Creating Professional SSRS Report based on Stored Procedure

This article talks about creating a professional SSRS report based on a stored procedure to meet the business specification.

Additionally, the readers of this article will learn how to implement the report procedure correctly to run a report according to its frequency.

This article also highlights the importance of properly coded report stored procedure as compared to a free form of T-SQL script which is harder to maintain over the passage of time.

About SSRS Report based on Stored Procedure

It is generally recommended to think of the database object, such as stored procedure, and its functionality from meeting the reports business requirements perspective.

We are going to understand this through some simple examples.

Example #1

If you are given a task to develop an SSRS report to show total sales per day, the first thing you need to begin with is a mapping of these requirements to a database object, such as stored procedure, and then bringing it forward to run behind the required SSRS report.

Example #2

If you receive a business requirement that states that the end user is interested in viewing sales for a specified period, you can either use the query-based approach with parameters in the dataset of the report or you can use a stored procedure with parameters to take the value for a specified period.

One of the benefits of using a stored procedure is a direct reference to the object (stored procedure) in case of any problem with the functionality of the report rather than opening the report going to the dataset and investigating the script in use.

This saves time and implements a more professional approach to reports development unless otherwise stated.

Example #3

Another very common professional reports example is when a report developer is asked to develop a daily, monthly, quarterly or yearly sales report then again it is far better to build a database object such as stored procedure or view to embed this logic rather than writing a T-SQL script behind a report dataset although there are few exceptions to this rule.

Available Options with Example #3

If we take a closer look at the example #3 in which the report developer is asked to develop a daily, monthly, quarterly or yearly sales report, the report developer has the following two options to proceed further:

Option #1:

1. Create the stored procedure with parameters to show sales figures.
2. Initialize the report parameters at runtime so that it becomes the report with the required frequency (daily, monthly, quarterly, etc.).

Option #2:

1. Create the stored procedure without any parameters to show the sales figures.
2. Also write the code for required frequency (daily, monthly, quarterly, etc.) for the report within the stored procedure.

Pre-requisites

Please go through the pre-requisites for this article in order to follow the walkthrough.

T-SQL and Reporting Basics

The article assumes that readers are familiar with the basics of T-SQL scripts and database reporting.

Must-Read Reference Article

The article assumes that you are well familiar with the concepts and you have implemented the walkthrough in the reference article How to Write Stored Procedure for Professional SSRS Reports.

As a result, you must have created a sample database ITSales including the stored procedure for the report.

Setup sample database (ITSales)

You can skip this step if you have already implemented the walkthrough in the reference article because you have already set up the sample database and the desired stored procedure.

If you are already familiar with the concepts and their implementation in the reference article and would like to skip reading and implementing the reference article,  please create a sample database named ITSales with the database objects including the stored procedure for the report as follows:

-- Create sample database (ITSales)
CREATE DATABASE ITSales;
GO


USE ITSales;

-- (1) Create monthly sale table in the sample database
CREATE TABLE MonthlySale (
  SaleId INT PRIMARY KEY IDENTITY (1, 1)
 ,SellingDate DATETIME2
 ,Customer	VARCHAR(50)
 ,Product VARCHAR(150)
 ,TotalPrice DECIMAL(10,2)
)

GO

-- (2) Populate monthly sale table
SET IDENTITY_INSERT [dbo].[MonthlySale] ON
INSERT INTO [dbo].[MonthlySale] ([SaleId], [SellingDate], [Customer], [Product], [TotalPrice]) VALUES (1, N'2019-05-01 00:00:00', N'Asif', N'Dell Laptop', CAST(300.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[MonthlySale] ([SaleId], [SellingDate], [Customer], [Product], [TotalPrice]) VALUES (2, N'2019-05-02 00:00:00', N'Mike', N'Dell Laptop', CAST(300.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[MonthlySale] ([SaleId], [SellingDate], [Customer], [Product], [TotalPrice]) VALUES (3, N'2019-05-02 00:00:00', N'Adil', N'Lenovo Laptop', CAST(350.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[MonthlySale] ([SaleId], [SellingDate], [Customer], [Product], [TotalPrice]) VALUES (4, N'2019-05-03 00:00:00', N'Sarah', N'HP Laptop', CAST(250.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[MonthlySale] ([SaleId], [SellingDate], [Customer], [Product], [TotalPrice]) VALUES (5, N'2019-05-05 00:00:00', N'Asif', N'Dell Desktop', CAST(200.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[MonthlySale] ([SaleId], [SellingDate], [Customer], [Product], [TotalPrice]) VALUES (6, N'2019-05-10 00:00:00', N'Sam', N'HP Desktop', CAST(300.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[MonthlySale] ([SaleId], [SellingDate], [Customer], [Product], [TotalPrice]) VALUES (7, N'2019-05-12 00:00:00', N'Mike', N'iPad', CAST(250.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[MonthlySale] ([SaleId], [SellingDate], [Customer], [Product], [TotalPrice]) VALUES (8, N'2019-05-13 00:00:00', N'Mike', N'iPad', CAST(250.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[MonthlySale] ([SaleId], [SellingDate], [Customer], [Product], [TotalPrice]) VALUES (9, N'2019-05-20 00:00:00', N'Peter', N'Dell Laptop', CAST(350.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[MonthlySale] ([SaleId], [SellingDate], [Customer], [Product], [TotalPrice]) VALUES (10, N'2019-05-25 00:00:00', N'Peter', N'Asus Laptop', CAST(400.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[MonthlySale] ([SaleId], [SellingDate], [Customer], [Product], [TotalPrice]) VALUES (11, N'2019-06-03 00:00:00', N'Sarah', N'iPad', CAST(300.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[MonthlySale] ([SaleId], [SellingDate], [Customer], [Product], [TotalPrice]) VALUES (12, N'2019-06-05 00:00:00', N'Sam', N'Dell Laptop', CAST(350.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[MonthlySale] ([SaleId], [SellingDate], [Customer], [Product], [TotalPrice]) VALUES (13, N'2019-06-10 00:00:00', N'Akeel', N'Acer Laptop', CAST(300.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[MonthlySale] ([SaleId], [SellingDate], [Customer], [Product], [TotalPrice]) VALUES (14, N'2019-06-12 00:00:00', N'Asif', N'iPad', CAST(400.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[MonthlySale] ([SaleId], [SellingDate], [Customer], [Product], [TotalPrice]) VALUES (15, N'2019-06-14 00:00:00', N'Sarah', N'Lenovo Desktop', CAST(400.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[MonthlySale] ([SaleId], [SellingDate], [Customer], [Product], [TotalPrice]) VALUES (16, N'2019-06-15 00:00:00', N'Sahil', N'HP Desktop', CAST(500.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[MonthlySale] ([SaleId], [SellingDate], [Customer], [Product], [TotalPrice]) VALUES (17, N'2019-06-20 00:00:00', N'Akeel', N'iPad', CAST(350.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[MonthlySale] ([SaleId], [SellingDate], [Customer], [Product], [TotalPrice]) VALUES (18, N'2019-06-24 00:00:00', N'Mike', N'Dell Desktop', CAST(300.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[MonthlySale] ([SaleId], [SellingDate], [Customer], [Product], [TotalPrice]) VALUES (19, N'2019-06-25 00:00:00', N'Sahil', N'Lenovo Laptop', CAST(500.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[MonthlySale] ([SaleId], [SellingDate], [Customer], [Product], [TotalPrice]) VALUES (20, N'2019-06-26 00:00:00', N'Peter', N'Acer Laptop', CAST(350.00 AS Decimal(10, 2)))
SET IDENTITY_INSERT [dbo].[MonthlySale] OFF;
GO

-- (3) Create report stored procedure to show monthly sales
CREATE PROCEDURE ShowMonthlySales
AS
SET NOCOUNT ON
BEGIN

DECLARE @CurrentYear INT
,@CurrentMonth INT
, @AfterEndDate DATETIME
, @StartDate DATETIME

SET @CurrentYear=YEAR(GETDATE())
SET @CurrentMontH=Month(GETDATE())

-- Calculating first day of current month
SET @AfterEndDate=DATEFROMPARTS(@CurrentYear,@CurrentMonth,1)

-- Calculating first day of last month
SET @StartDate=DATEADD(MM,-1,@AfterEndDate) -- Subtracting one month from AfterEndDate

-- Show sales from first day of last month and just before first day of current month
SELECT s.SellingDate,s.Customer,s.Product,s.TotalPrice FROM MonthlySale s
where s.SellingDate>=@StartDate and s.SellingDate<@AfterEndDate
order by s.SellingDate

END

Quick Check

Assuming the current date is 07 July 2019, run the stored procedure against sample database ITSales to see the results:

--Test run ShowMonthlySales procedure
EXEC ShowMonthlySales

The output is as follows:

Now that we can see the desired stored procedure is returning the correct results, we can proceed further with building an SSRS database report to show monthly sales.

Tip: Bear in mind that in case of a professional reports development scenario, a unit test must be written to ensure that the procedure is working properly and you only proceed further after the unit test is passed.

Creating SSRS Report based on Procedure

Review Basic Steps (optional)

If you are new to the SSRS reports,  refer to the following article to see the detailed steps of creating a basic SSRS report and defining its dataset.

Create SSRS Report Project

Open Visual Studio and create a new report server project under a solution named Monthly Sales Report Project as follows:

Create Monthly Sales Report as a New Item

Right-click Reports, click Add, click Add New Item.., type the name of the report, MonthlySalesReport, and Click Add:

Add Data Source

Right-click Data Sources under Report Data, click Add Data Source…, type the name of the data source, DS_MonthlySales, connect to the sample database, ITSales, and Click OK as shown below:

Add Dataset to use stored procedure

In this step, we are going to bring forward the report procedure to be used with the report dataset so that the running of the report is based on the stored procedure.

There is no script that needs to be written since we are using the stored procedure which also defines the business requirement. So it is a bit of relief in terms of the report design time since we are concerned with report development only while the business logic is provided by the stored procedure which is well tested by now.

Right-click Datasets under Report Data, click Add Dataset…, type the name of the dataset as DSet_MonthlySales, and choose the Use a dataset embedded in my report option:

Select DS_MonthlySales in the Data source drop-down list box, and select Stored Procedure as a Query type:

Quick Check

If you look at your report dataset, it should show the list of the report fields (output columns of the stored procedure) as follows:

Add Report Page Header

Right-click anywhere on the report designer and click Add Page Header followed by inserting a text box and setting the text to Monthly Sales Report:

Add Table Object and Drag Drop Report Fields

Right-click report designer, click Insert, and then click Table:

Drag the report fields under dataset one by one, and drop them to the table object:

Add Report Execution Date and Time

Expand Built-in Fields, drag Execution Time, and drop it in the middle of the report title and format it accordingly:

Format Date Field and Run the Report

Right-click the SellingDate field, click Text Box Properties, select Number from the left bar, and select Date under Category, and 31 Jan 2000 under Type:

Click the Preview tab to see the results:

Congratulations! You have successfully created an SSRS monthly sales report based on a  stored procedure just like the professional report developers.

Things to do

Now that you can write stored procedures for monthly reports, try the following things to improve your skills further:

1. Try creating a report based on a stored procedure to show the daily sales figures based on the start date and after the end date logic (see the reference article).
2. Keeping the monthly sales logic in mind, add some more data to the sample and then create a report based on a stored procedure for the yearly sales figures.
3. Try replacing the dataset T-SQL script in the article with a report stored procedure which shows all the articles written by the authors based on the year value based on run time.