all records including empty fields? 1

[martink1]

I trying to create a sales reports that shows dealer sales for 2001 grouped by month. problem is if dealer had no sales for entire year he doesn't show in my report. i have two tables linked by cust.name. left outer joined. left tbl is dealer data, right tbl is sales data. I only return 33 dealers when I want all 41 of them to show. how can I get all dealers to show even if they had no sales. thanks

 

[dgillz]

Grab the dealer info from a Customer file, where EVERY dealer must exist, rather than a SALES HISTORY file, where only those dealers that have had sales over a given point in time will exist.

You might also check to be sure that you are not conditionally formatting or group selecting dealers with zero sales out of the report.
Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[fwatanabe]

After linking the 2 tables, did you click on Link Options, and then under SQL Join Type select Left Outer? If all 41 dealers are in the Dealer Data table, you should be getting the results you want...

 

[Ngolem]

Yes...use Left outter join as suggested above.

If you adding selection criteria to your record select based on NON-JOINED fields then use this syntax for it to work

For example in your sales data table if you have sales year...you would not join on this but would select on it for sure in your record select


Use this in your record select formula (we will assume sales year is a 4 digit numeric in the table)


(isnull({table.salesyear}) or {table.salesyear} = 2000) and

the order here is VERY important...the test for null must come first

Hope this helps....Jim

 

[martink1]

The problem I'm having is that as soon as I select any of the "Sales" data my report only returns dealer that have sales in 2001. I left outer joined a {cust.mstr} table to a {sales.hist}table on custnumber field only. I run the report selecting dealers from the {cust.mstr} and for example 'state' from the {sales.hist} the report returns all dealers. but if I pick a dated sales field from the {sales} table and restrict it to 2001 then the report returns only dealers with sales in 2001. then only date fields in {sales.hist} are all document dates and invoice dates so I think I might need to change these to a string value??

 

[dgillz]

Rather than using raw data for your sales, set up a daterange paramter and use a formula for your sales amount:

If {SalesDate} in {?DateRangeParameter} then {SalesAmt} else 0.

Dump the record selection formula. This way EVERY dealer that has ever had a sale will show up, but only those with sales amounts in the date range will show the dollars. Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[kenhamady]

If you add criteria to the 'outer' table, you nullify the outer join. The workarounds are:

1) Use subreports, one per dealer.
2) Return all records to the report and use if-then formulas to only show/total the current year's records. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com