Formula Naming Tips

Wednesday 18 October 2006 @ 11:03 am

If a Crystal report has many formulas, and they aren’t named well, the report can become very difficult to understand. What seems clear today can seem confusing a few weeks down the road – even when working on your own reports. So, here are a few tips that I follow to keep things clear. They stem from the fact that the list of formulas is sorted by name, so keeping formulas in a logical order involves considering how the name will sort in the list. My goal is to see the formula list in chunks rather than as a random list of names.

1. If you need to have a series of almost identical formulas that are numbered, for instance if you create a sales formula for each of 12 months, it is better to use the month number than the name. So I would use Sales 01 and Sales 02 instead of January Sales and February Sales. Using the names means the formulas end up sorted all over the list. It is harder to see these formulas as a set if you need to update them all.

2. Note that in the last formula the numbers have a leading zero so that all 12 months will be 2 digits. I used Sales 01 instead of Sales 1. If I didn’t use that leading zero the months would sort as strings and not be in the correct order. (They would sort 1, 10, 11, 12, 2, 3, etc). The leading zeros keep them in the correct order.

3. Select the first word of the formula name carefully. This is what places the formula in its position in the list of formulas. So if you want a group of formulas about Cost to be together in the list, start them all with the word Cost.

4. Don’t be afraid to rename your formulas to improve the organization of the list. Remember that Crystal will automatically update the name of the formula inside all of the other formulas where it is used.

5. Be sure to delete any formula once you decide that is no longer needed. If I am not sure about a formula I will put an X at the front of the name so that it falls to the bottom of the formula list. Then I know that this formula should not be used.

6. When working with variables it is common to have several formulas that all work with the same variable. This is especially true with variable based Running Totals where you might have one formula that accumulates, one that displays and a third that resets the variable. I would start each of these with the name of the variable so that all 3 stay together in the formula list.

7. If you have the same formula, like a percentage formula, that occurs at different groups levels I would name them the same but add the name of the group onto the end, like this:

On Time Percent Customer
On Time Percent Region
On Time Precent Grand

Again, this keeps the similar items together in the list, but makes it clear what each one does. If I had several sets of these formulas I would try to make sure that each one follows the same pattern.

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

Leave a Reply

Recrystallize Pro