April 17, 2014
Hot Topics:
RSS RSS feed Download our iPhone app

Dynamic Default Date Parameters in SQL Server 2000 Reporting Services

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

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
    





Page 1 of 2



Comment and Contribute

 


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

 

 


Sitemap | Contact Us

Rocket Fuel