Solved

Grouping and detail records using crystal reports

Posted on 2013-11-21
5
477 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
  • 3
  • 2
5 Comments
 
LVL 100

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 100

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 100

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

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…
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…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

760 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

19 Experts available now in Live!

Get 1:1 Help Now