Crystal Reports Optional Table Linking and the Importance of Order

 Like with most things, Crystal Reports tends to be split into levels of complexity whenever possible. Beginner, Intermediate, Advanced are all used to describe books, courses and articles.

The problem is that reporting requirements are dictated by the client and by the nature of the database being reported from. In many cases the initial request may seem simple to implement, but may not be a good logical fit with how the database stores the information.

This is one of the subjects which would normally be labelled "Advanced" but is actually a common requirement.

Complex Reporting

This article covers how to conditionally use different tables within the same report and how to avoid the common mistakes.

Reports of this type are normally the result of mixed rules being applied to the same area within a business. Because the business area is one logical unit to report against, the client wants all the data measured together. This includes the exceptions to the rules, which logically don't fit, but still need including.

I am going to use the free sample MS Access Database Xtreme, which ships with Crystal Reports.

The report requirement we are going to work towards is:

"A single report to show all the customers who have ordered under £500 items, including those that ordered nothing."

How Table Linking Really Works

Table linking can be very deceptive and the type of link used between tables may actually change at runtime based on what you put in the report. This is the cause behind why this type of report can be so awkward.

We will start the report, and see this in action.

1. Create a new report against the Xtreme database.
   
2. Include the Customer and Orders tables and link them with a "Left Outer Join".
    
3. Add the Customer ID and the Order ID to the Detail Section and refresh the report.
   
   Note how all the customers are listed whether they have Orders or not. This is as expected and how a Left Outer Join is meant to work.
    
4. In the Record Selection, limited the Order Amount to less than £500 :
   
   {Orders.Order Amount} < 500;
   
   All the customers that placed orders of £500 are displayed, but those that ordered nothing (and obviously 'nothing' is less than five hundred!) have vanished.
   
   The problem here is that the report has to access the Orders table to know whether or not it is less than £500. This means the link between the two tables is treated as an Inner Join despite it being defined as a Left Outer.
   
   This is something which makes perfect sense really, but is very easy to forget and can go undetected in larger or more complex reports and cause incorrect report results.
   
   Using the Correct Order of Evaluation
   
   The way to handle this situation correctly is to check whether or not the Order table has a link for a particular record before evaluating the Order Quantity.
   
   The ISNULL function can be used to test the link, but it must be used before any criteria within the Order table is applied.
   
   So, for our current report requirement:
    
5. Open the Record Selection Formula Editor and replace the existing code with:
   
   (
   IsNull({Orders.Customer ID})
   OR
   {Orders.Order Amount} < 500
   );
    
6. Save the changes and refresh the report.

The report will now display the records as expected because if the Customer ID on the Order is null, then the report will not even look at Quantity Amount.

This concept can be expanded for more complex formula just by wrapping up second half of the "OR" in brackets.

(
IsNull({Orders.Customer ID})
OR
(
{Orders.Order Amount} < 500
AND
year({Orders.Order Date}) = 2010
)
);

This will return any customer with no Orders, or Orders under £500 placed during 2010.

Summary

This simple technique can save hours of development time as the other option is to create two reports and merge them together.

There are just two basic things to remember:

1. Crystal Reports formulas will not read the remainder of a control structure once a condition returns 'true'.
    
2. IsNull can be used to test links to child tables as well as the contents of fields.