SSRS Report Builder weekly report excluding entries which begin/end outside of the week

0
votes

I am using SSRS Report Builder to generate a weekly visitor report in which the data is pulled from a SharePoint list calendar.

I have two parameters, one for Start_Time and one for End_Time:

Start_Time expression:  =DateAdd("d", 8 - DatePart("w", Today()), Today())

Stop_Time expression:   =DateAdd("d", 15 - DatePart("w", Today()), Today())

The data has a filter (sorry, I'm new and they won't let me post a screenshot of the filter):

"Expression [Start_Time]

Operator -- Between

Value [@Start_Time]  [@End_Time]"

The report pulls the SharePoint calendar entries correctly if they start/stop within the week. However, if they begin or end outside of the week, the entries do not show up in the report at all.

I would like the report to show all entries which fall within the week pulled by the report, whether or not they begin/end outside of the week. Thanks!

1
sharepointreporting-servicesparametersfilterexpression
You can add a link to a screenshot and someone with the ability will edit it into your question – Chris Latta

1 Answers

0
votes

Firstly, you are using Sql syntax in your VBA expressions. The parameter value expressions should should use VBA syntax and be like this:

Start_Time parameter expression: 

=DateAdd(DateInterval.Day, 8 - DatePart(DateInterval.Weekday, Today), Today)

End_Time parameter expression: 

=DateAdd(DateInterval.Day, 15 - DatePart(DateInterval.Weekday, Today), Today)

You want to include calendar entries that span the range, so where the start time is before the end of your date range and where the stop time is after the start of your date range.

So add two filter criteria: 

Start_Time <= @End_Time
Stop_Time >= @Start_Time