forcing null results to show

[metaldude]

I'm using Crystal Reports 2008 with an ODBC connection to a SQL database. Most everything I need is in a table called RECEIPT_LINES. I have a few tables inner joined to it so I can give some user friendly info (for example, the department's name vs code). I'm trying to show every Store even if it has no sales in a certain Department but am only seeing a store if it had sales.
STORE DEPT $SOLD
1 1 $100
3 1 $25
...
but I want:
STORE DEPT $SOLD
1 1 $100
2 1 $0
3 1 $25
...

Here are some things I've done after reading on tek-tips about this issue:
1) I have checked "Convert Database NULL Values to Default" (as well as the next one "Convert Other NULL Values to Default") in the Report Options. (Result: still shows a store only if there were sales)
2) I've also tried making a left outer join to the STORES table which only has 7 records- one for each store. I am then reporting on STORES.STORE_NO (vs RECEIPT_LINE.STORE_NO)to try to force the store to show regardless of sales in the RECEIPT_LINE table. (Result: the report appears to hang or at least didn't to anything but say "Accessing Database" and show the hourglass for over 15 hours).

I've been able to do this on another report by doing 1 and 2 above. It took a while I think because the left outer joined table was huge but it did finish in less than 15 hours. I can't tell if I should wait it out or if there is something else I should do instead.

Thanks to anyone for taking the time to read this and for any advice.

 

[lbass]

You need a left outer join FROM the store table TO the receipt_line table (and to any other tables), with no selection criteria on the receipt_line table (or other tables) for this to work.

However, it sounds like you have a huge database if the report is running for 15 hours. There are two alternatives. One would be to use the store table as a main report, and then insert a subreport that is linked to the store field and placed in a store group in the main report. In the subreport you can add selection criteria, and you will still see all stores in the main report, since the sub behaves as if it were left joined.

Another alternative is to use a command as your datasource where you add criteria in the 'From' clause instead of the 'Where' clause. This will prefer the left join, as in:

SELECT Store.store_no, receipt_line.store_no, receipt_line.amt, receipt_line.date
FROM Store left outer join receipt_line on
Store.store_no = receipt_line.store_no and
receipt_line.date >= {?StartDate} and
store.store_location = 'Boston'

You would use this as your sole datasource for the report, so you would build in all tables.

-LB

 

[lbass]

PS. Converting nulls to default will not solve this issue.

-LB