Monday, July 30, 2012

Default values for a date parameter in SSRS

Introduction
                 We can use sql queries or some of the cool expressions available in SSRS to set default date parameters:


Set First Day of previous week (Monday)
=DateAdd(DateInterval.Day, -6,DateAdd(DateInterval.Day, 1-Weekday(today),Today))


Set Last Day of previous week (Sunday)
=DateAdd(DateInterval.Day, -0,DateAdd(DateInterval.Day, 1-Weekday(today),Today))


Set First Day of Current Week (Monday)
=DateAdd("d", 1 - DatePart(DateInterval.WeekDay, Today(),FirstDayOfWeek.Monday), Today())


Set Last Day of Current Week (Sunday)
=DateAdd("d" ,7- DatePart(DateInterval.WeekDay,Today(),FirstDayOfWeek.Monday),Today())


Set First Date of last month
=DateAdd("m", -1, DateSerial(Year(Now()), Month(Now()), 1))

Set Last date of last month
=DateAdd("d", -1, DateSerial(Year(Now()), Month(Now()), 1))

Set First date of current month
=DateSerial(Year(Now()), Month(Now()), 1)

Set Last date of current month
=DateAdd("d",-1,(DateAdd("m", 1, DateSerial(Year(Now()), Month(Now()), 1))))

0 comments:

Post a Comment