I was helping a user implement my formula for calculating the most recent business date. He wanted to use the most recent business date before today as the starting point for his date filter. We were able to calculate the correct date and use it in the selection formula correctly, but I noticed that the date criteria wasn’t passing to the SQL. This was affecting the performance of the report.

I tried starting the formula with “BeforeReadingRecords” since it didn’t need any field values from the database. But then it couldn’t see the holidays formula which stores the list of holidays in a second separate formula.  I had trouble assigning both formulas to BeforeReadingRecords and still have one formula reference the other. Then I realized that I could combine them into one formula by putting the holiday array at the beginning of the business dates formula. As soon as did that I could assign the combined formula to be BeforeReadingRecords, and then the value was passed successfully to the WHERE clause.

So I had to think back about why I originally wrote the two formulas separately.  I think in general I thought it was more efficient to have the holiday array done once in the report header. That way I could use it in several different business date formulas and Crystal wouldn’t have to recreate the holiday list on every record as it did the business date calculation. But in this case I found that it was more efficient to move the holidays than it was to have the database return all dates.