Solved

Grouping and detail records using crystal reports

Posted on 2013-11-21
5
481 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

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. …
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 …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

809 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