One of my readers needed this formula and even gave it a shot.  But this is a tricky formula. I have had a version for years posted on my formulas page, but I hadn’t tried it for months so I took on the challenge.  The formula below will take a date of birth and any ‘as of’ date and calculate the age in years and months. The ‘as of’ date could be simply today’s date (CurrentDate), or it could be a transaction or visit date from the database. Note that the formula only counts complete years and complete months. It adjusts for Continue Reading »
Formula for age in years and months

I don’t know how many of you out there using Crystal with the ABRA HR/Payroll software, but I just had a real challenging episode with it and ended up learning quite a bit (the hard way).

My task was to speed up a report that took 3 hours to run. The problem was obvious – a recurring subreport that ran 900 times on a typical payroll. A subreport was used because linking the table would have required adding a filter to an outer join, something that the CR linking window can’t do.

Plan A:
To eliminate the subreport we needed a SQL Command that included a Left Outer Join to a subquery. But when I wrote the command it generated errors. So I started Continue Reading »
Using SQL commands in ABRA HR

How would you like your reports to be automatically run, exported to a PDF and delivered to your Email InBox every Monday morning at 6am? The Crystal Reports designer doesn’t provide a way to do this (unless you upgrade to CR Server or BO Enterprise). But if you look at third party products like those on my LINKS page you will find several reasonably priced or free tools that do this. Some do even more. So every March I go through the list and publish a feature comparison on my blog.

I discovered and added one new tool this year, bringing the pool of reviewed tools up to ten. The article provides a brief introduction to each product and describes what sets that product apart. Then there is a detailed feature matrix that shows the key specifics for comparison, including prices and the install base. To clarify the matrix terms I have written a feature glossary to explain what each feature means. Finally there are links to the vendor websites so that you can get more information on each product.

In May I will be updating a separate article that compares server based scheduling tools. If you think one person can manage all of your scheduling you are probably fine with one of the desktop tools, regardless of the number of people receiving the scheduled output. But if you plan to have multiple people scheduling reports then you may want to consider a server based tool.

When you do something one way for a long time, it can be hard to get out of the rut. So even though the Crystal formula editor has a pretty good ‘auto-complete’ feature, I still don’t take full advantage of it. I still tend to scroll through the list of fields. But recently I have started to take advantage of auto-complete to speed things up.

The auto-complete in CR has two components. One is for fields which was introduced in CR v12(2008). The other is for reserved words like functions and was introduced in CR v10.

Fields:
If you want to enter a field you simply Continue Reading »
Using auto-complete in Crystal Reports formulas