[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 848
  • Last Modified:

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


0
soozh
Asked:
soozh
3 Solutions
 
peter57rCommented:
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
 
James0628Commented:
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
 
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
 
mlmccCommented:
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now