?
Solved

How to do a disctinct count of null values?

Posted on 2012-04-04
2
Medium Priority
?
350 Views
Last Modified: 2012-04-04
How to do a disctinct count of null values?

I have a report that I need the distince count of employee numbers, but I need to exclude the null values (as they are not to be included int a given category).



Thank you very much.
0
Comment
Question by:kgittinger
2 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 2000 total points
ID: 37805852
It sounds like you want to get this count at group level.
So create a formula field for the group header...

WhilePrintingrecords;
Numbervar nullKnt:=0;
""

In the detail line add formula field..

WhilePrintingrecords;
Numbervar nullKnt;
if isnull(table.employee}) then nullKnt:=1;
""

In the group  footer;

WhilePrintingrecords;
Numbervar nullKnt;
distinctCount({Table.Employee}, {table.groupfield}) - nullKnt
0
 
LVL 7

Expert Comment

by:waltersnowslinarnold
ID: 37805883
This sample query could well assist you;

SELECT SUM(CASE WHEN col IS NULL THEN 1 ELSE 0 END) NullCount
FROM tableName
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question