Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Distinct count of a formala field

Posted on 2011-03-02
2
Medium Priority
?
976 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 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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

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…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

580 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