Grouping/SubGrouping

[RonWeyforth]

I have a customer database that I want to group based on the customer number and then group on a customer's product expiration month. The purpose of this is to determine which customers have subscriptions that do not expire in the same month.

I am using a group selection formula:

count({@Expire},{SubsProd.Sp_SubID})>1

For the most part, the formula seems to find and display the customer records that have two or more products expiring in different months, however, there are still some customer records appearing that have two or more products expiring in the same month. What I am not doing correctly to get customer records that have two or more products expiring in different months only. Thanks.

 

[kenhamady]

Please list your exact Group field names, in the order they are grouped.

If we can assume that one product for one customer can only have one expiration date, then I think you can use:

distinctCount ({Expire},{Customer}) > 1



Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[lbass]

I think we need to know the formula for {@expire}. You should be using something like:

Date(Year({table.expdate}),Month({table.expdate}), 01)

This makes all dates within the same month and year equivalent.

Then as Ken suggests, use the following in your group select:

distinctcount({@expire},{customer}) > 1

-LB