cancel
Showing results for 
Search instead for 
Did you mean: 

Selecting records based on a sub total

Former Member
0 Kudos

I need to create a listing of sales reps and their customers who have spent over $300 in a selected date range. I am grouping by the sales rep id and then the customer id. However, I'm not sure how to only select a customer for each sales rep who has spent a total of $300 or more in the selected date range. If I put in the record selection formulas the formula, AmtSpent > 299.99, then I miss getting customers who have several orders for under $300, but together they add up to over $300.

Any help would be greatly appreciated.

-Rick Hardy

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Insert a sum summary field on the AmtSpent field then use the summary field in the group selection formula

sum ( AmtSpent field, customer id field) >= 300

Former Member
0 Kudos

Thanks to everyone for your help. When I inserted the summary formula in the group selection I had to also use WhilePrintingRecords; in order for it to work properly.

Thanks again.

Former Member
0 Kudos

Hi Ricky

I apologize as I was not able to update you earlier, however good to hear that you have figured out the problem.

Please let us know if you have any further queries.

Regards

Ashwini Yadav

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi Ricky

You will have to write a formula where you can calculate the customer who have spend over $300 and then you can place it on the group.

By doin this you will get the data for all the customer who have spend more over $300.

Hope this helps you.

Please let us know if you have any further queries.

Regards

Ashwini Yadav

Former Member
0 Kudos

Ashwini,

Thank you very much for your quick response. I am still new working with Crystal reports so I apologize for my ignorance. When you say write a formula, is that as easy as sum(AmtSpent) > 299.99? Then when you say 'place it on the group', I'm not sure what that means?

Thanks again for your help

-Rick