Crystal Reports Optional Table Linking and the Importance of Order
page 3 of 5
by Jason Dove
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 23796/ 101

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.


View Entire Article

User Comments

No comments posted yet.

Product Spotlight
Product Spotlight 

Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2018  |  Page Processed at 2018-05-24 1:50:02 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search