Distinct count all not null values

[JeroenBoon]

Hello,

I'm trying to distinct count costumers placed in the detail lines and I place the distinct count in the groupfooter. My problem is, that in some lines the costumer is null (which is correct). The distinct count should ignore this null and just count the not null costumers. Is there a way to do that?

thanks, Jeroen.

 

[Reebo99]

I would create the following formulas:

@NullTest
If IsNull({Customer}) then 1 else 0

@DistinctCount
If Sum({@NullTest}) >0 then DistinctCount({Customer})-1 else
DistinctCount({Customer})

Replace {Customer} with your real customer field....

Reebo
UK

Please Note - Due to current economic forcast and budget constraints, the light at the end of the tunnel has been switched off. Thank you for your cooperation.

 

[JeroenBoon]

Reebo99,

Thanks, great tip! And indeed: customer instead of costumer...

Jeroen.

 

[deb13]

Create a running total called RCustomerCount which distinct counts the customer field using a formula - not isnull ({customer]). You can then copy this running total into any group footer you need.

Hope this helps.

Deb

 

[JeroenBoon]

deb13,

That's even easier.
thanks.

 

[kskid]

CR 8.5
Oracle 8i

It works great but how can I get the running totals into the Group headers rather than the group footers for a more aesthetic looking report

For example, I need it to look like this

Group Header 1 100 100 100....
Grp Hdr2 25 30 35...
Grp Hdr2 75 70 65...


Instead of this

Grp Ftr2 25 30 35...
Grp Ftr2 75 70 65...
Group Footer 1 100 100 100...

Any ideas?

Thanks in advance

 

[lbass]

The running totals will only work in the footer, so you will need to use Reebo's solution instead if you need the results in the group header.

-LB