Writing a crystal report and it doesn't bring back expected records (Helpdesk Classic)

Version 2

    This is something which people who are quite new to Crystal hit quite often so I thought it was worth posting on here some general advice.  If you have dragged tables onto your report but are not seeing any or all the records in the report detail then one of the first things to check is how the tables have been linked together.


    1 - Check the columns that are linking the tables together


    When crystal is installed it often has "smart linking" switched on as default.  This means that it tries to link together the tables for you by looking for likely primary keys and columns on tables that you have added to the report.  I would recommend switching this off and manually linking the tables together as you understand the way in which your tables join better.


    Switching off smart linking in Crystal 10



    Switching off smart linking in Crystal 11



    To check how your tables in the report have been linked together select the Database - Database Expert option from the menu and then select the Links tab.  Expand this window to see the links between columns on the tables on your reports.


    If you aren't sure which Classic / Helpdesk tables you need in your report then here is a document which cover the most commonly used:

    There is further information on Helpdesk tables provided in your manuals.

    2 - Check the join type between the tables


    If you have checked that the linking on the tables is correct then check the type of joins between the tables.  To do this, remain in the Database Expert - Links tab and right-click on the line going between two tables.  Select Link Options.  Under Join Type see what is currently set and consider trying a Left Outer join.  To understand left outer joins please search for left outer in your Crystal help.  An extract "It also includes a row for every record in the primary (left) table for which  the linked field value has no match in the lookup table. For instance, you can  use a Left Outer join to view all customers and the orders they have placed, but  you also get a row for every customer who has not placed any orders."


    3 - Remove tables from the report


    If you have tried checking the linking between the tables and the join type of the tables, save a copy of your report and remove tables from it one by one.  You do this via  Database - Database Expert option from the menu and then select the Data tab.  You will need to also remove any fields on the report which reference this table too.  If you remove one table at a time and then try running the report again, at some stage you should find that you do now see all the records you want on the report.  It will be the table that you last removed from the report which wasn't linked correctly and so was stopping records from displaying on your report.  Revert back to your saved copy of the report and investigate the linking further...