A Single-Parameter Date Range in SQL Server Reporting Services

  • Comments posted to this topic are about the item A Single-Parameter Date Range in SQL Server Reporting Services

  • Hi. Thanks for that. it is very help full. But, Mostly in Financial reporting, it is required to present the reports by Month1, Month2, Month3....Month60 as separate columns of data so we can compare data on a monthly basis.

  • Good stuff. It should be coded better, though, perhaps; given the international audience and user-base and the long-established international standard ISO 8601 (https://xkcd.com/1179/) shouldn't this have been written to use the ISO standard date formats rather than assume/require everyone uses the American date formats? As written, this fails in the UK, the rest of Europe and other areas.

    However, as a concept it's much quicker and smaller than a dedicated calendar table for this use.

  • @Desh - if you have one of the columns being the month then in your tablix put that month column as a column header, sorted appropriately, would this (begin to) achieve what you want?

  • eavesdm (11/18/2015)


    Good stuff. It should be coded better, though, perhaps; given the international audience and user-base and the long-established international standard ISO 8601 (https://xkcd.com/1179/) shouldn't this have been written to use the ISO standard date formats rather than assume/require everyone uses the American date formats? As written, this fails in the UK, the rest of Europe and other areas.

    However, as a concept it's much quicker and smaller than a dedicated calendar table for this use.

    Agreed. Also, there is generally no need to perform an expensive cast to varchar() and back again. For example:

    select Period = convert(varchar(100), 'Year To Date')

    ,BeginDate = dateadd(yy, datediff(yy, 0, getdate()), 0)

    ,EndDate = dateadd(dd, datediff(dd, 0, getdate()), 0)

    union all

    select Period = convert(varchar(100), 'Next Year')

    ,BeginDate = dateadd(yy, 1, dateadd(yy, datediff(yy, 0, getdate()), 0))

    ,EndDate = dateadd(day,-1,dateadd(yy, 1, dateadd(yy, datediff(yy, 0, getdate()) + 1, 0)))

    Good article and good idea though.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Excellent article - this technique is invaluable, especially for scheduling reports with varying periods.

    Uncannily, after a long time thinking "there must be a better way to choose dates", I sat down this very week and wrote pretty much the same thing!

    My approach is slightly different. I didn't want to join a DateRange table to all the data sources so I make the DateRange selector "populate" (via auto refresh) two normal but hidden StartDate and EndDate parameters. The StartDate and EndDate parameters are consumed by the data source as normal.

    I can also leave the StartDate and EndDate parameters visible to allow users still to override the standard DateRanges with ad-hoc periods.

    My implementation is as a stored procedure which can be called once to populate the DateRange drop-down list, and again to obtain the selected dates to set the default values of the StartDate and EndDate parameters.

    I'm expecting to expand the list of ranges...

    CREATE PROCEDURE [parm].[DateRanges]

    /*

    Use to populate SSRS date ranges to provide a set of standard relative date ranges

    (e.g. Last Day, Last Month).

    PARAMETER

    @DateRangeId : set to 0 to return all date ranges. Use this mode for populating a

    dropdown list parameter named "DateRangeId", Label "Date Range".

    */

    @DateRangeId int = 0

    AS

    SET NOCOUNT ON;

    DECLARE @Yesterday date = DATEADD(d, -1, CAST(getdate() AS date))

    DECLARE @LastMonday date = DATEADD(WEEK, DATEDIFF(WEEK, '19060101', GETDATE()), '19060101');

    DECLARE @LastDayOfPrecedingMonth date = CAST(DATEADD(dd,-(DAY(GETDATE())), GETDATE()) AS date) ;

    DECLARE @FirstDayOfPrecedingMonth date = CAST(DATEADD(dd,-(DAY(@LastDayOfPrecedingMonth)-1), @LastDayOfPrecedingMonth) AS date) ;

    DECLARE @FirstDayOfYesterdaysMonth date = CAST(DATEADD(dd,-(DAY(@Yesterday) - 1), @Yesterday) AS date);

    WITH CTE AS (

    SELECT * FROM

    ( VALUES

    ( 1, 'Today', CAST(GETDATE() AS date), CAST(GETDATE() AS date) ),

    ( 2, 'Yesterday', @Yesterday, @Yesterday ),

    ( 3, '2 Days Ago', DATEADD(dd, -1, @Yesterday), DATEADD(dd, -1, @Yesterday) ),

    (101, 'Last 7 Days', DATEADD(dd, -6, @Yesterday), @Yesterday ),

    (102, 'Week to Yesterday', DATEADD(DAY, -6, @Yesterday), @Yesterday ),

    (201, 'Last 10 Days', DATEADD(dd, -9, @Yesterday), @Yesterday),

    (301, 'Calendar Month to Yesterday', @FirstDayOfYesterdaysMonth, @Yesterday),

    (302, 'Two Months to Yesterday', DATEADD(MM, -1, @FirstDayOfYesterdaysMonth), @Yesterday),

    (303, 'Three Months to Yesterday', DATEADD(MM, -2, @FirstDayOfYesterdaysMonth), @Yesterday),

    (306, 'Six Months to Yesterday', DATEADD(MM, -5, @FirstDayOfYesterdaysMonth), @Yesterday)

    ) DT (DateRangeId, DisplayName, StartDate, EndDate)

    )

    SELECT

    CTE.DateRangeId AS DateRangeKey,

    CTE.DisplayName AS DateRangeLabel,

    CTE.StartDate,

    CTE.EndDate

    FROM

    CTE

    WHERE

    (@DateRangeId = 0)

    OR

    (@DateRangeId = CTE.DateRangeId)

  • Great article. Couple of typos in your code that do not affect the execution. Look for "EndDateunion" aliases.

    Thanks for the article.

    [Edit]

    BTW, we have a commercial product that does something similar and allows the following options you might consider coding:

    Today, Yesterday, Tomorrow,

    Current week, month, quarter, semester, year

    Last # days, weeks, months, quarters, semesters, years

    Last week, month, quarter, semester, year

    Next # days, weeks, months, quarters, semesters, years

    # days ago

  • eavesdm (11/18/2015)


    Good stuff. It should be coded better, though, perhaps; given the international audience and user-base and the long-established international standard ISO 8601 (https://xkcd.com/1179/) shouldn't this have been written to use the ISO standard date formats rather than assume/require everyone uses the American date formats? As written, this fails in the UK, the rest of Europe and other areas.

    However, as a concept it's much quicker and smaller than a dedicated calendar table for this use.

    Thanks. I will look into this and perhaps issue a follow-up to the article.

  • Desh Maharaj (11/18/2015)


    Hi. Thanks for that. it is very help full. But, Mostly in Financial reporting, it is required to present the reports by Month1, Month2, Month3....Month60 as separate columns of data so we can compare data on a monthly basis.

    Hi - This seems to be a separate issue. This would help you to obtain the range of dates for which you need to retrieve rows. To separate monthly totals to columns, I would use a PIVOT.

  • Phil Parkin (11/18/2015)


    eavesdm (11/18/2015)


    Good stuff. It should be coded better, though, perhaps; given the international audience and user-base and the long-established international standard ISO 8601 (https://xkcd.com/1179/) shouldn't this have been written to use the ISO standard date formats rather than assume/require everyone uses the American date formats? As written, this fails in the UK, the rest of Europe and other areas.

    However, as a concept it's much quicker and smaller than a dedicated calendar table for this use.

    Agreed. Also, there is generally no need to perform an expensive cast to varchar() and back again. For example:

    select Period = convert(varchar(100), 'Year To Date')

    ,BeginDate = dateadd(yy, datediff(yy, 0, getdate()), 0)

    ,EndDate = dateadd(dd, datediff(dd, 0, getdate()), 0)

    union all

    select Period = convert(varchar(100), 'Next Year')

    ,BeginDate = dateadd(yy, 1, dateadd(yy, datediff(yy, 0, getdate()), 0))

    ,EndDate = dateadd(day,-1,dateadd(yy, 1, dateadd(yy, datediff(yy, 0, getdate()) + 1, 0)))

    Good article and good idea though.

    Hi - Thanks for the advice; I'd not seen great expense in casting the year to varchar versus a dateadd on top of a datediff.

    Thanks

    John.

  • Great idea, thanks for taking time to share your work.

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • SQLBlimp (11/18/2015)


    Phil Parkin (11/18/2015)


    eavesdm (11/18/2015)


    Good stuff. It should be coded better, though, perhaps; given the international audience and user-base and the long-established international standard ISO 8601 (https://xkcd.com/1179/) shouldn't this have been written to use the ISO standard date formats rather than assume/require everyone uses the American date formats? As written, this fails in the UK, the rest of Europe and other areas.

    However, as a concept it's much quicker and smaller than a dedicated calendar table for this use.

    Agreed. Also, there is generally no need to perform an expensive cast to varchar() and back again. For example:

    select Period = convert(varchar(100), 'Year To Date')

    ,BeginDate = dateadd(yy, datediff(yy, 0, getdate()), 0)

    ,EndDate = dateadd(dd, datediff(dd, 0, getdate()), 0)

    union all

    select Period = convert(varchar(100), 'Next Year')

    ,BeginDate = dateadd(yy, 1, dateadd(yy, datediff(yy, 0, getdate()), 0))

    ,EndDate = dateadd(day,-1,dateadd(yy, 1, dateadd(yy, datediff(yy, 0, getdate()) + 1, 0)))

    Good article and good idea though.

    Hi - Thanks for the advice; I'd not seen great expense in casting the year to varchar versus a dateadd on top of a datediff.

    Thanks

    John.

    And in this case, you are right! I should have checked execution plans etc before posting. My apologies.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • joeroshan (11/18/2015)


    Great idea, thanks for taking time to share your work.

    +1

  • Great article, it's like you read my mind, I just started back working with SSRS and was just planning on doing something exactly like this. Thanks!

    Jacque

  • Thanks for this great tip!

    One small change that I would make to avoid the "DR.EndDate+1" part and still be able to easily link to dates in other table is to create a new column adding end of day (i.e. 23:59:59.997) to the EndDate of period that have already ended and GETDATE() for current periods (e.g. last month has ended, but month to date is current). This way you can have something like

    SELECT b.Period, sales = SUM(a.trn_amt)

    FROM T_TRANSACTION a

    JOIN V_DATE_RANGE b ON a.trn_dt BETWEEN b.BeginDate AND b.EODEndDate

    GROUP BY b.Period

    FM

Viewing 15 posts - 1 through 15 (of 50 total)

You must be logged in to reply to this topic. Login to reply