Dynamic Default Date Parameters in SQL Server 2000 Reporting Services
Selecting Common Dates
These functions are great general purpose date calculators. To better integrate with Reporting Services, I've created a function and stored procedure to populate an RS dataset.
The following is a table-valued function that returns all of the calculated dates. It allows you to SELECT from the list to get only the parameters you need.
CREATE FUNCTION udfCommonDates (@date datetime) RETURNS @t table (week_start datetime, week_end datetime, lastweek_start datetime, lastweek_end datetime, month_start datetime, month_end datetime, lastmonth_start datetime, lastmonth_end datetime, yesterday_start datetime, yesterday_end datetime, today_start datetime, today_end datetime, thisweek_monday_start datetime, thisweek_monday_end datetime, year_start datetime, tomorrow_noon datetime, today_noon datetime) BEGIN INSERT @t SELECT dbo.get_week_start ( @date ) AS week_start, dbo.get_week_end ( @date ) AS week_end, dbo.get_week_start ( DATEADD(d, -7, @date ) ) AS lastweek_start, dbo.get_week_end ( DATEADD(d, -7, @date ) ) AS lastweek_end, dbo.get_month_start( @date ) AS month_start, dbo.get_month_end ( @date ) AS month_end, dbo.get_month_start ( DATEADD(m,-1, @date) ) AS lastmonth_start, dbo.get_month_end ( DATEADD(m,-1,@date) ) AS lastmonth_end, dbo.get_yesterday_start ( @date ) AS yesterday_start, dbo.get_yesterday_end ( @date ) AS yesterday_end, dbo.get_today_start (@date) AS today_start, dbo.get_today_end ( @date ) AS today_end, dbo.get_weekday_start(1,@date) AS thisweek_monday_start, dbo.get_weekday_end(1,@date) AS thisweek_monday_end, dbo.get_year_start(@date) AS year_start, dbo.get_tomorrow_noon(@date) AS TomorrowNoon, dbo.get_today_noon(@date) AS TodayNoon,RETURN END
CREATE PROCEDURE uspCommonDates AS begin set datefirst 1 declare @date datetime set @date = getdate() select * from dbo.udfCommonDates(@date) end
Integrating with Reporting Services
Pulling these dates into Reporting Services via the Report Designer is a breeze. In the Data tab, simply add the uspCommonDates stored procedure as a new Dataset.
Now that you have a Dataset of commonly used dates, they are now available to you to set as Default values for the parameters. Simply select the new dataset and choose whichever "Value field" matches your desired date.
Business intelligence requirements are often bound to relative dates such as the previous day, previous week, or previous month. Generating a list of commonly used dates and setting the appropriate dates as default report parameters can facilitate the easy generation of time-based reports.
About the Author
Paul Whitaker works as a software developer for Autobase, Inc., an Indianapolis, Indiana-based company that produces software for the auto sales industry. He is finishing his Bachelor's degree in Computer and Information Technology at the Purdue School of Engineering & Technology at IUPUI, where he leads presentations on .NET-related topics as President of the school's .NET User Group.
Page 2 of 2