Solved

Simple crystal reports question

Posted on 2011-02-26
5
835 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 35

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 35

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

737 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