How to report zero sales customers 1

[IanMac26]

Using crystal reports Xi. I have SALESTRANSACTIONHEADER.salesaccount left outer joined to SALESACCOUNT.id and with the other selection criteria the following sql is generated.

SELECT "SALESTRANSACTIONHEADER"."TransactionType",
"SALESTRANSACTIONHEADER"."ReversedBy",
"SALESTRANSACTIONHEADER"."TransDateLiymd",
"SALESTRANSACTIONHEADER"."Total", "SALESTRANSACTIONHEADER"."SalesAccount",
"SALESACCOUNT"."Status"
FROM "SALESTRANSACTIONHEADER" "SALESTRANSACTIONHEADER" LEFT OUTER JOIN
"SALESACCOUNT" "SALESACCOUNT" ON
"SALESTRANSACTIONHEADER"."SalesAccount"="SALESACCOUNT"."ID"
WHERE "SALESACCOUNT"."Status"='A' AND
"SALESTRANSACTIONHEADER"."TransactionType"=1 AND
"SALESTRANSACTIONHEADER"."ReversedBy"=0 AND
("SALESTRANSACTIONHEADER"."TransDateLiymd">=20071001 AND
"SALESTRANSACTIONHEADER"."TransDateLiymd"<=20071031)
ORDER BY "SALESTRANSACTIONHEADER"."SalesAccount"

However I am not getting rows for the SALESACCOUNT records that do not have a SALESTRANSACTIONHEADER. Then I was going to suppress the rows that have SALESTRANSACTIONHEADER values.

I have also tried using a subreport but cannot get the shared transaction total back to the main report to suppress the section.

tia.
Ian

 

[dgillz]

Reverse your links, left outer join FROM SALESACCOUNT (which presumably has all Sales Accounts, even if they have never purchased anything) TO SALESTRANSACTIONHEADER, which of course contains only those accounts that have purchased something.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[IanMac26]

I tried reversing the link but still only got the inner join i.e I did not get the sales accounts without transactions.

I also tred using Excel to extract the records - same result.

Perhaps my odbc driver or database (it is not native SQL) does not support left outer join.

I have almost got it working using a subreport.

 

[dgillz]

If you are using ODBC, and both tables are in the same database, then left outer join should be available. What type of database are you working with?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?