Help with multiple totals 1

[snowyej]

Hello,

I'm not very Crystal savvy, so I'm not sure this can even be done. I have a report that I'm trying to do for a doctor's office I work for. We want to show a list of doctors...each doctor will have a list of ages underneath of patients they've seen and then to the right will be totals (total patients of that age) for 2003, 2004, 2005 and 2006. It should look something like this:

age 2003 2004 2005 2006
dr jones 0-18 50 95 37 109
19-35 78 98 20 38
36-65...
66-100...
dr smith 0-18 89 32 79 89
19-35...

etc..

Currently I have it working for one year, but can't figure out how to have it count the other years. I also can't figure out how to group the ages like that and just currently have it listing each individual age for each doctor.

Does that all make sense? Any help is GREATLY appreciated.

Thanks in advance!

Elizabeth

 

[BobSuruncle]

You'll need a formula for the age grouping.

@age

Select {YourTable.AgeField}
Case 0 to 18  : "0 to 18"
case 19 to 35 : "19 to 35"
case 36 to 65 : "36 to 65"
case 66 to 100 : "66 to 100"
default : "> 100"

Insert a crosstab in the doctor group.
Use the formula as the Row and choose the date field as the Column. Using the Group Option button for the column, select the 'for each year' option for the date field.

An alternate method would be to get rid of the doctor group and just insert the doctor name field as the first field in the Row section of the crosstab.

If a doctor had no patients in a particular age group, then that age group will not show up in the crosstab for that doctor.


Bob Suruncle

 

[snowyej]

Excellent! THat worked great! I've never used crosstabs but that's exactly what I needed. Thanks so much for your help!

 

[madelca100]

Beware that this only counts "age" if there is a value.

If you have missing values (i.e., Null) they won't be counted.

mike

 

[snowyej]

That's ok. Every patient must have a value for age so that shouldn't be a factor. I like that it doesn't show an age group if the dr didn't see any patients of those ages.

Now I've come across another problem...well something new the user requesting this report has asked me for at least..

They've decided they want to only show distinct patients. A doctor could have seen the same patient multiple times during a year. We only want to count that as ONE patient. I can't figure out how to do this. DistinctCount doesn't work... Here's my SQL query if this helps:

SELECT
PatientProfile."Birthdate",
PatientVisit."Visit",
DoctorFacility."First", DoctorFacility."Last", DoctorFacility."Suffix"
FROM
{ oj ("shfm"."dbo"."PatientProfile" PatientProfile INNER JOIN "shfm"."dbo"."DoctorFacility" DoctorFacility ON
PatientProfile."DoctorId" = DoctorFacility."DoctorFacilityId")
INNER JOIN "shfm"."dbo"."PatientVisit" PatientVisit ON
PatientProfile."PatientProfileId" = PatientVisit."PatientProfileId"}
WHERE
PatientVisit."Visit" >= {ts '2003-01-01 00:00:00.00'} AND
PatientVisit."Visit" < {ts '2007-01-01 00:00:00.00'}
ORDER BY
DoctorFacility."Last" ASC

Any suggestions?

Thanks again for the help. Let me know if I should be creating a whole new post for this.

Elizabeth

 

[BobSuruncle]

What field are you summarizing on?
Looking at your SQL, it would seem that you're likely summarizing on "Visit". This would likely be a unique identifier for the visit, not the patient.
Is there a field in the database that uniquely identifies the patient?
If so, add that to your query, and use it to summarize.

Bob Suruncle

 

[snowyej]

Aha! Got it. Thanks everyone. I turned the report in and the user was very pleased.