Distinct Count - One to Many 1

[shelby55]

Hi

I am using Crystal 9 reporting from an Access 2002 database.

I have a table that has patient chart number, account type, type of test and number of tests in a row. There could be more than one row per account (which is a unique number).

I've created a report where I have to time frames and a variance column. So for tests1 the formula is

If {@OrderDate} in Minimum({?Order Date}[1]) to Maximum({?Order Date}[1])
then {Tests_Volumes_new.TtlTests} else 0

The above value is then summed in the group.

I also want to show number of cases but only distinct count. I thought to create the formula:
If {@OrderDate} in Minimum({?Order Date}[1]) to Maximum({?Order Date}[1])
then {Tests_Volumes_new.FIN}&{Tests_Volumes_new.MRN}

and then do a distintcount per group. It originally looked like it worked but investigation revealed the results were incorrect.

How can I do this? Thanks.

 

[lbass]

Do you want the distinct count of ALL cases within the range? Or only those cases that have results for at least one of FIN and MRN? Or only those cases that have results for BOTH FIN and MRN?

-LB

 

[shelby55]

Hi LB

I would like a distinct count of ALL cases in the range. Please note that all will have a FIN.

The data looks like this:

MRN FIN OrderDate Type # of Tests
123 456 jan 1 2010 Lab 3
555 677 jan 2 2010 DI 2
123 456 jan 3 2010 Lab 1
123 888 dec 3 2009 DI 4
555 999 dec 5 2009 DI 5

So in the case of MRN 123 there were 2 visits in the above but the FIN 456 should only be counted once for a total of 4 tests.

I hope that helps? Thanks.

 

[lbass]

First create a formula {@Null} by opening and saving a new formula without entering anything. Then create a second formula:

If {@OrderDate} in Minimum({?Order Date}[1]) to Maximum({?Order Date}[1]) then
{Tests_Volumes_new.FIN} else
tonumber({@Null})

If FIN is a string, remove the tonumber().

Then insert a distinct count on this formula at the group (chart number?) or report level.

-LB

 

[LisaRR]

I would accomplish this with groups and running totals as follows:

1. Group data by MRN
2. Create a running total, distinct count the FIN, reset on change of MRN group - this will give you the # of visits (FIN) for each MRN (put this in the group footer or it won't calculate correctly)
3. Sum the # of tests for each MRN group

LR

 

[shelby55]

Hi

Thanks LB - your suggestion worked (though I never doubted you!!!).

LisaRR - thanks for the suggestion but I didn't want the group to be by MRN, I wanted other data elements like discharge month etc. which is why your solution wouldn't work for my needs.