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.