DistinctCount in selection

[FionaCondon]

I want to report on everyone who has had more than one address. I created a formula, CountAddresses as

DistinctCount ({tblPUPIL_ADDRESS.tblADDRESS},{tblPERSON.PER_ID})

which seems to work. I have got the report grouped by PER_ID. When I put

{@CountAddresses}>1

in the group selection I get the message 'This formula cannot be used because it must be evaluated later'. I can sort of get round it by conditionally suppressing the details and group head when {@CountAddresses}<2 but I want to include a crosstab and that includes the suppressed records.

Can anyone help?

Fiona

 

[synapsevampire]

Not sure why you're using a formula, or why it's giving that error in this case, it might be Crystal version specific, which should always be included with posts if you want specific help, but try:

Group by per_id

Report->Edit Selection Formula->Group

DistinctCount ({tblPUPIL_ADDRESS.tblADDRESS},{tblPERSON.PER_ID}) > 1

I can't recall using a distinctcount, maybe that ticks Crystal off for some reason, but your formula isn't required and just adds in a layer of complexity to Crystal's already iffy ability to process record selections.

-k

 

[lbass]

SV's solution should work fine. I think the issue is that you have created an extra layer by using a formula within a formula--I have found that sometimes where a formula doesn't work, rewriting it using the constituent parts of the formula will.

-LB

 

[FionaCondon]

Thanks- you're right.