Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

Crystal reports grouping/summary issue

I am creating a report using data from two tables: Customers and Orders.  The Orders table has multiple records per customer ID.  I only want to pull into the report those records that have the highest OrderID per customer along with realated data, namely address information for the customer as of their last order.  I know that I can group my records by CustomerID, place records in the detail section and supress, then create a summary (Maximum(OrderID,CustomerID), and display just this in the group summary, thus yielding just the data associated with the highest OrderID per client,  but I am trying to create labels using the label wizard for these records (which as far as I know requires the data in the detail section) and so that method will not work for me.  

When I try to create a selection formula that I thought would work such as the following:

whileprintingrecords;
Maximum(OrderID,CustomerID)

I get the following error message:  The formula cannot be evaluated at the specified time, or if I remove the "whileprintingrecords" function, I get "This function cannot be used because it must be evaluated later."

Any ideas how I may be able to do this?  The end result would ideally be:

1. No groups
2. The address associated which each client's last OrderID in the detail section of the report (to create labels)
3. I do not want to use SQL commmands because the end user cannot support.  

Any ideas are greatly appreciated.

Thanks
0
scottyfurg
Asked:
scottyfurg
  • 3
  • 3
1 Solution
 
mlmccCommented:
Have you tried grouping by Customer then sorting by order id descending then using the group TOP N to select just the TOP 1?

mlmcc
0
 
scottyfurgAuthor Commented:
Well, if I group them I can't create labels so I'm not sure that would work.  Am I wrong about that?
0
 
mlmccCommented:
What does grouping have to do with creating the label?

mlmcc
0
Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

 
scottyfurgAuthor Commented:
Can you provide a bit more detail about the "group TOP N to select just the TOP 1?" part of your proposed solution?  I'm not certain how to do that.

Thanks in advance
0
 
mlmccCommented:
First create the group
Click REPORT --> GROUP SORT EXPERT
Choose TOP N form the drop down
Fill the other boxes

mlmcc
0
 
scottyfurgAuthor Commented:
That did not really work.  Thanks for the advice though.  What did work is the following:

1. Create a group on CustomerID
2. Suppress the group header and footer.
3. Create a conditional suppress section formula as follows:
    OrderID <> Maximum(OrderID,CustomerID)
4. That eliminated all but the latest address associated with the latest order.

Thanks for your help.
0
 
Computer101Commented:
PAQed with points refunded (500)

Computer101
EE Admin
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

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