Dynamic Default Date Parameters in SQL Server 2000 Reporting Services

Monday Jun 20th 2005 by Paul Whitaker
Share:

Many business intelligence reports call for date-driven parameters. These parameters are often based on the current date. Learn how to develop a DataSet of commonly used dates and use this DataSet to populate the default parameters in a SQL Server 2000 Reporting Services report.

SQL Server 2000 Reporting Services offers a robust report parameter interface that is exposed through the report designer, its Web service interface, the report manager, and via scheduled reports. The fact that this interface supports dynamic default parameters is essential to providing timely intelligence to consumers with minimal interaction.

Many business intelligence reports depend on date parameters based off of the current date. For example, a revenue report might need to be run at the beginning of each month, representing data from the previous month. Other examples include weekly or daily summary reports.

To facilitate serving this common need, I've created a series of database objects to calculate commonly used relative dates, and outlined the process for adding a list of commonly referenced dates as a dataset in the report designer. These dates then can be added as default parameters.

Creating SQL Server Objects

To facilitate a series of dynamic date parameters, I have created a series of User-Defined Functions that represent some commonly requested default dates.

They each operate with Monday as the first day of the week, which must be explicitly set with the command SET DATEFIRST 1.

  1. "Get Week Start" returns the beginning (00:00:00) of the Monday of the week passed to the function. "Get Week End" returns the end (23:59:59.997) of the Friday of the week passed to the function.

  2. CREATE FUNCTION get_week_start (@date datetime)
    RETURNS datetime AS
    BEGIN
       return dateadd(yyyy, datepart(yyyy,
          dateadd(weekday,1-datepart(weekday, @date),@date))-1900, 0)
        + dateadd(dy, datepart(dy,
          dateadd(weekday,1-datepart(weekday, @date),@date))-1,0)
    END
    CREATE FUNCTION get_week_end (@date datetime)
    RETURNS datetime AS
    BEGIN
       return dateadd(yyyy, datepart(yyyy,
          dateadd(weekday,7-datepart(weekday, @date),@date))-1900, 0)
        + dateadd(ms, -3,
          dateadd(dy, datepart(dy,
         dateadd(weekday,7-datepart(weekday, @date),@date)),0) )
    END
    

  3. "Get Month Start" and "Get Month End" return the start and end of the current month.

  4. 
    CREATE FUNCTION get_month_start (@date datetime)
    RETURNS datetime AS
    BEGIN
       RETURN dateadd(m,datediff(m,0, @date),0)
       END
    CREATE FUNCTION get_month_end (@date datetime)
    RETURNS datetime AS
    BEGIN
       RETURN dateadd(ms, -3, dateadd (m,datediff(m,0,
              dateadd(m,1,@date)),0))
    END
    

  5. "Get Yesterday Start" and "Get Yesterday End" return the start and end of the day prior to the parameter.

  6. 
    CREATE FUNCTION get_yesterday_start (@today datetime)
    RETURNS datetime AS
    BEGIN
       RETURN dateadd(day, -1, datediff(d,0,@today))
    END
    CREATE FUNCTION get_yesterday_end (@today datetime)
    RETURNS datetime AS
    BEGIN
       return dateadd(ms, -3, datediff(d,0,@today))
    END
    

  7. "Get Today Start" and "Get Today End" represent the start and end of the date passed.

  8. 
    CREATE FUNCTION get_today_start (@today datetime)
    RETURNS datetime AS
    BEGIN
       return dateadd(day, 0, datediff(d,0,@today))
    END
    CREATE FUNCTION get_today_end (@today datetime)
    RETURNS datetime AS
    BEGIN
       return dateadd(ms, -3, datediff(d,0,dateadd(d,1,@today)))
    END
    

  9. "Get Weekday Start" and "Get Weekday End" return the start and end of the weekday specified within the week passed as date. For example, to get the start and end of Tuesday of the current week, pass the parameters 2 and getdate().

  10. 
    CREATE FUNCTION get_weekday_start (@weekday tinyint,
                                       @date datetime)
    RETURNS datetime AS
    BEGIN
       return dateadd(yyyy, datepart(yyyy,
          dateadd(weekday,@weekday-
          datepart(weekday, @date),@date))-1900, 0)
        + dateadd(dy, datepart(dy,
          dateadd(weekday,@weekday-datepart(weekday, @date),
                                            @date))-1,0)
    END
    CREATE FUNCTION get_weekday_end (@weekday tinyint,
                                     @date datetime)
    RETURNS datetime AS
    BEGIN
       return dateadd(yyyy, datepart(yyyy,
          dateadd(weekday,@weekday-
          datepart(weekday, @date),@date))-1900, 0)
        + dateadd(ms, -3,
          dateadd(dy, datepart(dy,
          dateadd(weekday,@weekday-datepart(weekday, @date),
                                            @date)),0) )
    END
    

  11. In a similar fashion, the following functions generate dates as indicated in the function name.

  12. 
    CREATE FUNCTION get_year_start (@date datetime)
    RETURNS datetime AS
    BEGIN
       RETURN DATEADD(year,DATEDIFF(year,0, @date),0)
    END
    
    CREATE FUNCTION get_tomorrow_noon(@date datetime)
    RETURNS datetime
    BEGIN
       RETURN DATEADD(hh, 12, DATEADD(d,DATEDIFF(d,-1, @date),0))
    END
    
    CREATE FUNCTION get_today_noon(@date datetime)
    RETURNS datetime
    BEGIN
       RETURN DATEADD(hh, 12, DATEADD(d,DATEDIFF(d,0, @date),0))
    END
    

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.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved