Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 487
  • Last Modified:

Grouping and detail records using crystal reports

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
Scott Johnston
Asked:
Scott Johnston
  • 3
  • 2
1 Solution
 
mlmccCommented:
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
 
Scott JohnstonSystems ConsultantAuthor Commented:
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
 
mlmccCommented:
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
 
Scott JohnstonSystems ConsultantAuthor Commented:
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
 
mlmccCommented:
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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now