Search code examples
reporting-servicesparametersexpressionssrs-expression

How can I compute and display a date range in an SSRS report field?

In the report I'm working on, the user enters a begin date and an end date. Typically, the two will span two weeks, such as if they enter 5/15/2016 for the begin date and 5/28/2016 for the end date.

What I need to show in column headings are the first week over one column (which, in the case above, would be 5/15 - 5/21/16) and the second week over another (which would be in this case 5/22 - 5/28/16).

To achieve this, what do I need to do in the Textbox Expression properties for these two column headers, assuming the parameter BegDate contains 5/15/2016 and the parameter EndDate contains 5/28/2106?

Solution

  • Try:

    First Week:

    =Parameters!BegDate.Value & " - " &
DATEADD(DateInterval.Day, 7 - DATEPART(DateInterval.Weekday,Parameters!BegDate.Value), Parameters!BegDate.Value)

    Second week:

    =DATEADD(DateInterval.Day, -DATEPART(DateInterval.Weekday,Parameters!EndDate.Value)+1, Parameters!EndDate.Value)
& " - " & Parameters!EndDate.Value

    Example:

    enter image description here

    Let me know if this helps.