Solved

How to do a disctinct count of null values?

Posted on 2012-04-04
2
345 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

696 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