Solved

Grouping and detail records using crystal reports

Posted on 2013-11-21
5
484 Views
Last Modified: 2013-11-22
I have a report that produces details records grouped by a customer.  I like to see which of those customers bought a product, so I grouped by them by specific product code, the grouping worked great but now i only see the one detail line and not the other details lies associated to the customer.
Did I group wrong?
0
Comment
Question by:Scott Johnston
[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
  • 3
  • 2
5 Comments
 
LVL 101

Accepted Solution

by:
mlmcc earned 500 total points
ID: 39667508
When you group by the product the only records in the group are records for that product.

Are you trying to see which customers bought a particular product and then see everything else they bought?

You could write some fancy SQL  to get the records

Something like

SELECT FIeldList  
FROM Your Table
WHERE   Customer in (SELECT Customer FROM YourTable where Product = "XYZ")

THe other way to do it would be add a subreport to the customer group footer to show all the sales to that customer.  The subreport could be your "old" report (the one you have now without the product group)

mlmcc
0
 

Author Comment

by:Scott Johnston
ID: 39667619
Yes , I am.  
So i would add the SQL statement on the grouping or the detail?

--------------------------------------------------------------
Report layout is like this:

Detail line of orders, include Cust No. Inv No.  Inv date  Item code, quanity
grouped by Invoice number
grouped by custom er number.

My goal is to check the first invoice number for a customer after a specified date for a product code and then see if a similar product has been bought after that invoice no.

I am able to see the customer and invoices, I can see the products but when i try to add a "*" to the Customer, (the "*" would identify that customer as having a similar sale, it never adds the "*".
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 39668803
Unless you do it in the SQL with a subselect or some query that includes the invoice and product in each record it won't work in Crystal

Once you group then all records have to fall in the group.  SO in this case all records will have to be for that invoice  and that product which unless you have 2 detail lines for a product will only be 1 line.  A customer could have 1000 other purchases and all you will see in that group is the purchase for that invoice and that product.

In this case it is probably easiest to use a subreport

mlmcc
0
 

Author Comment

by:Scott Johnston
ID: 39669479
I understand exactly, I wanted to create a variable and then pass the invoice number for the item selected and then search based upon the invoice being greater than the invoice used.
I am working on setting up a formula to try this, I was not sure if I could do something like that?
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 39669530
Crystal works on 1 record at a time.  There is no way to search the rest of the recordset.
To do that requires a subreport that is linked on the invoice and customer

mlmcc
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
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 are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

632 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