Solved

How to do a disctinct count of null values?

Posted on 2012-04-04
2
342 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 500 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
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…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

785 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