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 and link them together. You then use 3 of the tables and leave the 4th one sleeping. You test and polish the report and it works perfectly. Next week someone tries to use a field from that extra (sleeping) table. At this point the report will often develop problems like duplicate records, missing records or sometimes it will display no records at all. But the table configuration in the report hasn’t changed.

The problem stems from the fact that Crystal Reports generates a SQL statement for the report based on the fields used in the report. Therefore, Crystal will not include any sleeping tables in the SQL because none of their fields are needed. You don’t see the effect of including a table in a report unless you use at least one field from that table. When you incorporate the first field from a sleeping table it becomes activated and is then included in the SQL. Often that is the first time you see the distortion caused by this table.

If you get distortion when you activate a sleeping table, it is typically caused by one of two things. It could be that the sleeping table is the ‘many’ end of a one-to-many relationship, which causes the original records to duplicate. Or it could be that some records in the original report don’t have a match in the newly activated table, which causes some or all of the report records to disappear.

So here are the 2 things that I recommend, especially for people still learning about their data:

1) Try to remove any sleeping tables from the report. That way you won’t accidentally distort your data by activating these tables with a future change. Note that a table used as an intermediary to join two tables on either side that are both being used is not really a sleeping table.

2) If the sleeping table is going to be needed later, then take at least one field from that table and put it physically on the report somewhere. Even in a suppressed section. This way the table is activated and you know that your results take this table into account. Now you can test to make sure this table doesn’t distort your report.