Distinct Count to Exclude Suppressed Detail

[mtepfer]

I am using Crystal Reports 9, and have a report that is grouping by month. Within each month is client detail. If the client detail appears twice within that month based on 2 fields being alike (tenant and dtpayfor) I am suppressing the detail with the following formula :

({@sTenant} = next({@sTenant}) and {ado.dtPayFor} = next({ado.dtPayFor})) or
({@sTenant} = previous({@sTenant}) and {ado.dtPayFor} = previous({ado.dtPayFor}))

This formula is done within the detail section expert to suppress data.

Now i want to do a distinct count on the items for each month, but do not want to count the suppressed items just the viewable items.

I have tried using a running total formula that is opposite of my formula where it resets on the group but am not getting the desired results.

Here is a sample for a clearer picture

JAN 06 (distinct count should be 1 not 2)
John Doe 1/1/06 240.00 (Suppressed)
John Doe 1/1/06 -240.00 (Suppressed)
John Smith 1/1/06 150.00

FEB 06 (distinct count of 1)
John Smith 2/1/06 150.00

 

[jomarelectric]

if i'm following, don't suppress just don't include them period.

go Report/Selection Formula/Record & enter your code there.

//frank

 

[mtepfer]

Did try that originally but kept getting the following error message:

This function cannot be used because it must be evaluated later.

 

[lbass]

Are you trying to exclude accounts that sum to zero? If so, then first insert a group on tenant and then use a running total that is a distinct count of tenant, evaluate using a formula:

sum({table.ado.dtPayFor},{table.tenant}) <> 0

Reset on change of group (month). Note that the running total must be placed in the month group footer, not the header.

-LB

 

[mtepfer]

Lbass, thanks for the insight but unfortuanatly not trying to suppress those items that are only equal to zero, can also be a positive or negative amount. The only criteria to suppress is if the name and date pay for match and are within the same month grouping.

 

[lbass]

First create a formula {@concat}:

{@sTenant}+totext({ado.dtPayFor},"M/d/yyyy")

Then use an evaluation formula like this:

(
nextisnull({@concat}) or
(
{@month} = next({@month}) and
{@concat} <> next({@concat})
)
) and
(
previousisnull({@concat}) or
(
{@month} = previous({@month}) and
{@concat} <> previous({@concat})
)
)

-LB