Solved

Simple crystal reports question

Posted on 2011-02-26
5
831 Views
Last Modified: 2012-06-27
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
Comment
Question by:soozh
5 Comments
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 166 total points
ID: 34986748
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
 
LVL 34

Accepted Solution

by:
James0628 earned 167 total points
ID: 34986905
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
 

Author Comment

by:soozh
ID: 34987304
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
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 167 total points
ID: 34987619
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
 
LVL 34

Expert Comment

by:James0628
ID: 35005667
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
This video discusses moving either the default database or any database to a new volume.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now