Solved

Distinct count of a formala field

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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 explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

708 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

15 Experts available now in Live!

Get 1:1 Help Now