dcsimg
December 6, 2016
Hot Topics:

Dynamic Default Date Parameters in SQL Server 2000 Reporting Services

  • June 20, 2005
  • By Paul Whitaker
  • Send Email »
  • More Articles »

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

The following stored procedure gets the pertinent dates based on the current date.

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.

Conclusion

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



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Sitemap | Contact Us

Thanks for your registration, follow us on our social networks to keep up-to-date
Rocket Fuel