Jan 222015

I’ve had to do a fair bit of updating Crystal Report syntax as of late.  As I had been told by the software integrators four years ago, Crystal is something you just have to fiddle with once you get past the basics.  Once you’ve figured out which tables your data is in, the rest is a sequence of trial and error until you become adept at knowing which tables to select and what formulas to use.  Most of the reports I’ve had to design thus far query invoices for sales figures.  Due to the nature in which the query is made, duplicate records are often returned.

While Google says that duplicate records can be filtered out by using section settings with a suppression formula that looks something like this:

I found that while the detail seemed to work as expected, a cross-tab report would often include data that should have been suppressed.  Also, depending on the nature and purpose of the report, the detail might exclude the first record processed in the query.

In writing a report that queried sales data from invoices based the preselected criteria of a particular set of items on the invoice, duplicate records had to be excluded where more than one item could appear on the same invoice and return a duplicate record.

After several variations of the suppression formula, I opted not to use one at all. By filtering the duplicates in the formula field itself, the problem of duplicates was solved.  The following syntax yielded the results I wanted, and returned accurate data for use in the cross-tab as well.