Simple crystal reports question

Hello,

I have a simple report that involves two tables.  It is the clasic products/orders senario.

I want to produce a chart that shows all the products and how many orders they have.

I have included both tables in my report but can not get a join to work where the products with zero orders come up.

Both tables have the columnn prodId (product id).  The product table has product name which i want to have as the x-axis.

Any suggestions


soozhAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
James0628Connect With a Mentor Commented:
Peter is correct, but if you're using fields from the Orders table in the report, CR will probably change any OUTER JOIN to an INNER JOIN, and you won't get the products with no records in Orders.  If you're only doing a count and not using any of the fields in Orders for anything else, make sure that you do the count on prodID, or some other field that's used to link the tables.  That would probably have the best chance of working.

 If that doesn't work, you could write your own query with an OUTER JOIN (eg. in a CR Command), or have the main report read the Products table and use a subreport to read the Orders table.  The manual query option would be much more efficient, but the subreport option might be fine, depending on how much data you're dealing with.

 James
0
 
peter57rConnect With a Mentor Commented:
You need a Left Outer Join from Products to Orders or a Right Outer Join from Orders to products, depending on which way round the tables appear in your links tab.
Check the sql created to see if it's correct. (Database>Show SQL Query)
0
 
soozhAuthor Commented:
i think i see what my problem is...

I have an extra condition on the orders table... i am only interested in orders in the last month.  The query only returns the rows that have a order in the last month.

As the product rows that do not have an order are not returned I dont get a complete picture of all the products.


0
 
mlmccConnect With a Mentor Commented:
To get a chart to work you probably need to use a command as the data source.

Something like this should work

SELECT Product.ProdId, Product.ProductName
FROM Product LEFT OUTER JOIN Orders
ON Product.ProdId = Orders.ProdId AND Orders.Date >= {?StartDate} AND Orders.Date <= {?EndDate}

mlmcc
0
 
James0628Commented:
mlmcc has a good point about the chart.  That probably would eliminate the subreport option (or at least make it much more complicated).  I wasn't thinking about creating a chart.

 James
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.