I recently worked with a customer who used the 5-4-4-accounting period schedule. This means that each quarter is made up of 13 weeks, with the first month of each quarter being 5 weeks long and the other 2 being 4 weeks long. This makes for a 364 day year, which means once every 7 years or so you need to have a 53 week year. The challenge is that the year always starts either a few days before or after January 1.

This customer had some fairly complex Crystal Reports formulas that were difficult to maintain each year and so I tried to come up with a simpler process. I came up with a set of 3 formulas, Continue Reading »
Calculating periods in 5-4-4

One of my roles as a Crystal Reports consultant is to troubleshoot reports that mysteriously stop working. Often the culprit is what I call a “sleeping” table in the report that somehow gets activated. A sleeping table is a table that is not being used in the report, but that someone has decided to include anyway. I recommend that you avoid sleeping tables, even though they don’t have any effect on how the report behaves (now). They invite problems down the road.

Say you create an initial draft of a report that requires 3 tables, but you decide to include an extra table. You don’t need this table now but you figure you might want to use it in a later version of the report. So you add all 4 tables Continue Reading »
Dont rouse the sleeping tables

When you place a large cross-tab in the report header, page numbers won’t appear until the cross-tab is complete. This is because the page number is usually in the page header/footer and these sections have to wait until the Report Header has completely printed. So, if the cross-tab takes up 3 pages, you won’t see page numbers until page 4. Some folks have tried putting the page number in the Report Header with the Cross-tab, however this will only print the page number on the first page.

The workaround is to create what I call a “dummy” group. Continue Reading »
Multi-page cross-tabs missing page numbers

I find many formulas using the ToNumber() function in Crystal Reports formulas to convert numeric text into a true numeric value. Usually it is because they don’t know the advantages of using the Val() function. While ToNumber() works, it will generate an error if the string has any characters, punctuation or symbols in it. The usual solution is to test the value by saying:

If NumericText ({field}) then ToNumber ({field}) else 0

However, even this doesn’t work well if characters come after the numerics. For instance Continue Reading »
ToNumber() vs VAL():