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

Question

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?

score 1 · Answer 1 · answered Jun 02 '16 at 15:59

1

Add a new column to your dataset that contains the first day of the week. You can do this in SQL like this:

DATEADD(DAY, 1-DATEPART(WEEKDAY, @d), @d)

You could also do it as a calculated field in your dataset.

Now you simply refer to this field in your textbox. It could even be a column grouping depending on what you need.

answered Jun 02 '16 at 15:59

StevenWhite

5,907
3
21
46

Thanks, but I don't want to alter the stored procedure; I want to do it within the report by writing an expression for the textbox. – B. Clay Shannon-B. Crow Raven Jun 02 '16 at 16:01

score 1 · Accepted Answer · answered Jun 02 '16 at 16:02

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:

Let me know if this helps.

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

2 Answers2