SSRS Last Business Day of Previous Month vbscript

Question

I need some help writing an SSRS expression for the last business day of the previous month.

I have tried the following, which gives me the last calendar day of the prior month, but I need the last business day.

= DateAdd("d", -1, DateSerial(Year(Now), Month(Now), 1))

and

=dateadd("d",-1,dateadd("q",datediff("q","1/1/1900",today()),"1/1/1900"))

Thanks in advance.

Answer 1

You can do this by breaking it down into two steps. Create a textbox (lets call it endoflastmonth).

Set the expression to the following:

=DateAdd(DateInterval.Minute, -1, DateSerial(Year(Date.Now), Month(Date.Now), 1))

Now create another text box and put in the following expression:

=format(DateAdd("d"
    , Switch(DatePart("w", reportitems!endoflastmonth.Value) = 2, -3
        ,DatePart("w", reportitems!endoflastmonth.Value) = 1, -2
        ,True, -1)
    , reportitems!endoflastmonth.Value),"dd/MM/yyyy")

You should in theory, have the last business day of previous month in this text box.

The format on the second one is to get the date in "dd/MM/yyyy" format.. you can format it however you like.

If you want it as a parameter.. you have two choices.. one is use SQL to get the value you want.. but since you want it done in the expression.. you can do it like this.. I don't know if it is the best way though

= DateAdd("d"
    , Switch(DatePart("w", DateAdd(DateInterval.Minute, -1, DateSerial(Year(Date.Now), Month(Date.Now), 1))) = 2, -3
        ,DatePart("w", DateAdd(DateInterval.Minute, -1, DateSerial(Year(Date.Now), Month(Date.Now), 1))) = 1, -2
        ,True, -1)
    , DateAdd(DateInterval.Minute, -1, DateSerial(Year(Date.Now), Month(Date.Now), 1)))