Thursday, January 10, 2013

Default values for date parameters in SSRS

I will create a report that will have 3 parameters:

Interval: type: int with the following available values:

StartDate: type: DateTime; this will have different default values depending on the value of the Interval parameter.

The default values for the StartDate are:

For this week (first day of current week, Monday)
DateAdd("d", 1 - DatePart(DateInterval.WeekDay, Today(),FirstDayOfWeek.Monday), Today())

For last week (first day of last week, Monday)

DateAdd(DateInterval.Day, -6,DateAdd(DateInterval.Day, 1-Weekday(today),Today))


For current month
DateSerial(Year(DateTime.Now),Month(DateTime.Now),1)

For last month
DateSerial(Year(DateTime.Now),Month(DateTime.Now)-1,1)

The DateSerial function automatically generates the correct date also in January. If the value for the month = 0 then it sets month 12 of the previous year (year is also changed).

For last quarter: 
DateAdd("q",DateDiff("q","1/1/1900",today)-1,"1/1/1900")

For last year
DateSerial(Year(DateTime.Now)-1,1,1)


We set the default values using the switch function:


switch(Parameters!Interval.Value=1,DateAdd("d", 1 - DatePart(DateInterval.WeekDay, Today(),FirstDayOfWeek.Monday), Today())
Parameters!Interval.Value=2,DateAdd(DateInterval.Day, -6,DateAdd(DateInterval.Day, 1-Weekday(today),Today)),
Parameters!Interval.Value=3,DateSerial(Year(DateTime.Now),Month(DateTime.Now),1),
Parameters!Interval.Value=4,DateSerial(Year(DateTime.Now),Month(DateTime.Now)-1,1),
Parameters!Interval.Value=5,DateAdd("q",DateDiff("q","1/1/1900",today)-1,"1/1/1900"),
Parameters!Interval.Value=6,DateSerial(Year(DateTime.Now)-1,1,1)
)



EndDate: type: DateTime; this will have different default values depending on the value of the Interval parameter.


For this week (last day of current week, Sunday)
DateAdd("d" ,7- DatePart(DateInterval.WeekDay,Today(),FirstDayOfWeek.Monday),Today())

For last week (last day of last week, Sunday)
DateAdd(DateInterval.Day, -0,DateAdd(DateInterval.Day, 1-Weekday(today),Today))

For current month
DateSerial(Year(DateTime.Now), Month(DateTime.Now)+1,0)

For last month
DateSerial(Year(DateTime.Now), Month(DateTime.Now),0)

If the day = 0, the date will become the last day of the previous month.


For last quarter: 
DateAdd("s",-1,DateAdd("q",DateDiff("q","1/1/1900",Today),"1/1/1900"))

For last year
DateSerial(Year(DateTime.Now)-1,12,31)


Again we set the default values using the switch function:


switch(Parameters!Interval.Value=1,DateAdd("d" ,7- DatePart(DateInterval.WeekDay,Today(),FirstDayOfWeek.Monday),Today()),
Parameters!Interval.Value=2,DateAdd(DateInterval.Day, -6,DateAdd(DateInterval.Day, 1-Weekday(today),Today)),
Parameters!Interval.Value=3,DateSerial(Year(DateTime.Now), Month(DateTime.Now)+1,0),
Parameters!Interval.Value=4,DateSerial(Year(DateTime.Now), Month(DateTime.Now),0)
Parameters!Interval.Value=5,DateAdd("s",-1,DateAdd("q",DateDiff("q","1/1/1900",Today),"1/1/1900"))
Parameters!Interval.Value=6,DateSerial(Year(DateTime.Now)-1,12,31)
)




No comments:

Post a Comment