Written by 01:06 Business intelligence, Database development, SSRS, Stored Procedures

How to Write Stored Procedures for Professional SSRS Reports

In this article, we’ll talk about professional SQL Server Reporting Services reports, how to create stored procedures for these reports to meet business requirements, and the benefits of using stored procedures to run behind SSRS reports.

About Professional SSRS Reports

Let’s first get familiar with professional SSRS reports.

Simple Definition

A professional SSRS report is a report created with the standard practices of report development in mind and which has been thoroughly tested to be capable of fulfilling business requirements.

In other words, a professional SSRS report is very carefully designed, developed, tested, and deployed to the target environment to make sure it serves the purpose and is beneficial to the business.

Examples

A simple example of a professional SSRS report is a monthly sales report which gives an insight into how good the business is doing in terms of selling its services or products.

Another example of a professionally developed SSRS report is a report which shows daily business transactions from the business perspective.

Frequency of professional SSRS reports

Frequency of a report describes how often this report should be run to show the latest figures which can then be sent to related departments within the company or to external clients.

A professional SSRS report’s frequency can be one of the following:

Daily Report

A daily report, as the name indicates, must run on a daily basis to be accessed or sent to its internal and/or external subscribers.

Weekly Report

A report which shows weekly figures and is sent to the recipients every week.

Monthly Report

A monthly report contains data for the whole month and is meant to be delivered each month.

Yearly Report

A yearly report gives more insight into the data by calculating yearly figures for its subscribers and end users.

Report based on any financial period

Reports which follow any other financial period come under this category.

About the Frequency of Reports

Please bear in mind that the most important report when it comes to frequency is the daily report.

In fact, it’s not just the report – the data you prepare for the report on a daily basis is also important.

If you have prepared data for a daily report, then preparing data for a weekly report is the same as running daily reports for a whole week. A monthly report, in its turn, is the same as running a weekly report four times.

However, if you are only asked to create a monthly report, then preparing data for the report on a monthly basis is also acceptable, and is recommended in some circumstances over preparing daily data.

The details of this are beyond the scope of this article, but the reason I mentioned this here is to highlight the importance of developing a daily report which does require preparing data for the report on a daily basis. This can also show how to prepare data for weekly, monthly and yearly reports.

Stored Procedures for Reports Overview

The Role of a Stored Procedure

Stored procedures play a vital role in professional SSRS reports since they get the required data for the report from the database.

Stored procedures provide many benefits when used for reporting purposes.

Business Logic and Stored Procedures

Stored procedures are highly recommended to implement reporting business logic.

Ease of database unit testing

Stored procedures can be easily unit-tested to ensure that they meet the business specification and implement business logic for the SSRS report.

Security

Using stored procedures to run a report requires access rights to the required stored procedure – this can be managed with database users or roles.

This access management for stored procedures written for reporting purposes helps to build a secured reporting solution in which only specific users are able to access and run the reports.

Maintenance

SSRS reports based on stored procedures are easy to maintain since only changes in the stored procedure are required, with no need to modify and keep track of free-form scripts for the report data sets.

Pre-requisites

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

Please read the article SSRS Reports Development in Simple Words to get a quick understanding of how to create a simple SSRS report.

Setup a sample database (ITSales)

To start things off, let’s create a sample database named ITSales which contains IT sales figures:

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


USE ITSales;

-- (1) Create a 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 the 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

Requirements, Analysis and Planning

Before you start working on this project, let’s have a look at the expected business requirement for our monthly report and a preliminary analysis.

Statement

The monthly report business requirement may come to you in the following form:

“As a business user I want to view a monthly sales report”

Hint

Please look for the keywords in the business requirement such as monthly, daily or weekly to understand what frequency the resulting report should have.

Preliminary Analysis

Since this is a monthly sales report, it either requires parameters to be supplied at run time automatically or relies on the code of the stored procedure to calculate monthly sales.

A monthly report shows figures for a complete month – this means that the report should keep the following things in mind:

  1. The report must show figures from the previous month based on the current date
  2. The report must dynamically calculate the previous month of the current year

Finally, we have to choose the best suitable database object. And a stored procedure is the best option to deal with this kind of business reporting requirement.

Development Plan

The development plan is simple:

  1. Create a Stored Procedure to show monthly sales figures
  2. Create a SSRS report to show monthly sales figures based on the stored procedure

