It is not unusual for reports to show the previous x weeks or months numbers. Report builders can simply create a start and end date parameter and leave (the intelligence) to the operator for them to fill it in. In some instances, not that we’re questioning anybody’s ability to count weeks or months backwards, we’d rather set default values programmatically. The solution, not only saves the operator a few clicks, it can also be handy if we are supporting reports whose week start day vary or loobacks vary.
Programmatically set default date parameters in SSRS to previous n weeks or months. The solution should be easily understood by report builders who may not be real life programmers.
One can achieve this by using functions directly in the parameter but one of the advantages of using a custom code is re-usability and readability. It being reusable enables the report builder to code it once and call it multiple times within the report or even across reports. Within-expression functions are not the easiest codes to read, write, and maintain. So having the codes neatly written and formatted make our report builders lives a bit more happier.
I this solution, we will use embedded code for simplicity. We can use the same code to create an assembly that any report can reference but that will be for another blog. Let’s get on with the steps.
1. Open the report in report builder and right click outside the body (visible white page of the report) and select Report Properties.
2. Select code tab and add the code below to it and click OK to close.
3. Right click Parameters in Report Data and add a parameter. Set data type to Date/Time.
4. Go to Default Values, select specify values, and click the ‘fx’ button to launch expression editor. In it, use the code function help below to set your desired default values.
Code Function Help
A weekly summary report requires that the last 12 weeks (excluding the current week) be shown by default. The client’s week start on a Wednesday.
Start Date = Code.WeekStart(false , “Wednesday”, 12)
End Date = Code.WeekEndDate( false, “Wednesday”)
A non summary report requires that the month’s data be shown by default.
Start Date =Code.MonthStart(true, 1)
End Date = Code.MonthEnd (true )
Week Start Date
Code.WeekStart(IncludeCurrentWeek, ” WeekStartDay“, LookBackWeeks)
Week End Date
Code.WeekEndDate(IncludeCurrentWeek , “WeekStartDay”)
Month Start Date
Month End Date
- WeekStartDay is the Client’s week start date. Do not remove the quotes (“). Replace it with one of the following: Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday.
- LookBackWeeks/LookBackMonths is the number of weeks/months to return in the report
- IncludeCurrentWeek/IncludeCurrentMonth is true if you want to return the current week/month. Set it to false otherwise.
- Only use month scripts on normal calendar months where the start day is 1st day of that month and end date is the last day of that month.
- Only use week script on regular 7 day weeks.
Public Shared Function WeekStart(IncludeCurrentWeek As Boolean, WeekStartDay As String, LookBackWeeks As Single) As Object
Dim OriginWeekStart As Date = OriginWeekDate(WeekStartDay)
WeekStart = DateAdd("d", (DateDiff("d", OriginWeekStart, Today()) \ 7) * 7 - (7 * (LookBackWeeks - Convert.ToInt32(IncludeCurrentWeek))), OriginWeekStart)
Public Shared Function WeekEnd(IncludeCurrentWeek As Boolean, WeekStartDay As String) As Object
Dim OriginWeekStart As Date = DateAdd("d", 6, OriginWeekDate(WeekStartDay))
WeekEnd = DateAdd("d", (DateDiff("d", OriginWeekStart, Today()) \ 7) * 7 + Convert.ToInt32(IncludeCurrentWeek), OriginWeekStart)
Public Shared Function MonthStart(IncludeCurrentMonth As Boolean, LookBackMonths As Single) As Object
MonthStart = DateAdd("m", DateDiff("m", "1900-01-01", Today()) - LookBackMonths - Convert.ToInt32(IncludeCurrentMonth), "1900-01-01")
Public Shared Function MonthEnd(IncludeCurrentMonth As Boolean) As Object
MonthEnd = DateAdd("d", -1, DateAdd("m", DateDiff("m", "1900-01-01", Today()) - Convert.ToInt32(IncludeCurrentMonth), "1900-01-01"))
Public Shared Function OriginWeekDate(WeekStartDay As String) As Object
Select Case WeekStartDay
OriginWeekDate = "1900-01-01"
OriginWeekDate = "1900-01-02"
OriginWeekDate = "1900-01-03"
OriginWeekDate = "1900-01-04"
OriginWeekDate = "1900-01-05"
OriginWeekDate = "1900-01-06"
OriginWeekDate = "1900-01-07"
Download sample report file