Reporting Services

This post is inspired from my 2+ years experience with Reporting Services 2005 & 2008 in a corporate reporting environment for which an SSRS implementation was pioneered, and 8+ years T-SQL experience.

The following bullets some things to keep in mind when starting a Reporting Services solution with MS SQL 2005 or better data source that is expected to scale for corporate reporting needs:

• Determine whether you will be using Native Mode or a custom Reporting Services interface. This decision will have a significant impact on the reporting architecture that is right for you. A custom interface will allow you to take advantage of .NET for example rather than TSQL.  This will provide your project with less exposure to the limitations of the Native Mode UI as well as cumbersome code.  (I have no experience with Microsoft SharePoint.)
• SQL CLR is your friend so use it. Parameter splitting is better off done in the .NET runtime as needs could change (like and boolean searches or escape characters for example). The last thing you want is complicated T-SQL code to deal with.
• Use stored procedures. If you have to resort to OPTION(recompile) or even WITH RECOMPILE it's still preferable to T-SQL in your reports.
• Come up with a standard report definition doc and share with your team mates.
• Make your reports provide validation for parameter values. In other words a text box in which someone types "Green Day" instead of "Green Bay", surely it is better that your report advises the user that "Green Day" is not a valid entry. This may seem a bit silly at first but remember some of these reports may be running from an automatic subscription and to miss Green Bay's sales could be awfully embarrassing.
• Create standard T-SQL procedure naming conventions and implementations for providing drop down values, creating parameter temp tables for there subsequent joining, lookup functions for constants, and validation functions.
• If you are planning on creating subscriptions be aware that SSRS is lacking in UI and will not support dynamic expressions (assuming that a user base would be able to do this in the first place.) Depending on the time you have your project you may want to consider creating a schema along with a series of stored procedure helpers to assist developers in dynamically creating subscription data. This way you can take advantage of the Data Driven Subscriptions which with some clever T-SQL development will give you all the dynamic data you need. (Hint: Don't forget about how useful dynamic SQL is when writing expressions.)
• If you are relying on native mode as the UI also keep in mind that Visual Studio's deploy target does not provide much for configuration options. Simple departmental organization of reports can be difficult to achieve. In my experience a custom deployment solution is best as this will allow for better control. A custom solution can link all RDL files to the same data source, linked reports are made copies instead of links (which overcomes the "landscape print as portrait" issue with linked reports), certain files can be automatically deployed hidden, HTML files deploy more gracefully and and a host of other good reasons.

These are some of the things you should keep in mind during early stages of an SSRS implementation. When everything is said and done a satisfactory Reporting Services can be achieved. Feel free to communicate with me on any of these topics I would be glad to help.

-- Good luck.

SQL Server 2005 - Date Advancement

Found a cool use for MSSQL 2005 - Common Table Expressions. I wrote this function utilizing recursion to add the number of days you specify skipping weekends. I am sharing this because I've seen a lot of these functions done poorly. This one works well-- no flaws discovered.



/****** Object: UserDefinedFunction [ufnAdvanceWeekDays] Script Date: 07/09/2010 10:20:10 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ufnAdvanceWeekDays]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION dbo.[ufnAdvanceWeekDays]

GO

/*
Summary
This function will increment or decerement the @advanceFromDateTime by @numberOfDaysToAdvance skipping weekends.
For example if @advanceFromDateTime is a Saturday or Sunday, and @numberOfDaysToAdvance is 1 then
the following Tuesday date will be returned, or previous Thursday if @numberOfDaysToAdvance is -1.
*/
create function dbo.ufnAdvanceWeekDays(@advanceFromDateTime datetime, @numberOfDaysToAdvance as int)
Returns datetime
AS
BEGIN

/*
declare @advanceFromDateTime datetime
declare @numberOfDaysToAdvance as int

set @advanceFromDateTime=getdate()+1
set @numberOfDaysToAdvance=1

select dbo.ufnAdvanceWeekDays(@advanceFromDateTime,@numberOfDaysToAdvance)

----769 calculations per second with 50 @numberOfDaysToAdvance
----78 per second with 500 @numberOfDaysToAdvance
----by the above results a close proportion can be made between @numberOfDaysToAdvance and calculations per second.
*/

declare @advancedDate datetime
,@SATURDAY as int
,@SUNDAY as int
,@advancement as int

set @advancedDate = @advanceFromDateTime
set @SATURDAY = 7
set @SUNDAY = 1

IF(@numberOfDaysToAdvance!=0)
BEGIN

if(@numberOfDaysToAdvance<0)
set @advancement=-1
ELSE
set @advancement=1

;WITH dateRecursionCTE([advanceDate],[isWeekend],numberOfDaysRemaining)
AS
(
---- Anchor Member
SELECT
@advanceFromDateTime advanceDate
, case when datepart(dw,@advanceFromDateTime) in (@SATURDAY,@SUNDAY) then 1 else 0 end isWeekend
, abs(@numberOfDaysToAdvance) numberOfDaysRemaining
UNION ALL
---- Recursive Member
select
_tmpCTEResults.advanceDate
,_tmpCTEResults.isWeekend
-- only decrement recursive terminator for weekdays
,(_tmpCTEResults.numberOfDaysRemaining-(abs(_tmpCTEResults.isWeekend-1))) numberOfDaysRemaining
from
(
SELECT
cte.advanceDate+@advancement advanceDate
, case when datepart(dw,cte.advanceDate+@advancement) in (@SATURDAY,@SUNDAY) then 1 else 0 end isWeekend
, numberOfDaysRemaining
from dateRecursionCTE cte
where numberOfDaysRemaining!=0
) _tmpCTEResults
)

--select * from dateRecursionCTE OPTION (MAXRECURSION 32767)

select @advancedDate =
(
select
@advanceFromDateTime+
((@advancement*sum(isWeekend))+@numberOfDaysToAdvance)
--advance date by requested days + weekends
from dateRecursionCTE
)
OPTION (MAXRECURSION 32767)
----Limited by an approximate 89 year date advancement

END

--select @advancedDate
return @advancedDate
END

GO

--GRANT EXEC ON dbo.[ufnAdvanceWeekDays]  TO someone