You can approach this in one of the following ways:

  1. A stored procedure with no parameters, and the procedure calculates both the last month time period and the sales
  2. A stored procedure with parameters, where the procedure calculates sales, and the report calculates the last month time period

Please remember that, in both cases you have to write some code to achieve this since SSRS does not come with an out-of-the-box solution.

Create and Test-Run a Report Stored Procedure

The first thing here would be to create a stored procedure which can fulfill the business requirement.

Choosing the Procedure without Parameter Approach

Here, we will meet the business specification by using a stored procedure without parameters. This means that we are not only going to calculate sales using the stored procedure, but we’ll also compute the monthly sales time period based on the current date inside the stored procedure rather than doing it at run time.

Understating the Monthly Report Logic

The monthly report does not really meana sales report for the current month’ because the current month is not complete yet, so we have to deal with the latest complete month – that is, the previous month.

This is how most of the professional monthly reports are built unless stated otherwise.

For example, if the current date is 06 July 2019, then we expect the monthly sales report to show us sales for June 2019 because June is the last complete month.

Designing the Monthly Report Logic

There are many ways to design monthly report logic in T-SQL.

The Concept of StartDate and AfterEndDate

This is the most important concept in professional reporting with daily, monthly, weekly or yearly frequencies.

We have to query the sales tables from a start date through the end date – the latter can also be called after end date.

So, assuming that the current date is 06 July 2019, we must initialize the start date (StartDate) with 01 Jun 2019 and after end date (AfterEndDate) with 01 Jul 2019, but this should be performed dynamically each time the procedure is called.

Computing the After End Date

AfterEndDate is the next day after the last month is finished.

Assuming that today is 06 July 2019, the AfterEndDate will be 01 July 2019.

The simplest way to achieve this is to use DateFromParts() function which takes Year, Month and Day parameters to setup a date.

Run the following T-SQL code to get AfterEndDate by using the DateFromParts() function:

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

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

SET @AfterEndDate=DATEFROMPARTS(@CurrentYear,@CurrentMonth,1)

SELECT GETDATE() AS CurrentDate,@AfterEndDate as Next_Day_After_Last_Complete_Month

The output is as follows:

Calculating After End Date

Computing the Start Date

The Start Date (StartDate) is the first day of the last month. This can also be calculated by the DATEFROMPARTS() function along with the DATEADD() function.

Subtracting one month from AfterEndDate by using the DATEADD() function, we get the start date for the last month.

Please see the following T-SQL code:

-- Computing StartDate and AfterEndDate for the monthly report
DECLARE @CurrentYear INT
,@CurrentMonth INT
, @AfterEndDate DATETIME
, @StartDate DATETIME

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

SET @AfterEndDate=DATEFROMPARTS(@CurrentYear,@CurrentMonth,1)
SET @StartDate=DATEADD(MM,-1,@AfterEndDate) -- Subtracting one month from AfterEndDate


SELECT GETDATE() AS CurrentDate,@StartDate AS StartDate,@AfterEndDate as Next_Day_After_Last_Complete_Month

The output is as follows:

Calculating Start Date

The ShowMonthlySales stored procedure

We are going to code the stored procedure in such a way that it computes the correct time period plus the monthly sales based on the above calculations, without the need for parameters.

Create the stored procedure as follows:

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 the first day of the current month
SET @AfterEndDate=DATEFROMPARTS(@CurrentYear,@CurrentMonth,1)

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

-- Show sales from the first day of last month through the first day of the 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

Test-Running the stored procedure

Assuming the current date is 06 July 2019, let’s run the stored procedure against the sample database ITSales to see the results:

--Test-run the ShowMonthlySales procedure
EXEC ShowMonthlySales

The output is as follows:

Test-running the Stored Procedure

Congratulations! You have successfully created a stored procedure that can run behind a professional monthly sales report. As long as it remains untouched and business requirements don’t change, the procedure does the job throughout the year.

Things to do

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

  1. Create a report stored procedure to show daily sales figures based on the start date and after end date logic discussed in this article
  2. Keeping the monthly sales logic in mind, please try to create a report procedure for yearly sales figures
  3. Create a SSRS report to show monthly sales based on the stored procedure mentioned in this article while using the following articles for reference:
Tags: , Last modified: September 21, 2021
Close