Finding Last Business Day 1

[lynchg]

I am using Crystal 11.5 with Lotus Notes 7 views as datasources for my report.

I am trying to create a report that returns data starting with the previous business day.

I was working on a function that does that, but I need to consider our corporate holidays. Here is a draft of the function with a list of holiday dates hard-coded in an array. This function will keep stepping back one day at a time until it finds a date that is not a Sunday and is not in the array list. I want that date to be the start of the date range for data that gets included in my report.

Function LastBusinessDate() As Date
Dim BadDay As Boolean
Dim PreviousDate As Date

BadDay = TRUE
PreviousDate = CDate(DateAdd("d", -1, CurrentDate))

DO WHILE BadDay = TRUE
IF DayOfWeek(PreviousDate) = 1 OR PreviousDate IN ARRAY (#11/22/2007#, #11/23/2007#, _
#12/24/2007#, #12/25/2007#, #12/31/2007#, #01/01/2008#) THEN
PreviousDate = CDate(DateAdd("d", -1, PreviousDate))
ELSE
BadDay = FALSE
END IF
LOOP

LastBusinessDate = PreviousDate

END Function

And then I use this expression as a record selection formula:

Date({MyTable.ImplementationDate}) >= LastBusinessDate

It works fine but I would like to replace the array list with some kind of a table lookup. We have a Notes view that lists our corporate holidays, I have that view as the second datasource for this report, but I don't know how to access that in a function or a formula.

 

[lbass]

If the date field from your existing datasource contains all dates, you could do a left join from that date field to the holiday date field in the view. Then you would just ensure that the previous date was not a holiday.

Otherwise, you could add the holiday view in a subreport, collect the dates in a shared date array, which you would then reference and check in the current formula.

-LB

 

[lynchg]

If I am not mistaken the left join would exclude any records with a timestamp in the holiday dates list from the report entirely. I didn't explain it quite enough in detail, but I DO want to include data from holidays, I just don't want the report data time range to begin on a holiday date.

I am curious about how I would create a shared data array, I am not familiar with that.

Thanks

 

[lbass]

If you set up your holiday view in a subreport in the report header, you can then add the date field to the detail section of the sub and then create a formula like:

whileprintingrecords;
shared datevar array x;
shared numbervar i := i + 1;

if i < 1000 then (
redim preserve x[i+1];
x := {table.holidaydate}
);

You can suppress all sections within the subreport, format the sub to "suppress blank subreport", and then format the report header section to "suppress blank section" if you don't want the sub to show.

Then in the main report, you can reference the array like this:

whileprintingrecords;
shared datevar array x;
if {table.date} in x then //etc.

-LB

 

[lynchg]

Is the type of code in your example only valid in Crystal syntax in a formula?

I need to continue in the loop until I have stepped back enough days to arrive at a valid business date, and then return that date, or evaluate a date field on my report based on the result of the loop. The looping available within formulas doesn't seem to allow that. That is why I was trying to work this into a function, but functions don't allow the inclusion of data fields.

I can't seem to get a formula to come up with the result that I want.

Thanks again for your help.

 

[lbass]

I'm not sure what the Basic syntax would be, but you should be able to create and reference the variable using Basic syntax like:

DO WHILE BadDay = TRUE
IF DayOfWeek(PreviousDate) = 1 OR PreviousDate in x
THEN
PreviousDate = CDate(DateAdd("d", -1, PreviousDate))
ELSE
BadDay = FALSE
END IF
LOOP

I'm not sure how to convert the inital Crystal formula into Basic or how to declare it in the current formula, but maybe you do.

-LB

 

[lynchg]

LB,

I spent a lot of time trying to use basic syntax for the initial formula in the subreport and I couldn't get it to work. So I decided to leave it as crystal syntax and use basic for the LastBusinessDate function.

It looks like it is working. I pushed it back to Labor Day weekend by initializing the PreviousDate value to point to 9/3, simulating a report that would run on Tues. 9/4, and it began the report with the data for Sat. 9/1, excluding Labor Day and Sun. 9/2, which is just what I wanted.

I am not quite sure how it works, but it works.

Thank you very much!