Solved

Distinct count of a formala field

Posted on 2011-03-02
2
963 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
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 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
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 …
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …

929 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now