A Single-Parameter Date Range in SQL Server Reporting Services

  • SQLBlimp

    SSCertifiable

    Points: 6325

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

  • Desh Maharaj

    SSC Rookie

    Points: 46

    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.

  • eavesdm

    SSC Journeyman

    Points: 98

    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.

  • eavesdm

    SSC Journeyman

    Points: 98

    @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?

  • Phil Parkin

    SSC Guru

    Points: 244655

    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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • andrew.ing

    SSC Eights!

    Points: 847

    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)

  • dgreen-1126628

    SSC-Addicted

    Points: 443

    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

  • SQLBlimp

    SSCertifiable

    Points: 6325

    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.

  • SQLBlimp

    SSCertifiable

    Points: 6325

    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.

  • SQLBlimp

    SSCertifiable

    Points: 6325

    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.

  • joeroshan

    SSChampion

    Points: 10377

    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]

  • Phil Parkin

    SSC Guru

    Points: 244655

    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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • jshahan

    SSCarpal Tunnel

    Points: 4622

    joeroshan (11/18/2015)


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

    +1

  • jsm60

    SSC Enthusiast

    Points: 191

    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

  • Fernando Margueirat-273899

    SSC Enthusiast

    Points: 179

    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 51 total)

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