Running Count and Group subtotals

[sayden]

Hi

I need help with creating a running count that counts patients only on the first day that they visit a clinic during a month but displays a subtotal for each of the days of the month

I've grouped by Day, which is a field with values numbered 1 to 31. Don't even suggest removing this!

This is the formula for my running count:

Summary:
patientID - distinct count

Evaluate:-
Day(visitdate) = day

Reset:- Never

Here is the problem: The running count displays the following when inserted into the group footer for Day.

Day RC Data
1 0 (no patients)
2 4 (4 patients came)
3 4 (but there were no patients)
4. 4 (but there were no patients

When I drill down I can see that the subtotal is 0 for 3 and 4. What do I need to do to display that subtotal instead of the running count total?

 

[Madawc]

The running total does what it was designed to do: accumulates values from the print lines. If you set it to reset each day, that would work for the data you showed.

The problem is, if someone had come on day 3, you'd count them if they'd not been that month, but otherwise not count them. I can't see an easy way to do this in Crystal - maybe someone else can?

A crosstab, day-by-patient, would probably count correctly but might show too much else.

Madawc Williams (East Anglia, UK)

 

[sayden]

Can you explain further? The problem is sounding a lot likw what I want to do.

"The problem is, if someone had come on day 3, you'd count them if they'd not been that month, but otherwise not count them."

How do I reset each day without having the person counted again if they come back again in the same month?

Thanks

 

[Madawc]

You can't, not in Crystal. Crystal is built around 'rows' or detail lines, which are grouped. If you grouped by patient within month, you could easily find how many per month, but that would not show the total per day. If you group by day within month, you can't easily tell if the same patient also came another day.

The best solution I can see is a subreport which checks the patient for the entire month, and only counts them if they have not visited on an earlier day in the same month. This would need subreport links and shared variables - topics you can look up using the [Search] option. It will also be horribly slow.

Madawc Williams (East Anglia, UK)

 

[lbass]

What version of CR are you using? Is the report just for a particular month, or do you also have a group on months? Are you using more than one table? If so, what are they, and what fields come from each? What is your record selection formula?

You could potentially use a SQL expression to solve this, but we would need more information.

-LB

 

[sayden]

I am using CR for VB.Net(2003), the rport is only for a particular month, using four tables. I need to do this for only one table, and for two fields. The report is an on-demand subreport but if its necessary I can make it a stand alone report. It is grouped by a blank group for the header and Day.

Report Selection formula
-------------------------------------------------
sc_clinic.SC_clinicID} = {?Clinic ID} AND

(({sc_patient.SC_patientID} = {sc_contraceptive.sc_patientID} and
{sc_contraceptive.sc_clinicID} = {sc_clinic.SC_clinicID} and
Year({sc_contraceptive.eventDate}) = {?Year} and
Month ({sc_contraceptive.eventDate}) = {?Month}) OR

({sc_patient.SC_patientID} = {sc_maternalhealth.sc_patientID} AND
{sc_maternalhealth.sc_clinicID} = {sc_clinic.SC_clinicID} and
Year({sc_maternalhealth.eventDate}) = {?Year} and
Month ({sc_maternalhealth.eventDate}) = {?Month}) OR

({sc_patient.SC_patientID} = {sc_papsmear.SC_patientID} and
{sc_papsmear.SC_clinicID} = {sc_clinic.SC_clinicID} and
Year({sc_papsmear.eventDate}) = {?Year} and
Month ({sc_papsmear.eventDate}) = {?Month}) OR

({sc_patient.SC_patientID} = {sc_supplements.sc_patientID} AND
{sc_supplements.sc_clinicID} = {sc_clinic.SC_clinicID} and
Year({sc_supplements.eventDate}) = {?Year} and
Month ({sc_supplements.eventDate}) = {?Month}))
------------------------------------------------------
The information in sc_supplements is the only one I have to do this for.

Running Count evaluation formula
({sc_patient.SC_patientID} = {sc_supplements.sc_patientID} AND
UpperCase ({sc_supplements.breast}) = "TRUE" and
DateDiff ("m",{sc_patient.dateOfBirth} ,{sc_supplements.eventDate} ) in 0 to_ 6) AND
Day ({sc_supplements.eventDate}) = {sc_days.Day}
--------------
Thanks

 

[lbass]

On second thought, it looks to me like all you need to do is insert a distinctcount on the patientID field for both the day group and the grand total--without using running totals at all.

-LB

 

[sayden]

Hi

Thanks LBass for the advice. I tried your suggestion an inserted a summary, but it counted the patients when they came a second time. I found a solution though:

I kept the running count field ({#RCBS-6} and used the following formulas:

@Zero (in report header)

//initializes the variables
WhilePrintingRecords;
NumberVar X := 0;
NumberVar Y := 0;
----------------------------------
@One (placed in group footer (a) for sc_days.day)
//Sets X to value of {#RCBS-6}at the end of the group
WhilePrintingRecords;
NumberVar X;
X := {#RCBS-6};
----------------------------------------
@Two (in group header for sc_days.day)
//Sets Y to the value of X in the previous group
WhilePrintingRecords;
NumberVar X;
NumberVar Y;
Y := X;
Y;
-------------------------------------------
@Three (in group footer (b) for sc_days.Day)

//Subtracts the new value of X from the value of Y for the group. The result is the number of new patients in the group
WhilePrintingRecords;
NumberVar Y;
NumberVar X;

X-Y;
----------------------------------------

Thanks to everyone for their help.