SSRS: Use Stored Procedures in Datasets

This is my contribution to T-SQL Tuesday #24 hosted by Brad Schulz (blog) on the subject of Prox ‘n’ Funx (Stored Procedures and Functions to you and me :-) ).

I'm a big believer in using Stored Procedures (or at the very least, UDFs) for your Reporting Services datasets. and separating your presentation layer from your data layer and moving the SQL code away from the RDL.

The benefits of this are that you as long as the meta-data of your Stored Procedure stays the same, then you able to modify and enhance your SQL code without having to touch the RDL. You essentially abstract away the source code from the report.

Perhaps you're improving performance by moving to JOINs from cursors, extended the business logic to only return rows that meet new criteria or simply doing a refactoring of SQL code to standardise your table names. All of these don't affect the presentation layer and having them reside as Stored Procedures on the database, gives huge maintenance benefits.

Other advantages include having all your T-SQL held in the one place and knowing that you are aware of the impact of any changes without having to worry about dependancies elsewhere. Also, you will often be re-using code (eg for parameter datasets) and using a single stored procedure helps reduce duplication of effort (and probably performance benefits too).

Of course, there are downsides to this approach. If you need to introduce a new parameter to a report (which is passed to your dataset) then you have to change both the RDL and the stored procedure. I can see this being a slight irritation as you now have 2 deployments whereas holding the SQL code "inline" means a simple upload of the new report.

For me though, the former approach still wins and I advocate using Stored Procedures for Reporting Services datasets. I've been experimenting recently with putting Stored Procedures used for Reports into their own schema (acting as a namespace) although I can't categorically say whether this has been a success or not (Jamie Thomson (Blog | Twitter) has an interesting blog post which touches on Schema usage here).

T-SQL: Get first day of the current month

You get a lot of people asking to find out first/last days of months based upon a certain date. Essentially, you can work out nearly anything you want just by getting day 1 of your current month and then using the date functions.

Although this is posted in hundreds of places on the web, for my own sanity as much as anything i'm going to post it here too.

DECLARE @TestDate DATE = '20110417';

SELECT DATEADD(M, DATEDIFF(M, 0, @TestDate), 0) AS NewDate;

-- NewDate
-- 2011-04-01 00:00:00.000

T-SQL: Get Next/Previous Business Day

In a previous role, we used a service from a company who provided data from the last working day of each month. The application to retrieve the data was an SSIS package which was scheduled via SQL Agent. Unfortunately, the SQL Agent scheduler doesn't (yet) have the abililty to provide custom schedules and there is no inbuilt function to "run on the last working day of the month".

Our solution to this was to implement a schedule table which held a RunDate and a TradeDate, include a task which checked to see if the current day was a valid day and then run the package daily. Nice and simple. The final step was to populate the schedule table with data for the next year and this is where I had to address the T-SQL challenge.

Fortunately, there are many solutions on the web for this sort of thing, but i'm going to post my objects here for reference.

NB: I like to keep all these type of generic functions in a Utils schema so that they can managed in a database project and deployed to a database as required.


CREATE FUNCTION Utils.IsWeekDay(@Dt DATE)
RETURNS BIT
AS
BEGIN
DECLARE @IsWeekDay BIT = 1

IF DATENAME(DW,@Dt) IN ('SATURDAY', 'SUNDAY')
SET @IsWeekDay = 0

RETURN @IsWeekDay
END
GO

CREATE FUNCTION Utils.GetNextWeekDay(@Dt DATE)
RETURNS DATE
AS
BEGIN
DECLARE @NxtDt DATE

SET @NxtDt = DATEADD(D,1,@Dt)

IF (SELECT Utils.IsWeekDay(@NxtDt)) != 1
BEGIN
SET @NxtDt = DATEADD(D,1,@NxtDt)

IF (SELECT Utils.IsWeekDay(@NxtDt)) != 1
SET @NxtDt = DATEADD(D,1,@NxtDt)
END

RETURN @NxtDt
END
GO

CREATE FUNCTION Utils.GetPreviousWeekDay(@Dt DATETIME)
RETURNS DATETIME
AS
BEGIN
DECLARE @NxtDt DATE

SET @NxtDt = DATEADD(D,-1,@Dt)

IF (SELECT Utils.IsWeekDay(@NxtDt)) != 1
BEGIN
SET @NxtDt = DATEADD(D,-1,@NxtDt)

IF (SELECT Utils.IsWeekDay(@NxtDt)) != 1
SET @NxtDt = DATEADD(D,-1,@NxtDt)
END

RETURN @NxtDt
END
GO


-- and this is how we use the functions to get the schedule table for 2011

DECLARE @Year CHAR(8) = '20110101';

WITH Months
AS
(SELECT 0 AS Mth, CONVERT(DATETIME, @Year) AS Dt
UNION ALL
SELECT 1 + Mth, DATEADD(M, 1+Mth, @Year) AS Dt
FROM Months
WHERE Mth < 11
)
SELECT CASE Utils.IsWeekDay(Dt) WHEN 1 THEN Dt ELSE Utils.GetNextWeekDay(Dt) END AS RunDate,
Utils.GetPreviousWeekDay(Dt) AS TradeDate
FROM Months
GO