Solved

Grouping and detail records using crystal reports

Posted on 2013-11-21
5
483 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 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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Suggested Solutions

Title # Comments Views Activity
Mask on Parameter CR2008 28 83
business objects to connect to MSSQL 6 113
Crystal Reports 2013 25 78
Trendline on Charts in Crystal Reports 2013 4 30
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. …
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…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

752 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