Here is a summary version of this article:  No.

But twice in the past 2 months I have read people giving this advice to users online.  Before I give the detailed explanation let me provide some background for those who are less familiar with SQL:

When you link two tables in Crystal, the default join between the tables is called an INNER JOIN.  This means that you will only get records that are included in BOTH tables.  So if you link the Customer table to the Order table you will only get customers who have orders (and orders that have customers).  A customer with no orders would not show in a report using an INNER JOIN.

When you want to include all customers, even those without orders, you have to use an OUTER JOIN.  You could use a  LEFT OUTER JOIN from the Customer table to the Order table.  This makes the Order table optional and now all customers show up at least once – even those without orders.  The customer with no orders will have only one record and it will have NULL values for all the Order columns, like the order date.

The confusion starts when you try to filter the Order table, like adding a date range.  As soon as you add criteria to fields from the ‘optional’ table (in this case the Order table) your join will behave like an INNER JOIN.  This is because a record with a Null date can’t be in any date range.  So, if you ask for dates in October 2010 you are going to lose two groups of records from this report:

1) All the records with the NULL dates

2) All the records that have dates, but that aren’t in October 2010

So this criteria will cause the report to drop customers who had no orders at all – even with the OUTER JOIN.  It will also drop some customers who do have orders – if those orders are all in other months.  For example a customer with one order in September 2010 is not going to show up in the report.

So how do you keep all customers in the report and still only show activity for the target time period?  There are a couple of advanced solutions that work (see below) but there is also the simple and risky suggestion that I hear periodically.  It involves using the IsNull () function.  You are told to change your criteria from this:

{Services.DATE} in Date (2010,10,1) to Date (2010,10,31)

to this:

IsNull ({Services.DATE}) or {Services.DATE} in Date (2010,10,1) to Date (2010,10,31)

The thinking behind this is that a customer that doesn’t have October dates will therefore be a Null value and so he will be picked up by the first rule.  The problem is that this is only true some of the time.  If you go back to what I described above, there are two groups of customers that drop off when you add a date range.  The IsNull() only solves the problem for group one, customers with no orders at all.  It does not solve the problem for group two, customers who have orders in other months but none in the target month.   A customer who has one order in September will not have any records with a Null date, so their record doesn’t meet either criteria.  You lose that record and therefore you lose that customer.

So what other methods can you use?  I have used three methods in different scenarios.  The first is the best but involves some SQL knowledge.  The other two are only workable in smaller results because they can really slow things down:

1) Use a SQL Command and put the date criteria into the FROM clause rather than the WHERE clause

2) Create the complete customer list in the main report, and create a linked subreport to find the orders for each customer

3) Leave all the orders in the report and suppress the details on orders that you don’t want to see, rather than using a filter.

If you would like some help applying one of these methods to a report in your environment, give me call.