Sales Report not showing $0 sales

[awise]

Crystal v8.5 w/SQL Server 2000 / Windows 2000

Designing a report to show sales by brand by joining the
customer master table to the sales history table by custid.

The problem is that the join limits the results to only records where there is an actual sale, which I understand.

If the customer did not have any sales for brand A, then a line item record for brand A will not appear on the report.
The report can't show a $0 sale for a record that never existed.

I'm hopeful that this is a common obstacle where a common
resolutoin is available.

How do I get results for all brands, even if there are no sales records in the sales history table for that customer and brand? How can I get the report to show sales for all brands for a customer, include brands without sales records showing $0 sales in the results?

Appreciate any tips or assistance.

zaw

 

[MisterMo]

could give the structure of the tables, normally a left join should do but I am not sure that in your case this will work.


-Mo

 

[awise]

The customer master table (rm00101) holds all customer setup/address data with the primary key being the custid field.
(rm00101.custnmbr)

the sales history table(sop30200) holds all the customer sales records with the primary key being sales order number. The custID is a field in the sales history table.
(sop30200.custnmbr)

rm00101.custnmbr
smith 12345

smith purchased brand a($10) & b($20) of a possible selection of brands a, b, c and d.

looking for results;

smith 12345
A $10
B $20
C $0
D $0

however, in the history table there are no records for CustID 12345 with the brand = C or D.

I created a view based upon the customer master table inner joined with the sales history table

rm00101 inner join sop30200 where rm00101.custnmbr=sop30200.custnmbr

Appreciate your input.

zaw

 

[dgillz]

You are missing a table, the item master table or its equivalent, where all items are defined. If you make this the leftmost of your tables, and link to sales history by item ID and retain your current linkage of sales history to customers, then all items will appear on your report.

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

http://www.gainfocus.biz

 

[awise]

you're right. My bad. I left out of the design the item master table. I was calling the item from the sales history, not the item master.

I will redesign the report as you discribed and hopefully this will allow for the desired results.

I will follow up a status post once ready.

Thanks,

zaw

 

[awise]

Not having much success.
I have the inv master table linked to the sales history table by itemID.

if I link them with an outer join, only the items in the sales history show, but, if I change the link to a greater Than inv mastr > sales hist then all the item show. However,
the sales records from the history table show up under each itemID, even those without any transactions.

What I'm I doing wrong?

 

[leftfldr5]

I am having the exact same problem with the exact same scenario. Did you find a solution to your problem?

 

[MisterMo]

are you passing parameters to the Sales History table?
If so than your left join will not work.
Here is a sample that will not work despite having a left join

SELECT CL.mapping_clientid, CL.clientid, CO.mapping_codes_id, CO.codeid, CO.CreatedDate
FROM dbo.mapping_client CL 
LEFT OUTER JOIN dbo.mapping_codes CO ON CL.mapping_clientid = CO.mapping_clientid
Where CO.CreatedDate between 2006-01-01 and 2006-01-03

-Mo

 

[leftfldr5]

I just have the Item Master table (IV00101) as my leftmost table. I then have my sales line table (SOP30300) linked by item number with a left outer join. My sales header table is linked to sales line table.
The report is only showing item numbers that exist in both the IV00101 and SOP30300 tables, which means I do not get the records that do not exist in SOP30300.

 

[MisterMo]

OK could you post the sql statement that may be other ways around it but my guess is something like

SELECT T1.Item1, T2.SalesNum, T3.SalesHeader
FROM IV00101 T1 
LEFT JOIN SOP30300 T2 ON T1.ItemNum = T2.ItemNum 
LEFT JOIN SalesHeader T3 ON T2.SalesHeaderId = T2.SalesHeaderId

-Mo