Date Parameter for Previous Work Day (No Sat. or Sun) 1

[kalvis]

I am using MS SSRS 2005. I created a report with two date parameters for From and To to filter for all records on a specific day (normally yesterday). The parameter Non-queried default values are currently set to

DateFrom: =format(dateadd("d",-1,Now()),"M/d/yyyy")
DateTo: =format(dateadd("d",1,Now()),"M/d/yyyy")

This effectively gives me all records for yesterday. But I would like to use a subscription on this report and have the default values take into account weekends. Can anyone tell me a way to change to date parameters to only select weekdays, which in this case is Monday through Friday. Thanks in advance.

 

[SgtJarrow]

Based on your definition.....Saturday, Sunday and Monday reports would be the same as they all have the same previous work day... So you really only need to check if the report date is Sunday and back up two days then or Monday and back up three.

DateFrom: =format(dateadd("d",IIF(Weekday(Today)=1,-2,IIF(Weekday(Today)=2,-3,-1)),Now()),"M/d/yyyy")

If you meant something else, you might want to look at this code. It the same as the IIF above, but you can modify it much easier where needed. You need to create a function of your own and put it in the code section of the report. Then refer to that code to get your appropriate day...

Public Function GetPrevWorkDay() As String
    Select Case WeekDay(Today)
        Case 1
            Return Format(DateAdd("d", -2, Today), "M/d/yyyy")
        Case 2
            Return Format(DateAdd("d", -3, Today), "M/d/yyyy")
        Case Else
            Return Format(DateAdd("d", -1, Today), "M/d/yyyy")
    End Select
End Function

'Usage In Report Expression Field:
=Code.GetPrevWorkDay()

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[kalvis]

Both solutions you proposed worked perfectly. Thank you very much for your help!