Using formula fields and calculations in the selection formula

Friday 18 May 2012 @ 5:18 pm

I recently needed a selection formula that automatically selected the last “half month”.   In other words if the report was run in the first half of a month it should return the last half of the previous month.  If the report was run in the last half of a month then the report should return the first half of that same month.     So I wrote the following selection formula:

if Day (CurrentDate) > 15
then ( day ({charges.date}) <=15 and {charges.date} in MonthToDate)
else (day ({charges.date}) >15 and {charges.date} in LastFullMonth)

This worked correctly, but I knew that it would not be very fast, because it would not pass the date criteria to the database (SQL Query).  That meant that the database would have to send back ALL of the dates, and CR would have to apply the date rule locally.  CR won’t translate the DAY() function, and several other functions, into SQL.   So as a short-term fix I added a second criteria.  First I turned the formula above into a formula field called {@DateCriteria} and then I changed my selection formula to say:

{charges.date} > CurrentDate - 60 AND {@DateCriteria}

The first rule above does what I call a ‘rough cut’.  It lets the database return a small number of records, but still ensures that the report gets all of the records that it needs.   Then the report can use the local formula to eliminate any unneeded records that were included in the rough cut.   It is logically redundant but in some cases it is the most efficient approach.  That was an improvement.

But in this case I found an even better solution.  I also needed to display the date range on the page header, so I created formulas for StartDate and EndDate :

//@startdate
if Day (CurrentDate) > 15
then minimum (MonthToDate)
else minimum (LastFullMonth) + 15

//@enddate
if Day (CurrentDate) > 15
then minimum (MonthToDate) + 14
else minimum (MonthToDate) -1

These calculate exactly the dates for the beginning and ending of the desired range.   Once I had finished them I realized that because these two formulas do not require any data from the database, they can be calculated before the selection formula happens (BeforeReadingRecords).   That meant that I could use them in my selection formula and the values would get sent to the database just as if I had hard coded them into the selection formula.  So I simplified my selection formula to:

{charges.date} in {@Start Date} to {@End Date}

Checking the SQL I could see that the those dates were being sent to the WHERE clause.

So if find that a calculation in your selection formula is slowing thing down, see if part of that calculation can be done without the database.  You can still use parameter values, or today’s date and time or similar things.  You may find that you can do these calculations outside the selection formula, and then use the results in the criteria to make your query run faster.

And if that can’t be done you might also try adding a redundant rule to the selection formula to do a rough cut.

If you need help implementing one of these techniques give me a call.

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)







Leave a Reply

Jeff-Net
Recrystallize Pro

Crystal Reports Server