LawsonGuru Blog

Thought-Provoking Commentary for the Lawson Software Community

Troubleshooting "Duplicate Records" in Crystal Reports


If you haven’t discovered Ken Hamady, you need to check out his recent post on troubleshooting “duplicate records”.  This is perhaps the most frustrating issue that report designers encounter when developing reports against Lawson tables.

If you don’t have a good grasp on the JOINs/relationships that are used in Lawson’s S3 databases, you can really get in trouble in a hurry.  Some quick tips based on what I see quite often:

  • Make sure to include COMPANY when joining HR tables.  This eliminates “table scans” and will greatly improve report performance.  An simple example of this is the relationship between EMPLOYEE and PAEMPLOYEE.  Join not only on EMPLOYEE, but also on COMPANY.
  • Understand the difference between an “INNER JOIN” and a “LEFT OUTER JOIN”, and know when to use each.  An INNER JOIN between EMPLOYEE and PAYMASTR returns only the records that match the JOIN criteria on BOTH tables.  So, if you wanted to include employees on a report who have not been paid, an INNER JOIN will not work.
  • Using SQL Expressions rather than Formulas for selection criteria. Using SQL Expressions pushes the selection criteria back to the database, making the server do the work.   By contrast, record selection based on Formulas brings all of the records over the network into Crystal.  This is not only a potential performance issue, but depending on your selection criteria, and how your tables are joined, can result in erroneous reports.
  • Filter on record status fields where appropriate. For instance, a common mistake is reporting on all GLTRANS records—regardless of status.  Reporting on only “status 9” records will prevent you from including memo-entry, unreleased, and unposted entries in your report.
  • Deal with Lawson’s quirky way of storing “null dates” in the database.
  • Lastly, include the conditional/”subset switch” when JOINing tables based on a conditional index.  For example, when you JOIN from ACTRANS to PRDISTRIB to report payroll details, you need to include PRDSET5_SS_SW=’Y’ in the JOIN criteria in order to force the use of PRDISTRIB’s PRDSET5 index, which is based on ACTIVITY (not doing so will result in a table scan):

    WHERE PRD.PRDSET5_SS_SW = 'Y'
    AND PRD.ACTIVITY = ATN.ACTIVITY
    AND PRD.COMPANY = ATN.COMPANY
    AND PRD.ATN_OBJ_ID = ATN.OBJ_ID

Again, just a few tips to help you whip your reports into shape, and keep you out of trouble with your DBA!

Advertisements

3 responses to “Troubleshooting "Duplicate Records" in Crystal Reports

  1. Phil Simon July 8, 2009 at 8:42 pm

    The man knows his stuff. Very knowledgeable.

  2. Jeannette Larson January 12, 2010 at 1:50 pm

    This is good information.

    The link for –Deal with Lawson’s quirky way of storing “null dates” in the database– doesn’t work. I have used my own work-around, but would be interested in someone else’s take on the issue.

    • John Henley January 12, 2010 at 2:43 pm

      Thanks. I have updated the link.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: