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.
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:
- The report must show figures from the previous month based on the current date
- 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:
- Create a Stored Procedure to show monthly sales figures
- Create a SSRS report to show monthly sales figures based on the stored procedure
You can approach this in one of the following ways:
- A stored procedure with no parameters, and the procedure calculates both the last month time period and the sales
- 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 mean ‘a 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:
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:
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:
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:
- Create a report stored procedure to show daily sales figures based on the start date and after end date logic discussed in this article
- Keeping the monthly sales logic in mind, please try to create a report procedure for yearly sales figures
- Create a SSRS report to show monthly sales based on the stored procedure mentioned in this article while using the following articles for reference:
