?
Solved

Distinct count of a formala field

Posted on 2011-03-02
2
Medium Priority
?
969 Views
Last Modified: 2012-06-21
I have a report that lists visits by patients to clinics,

In the report is a formula field that is looking for patient visits at a specific clinic.  The code is :

if {sp_GetLucentisResults;1.kli_kliniknr} = {?ClinicNumber} then {@EyeID}
else {@null}

When the kli_kliniknr is the clinic we are interested in I display the patients EyeID, otherwise i return null - which is an empty formula.

This works ok.  I get the correct number of visits for the clinic displayed in the report.

However when i do a DISTINCT COUNT of the formula I always get one more visit than there actually is.  I guess this is because "null" is also included in the distinct count.

I could take one from the total, but i may one day have the case that all the visits in the report are at the clinic, so there will not be a null value.  Therefore I will be showing one less visit than there is.

Does anyone have a solution?  How to do a distinct count of a field when sometimes the field maybe empty (null), and should
0
Comment
Question by:soozh
[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 4

Expert Comment

by:MarioAlcaide
ID: 35015978
Hi,

You could do something like this

SELECT DISTINCT COUNT(*) FROM YOUR_TABLE
WHERE YOUR_COLUMN IS NOT NULL;
0
 
LVL 77

Accepted Solution

by:
peter57r earned 2000 total points
ID: 35016111
i think you will have to set a variable if you find a null and then deduct it from the total.

If you arer totalling for the report then in the report header ..

numbervar FoundNull:=0;
""

in your formula field...

numbervar FoundNull;

if {sp_GetLucentisResults;1.kli_kliniknr} = {?ClinicNumber} then
 {@EyeID}
else
(foundnull:=1;
{@null})

then for your total...

numbervar FoundNull;
DistinctCount ({@eyeid})-foundnull


If you are doing group totals then you would need to put the first formula in the group header and total formula would need amending to set the group field in the Distinctcount function.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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…
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 …
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

770 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