One of my recent projects was a report to monitor providers' compliance with State of Maine vaccination standards for children under 2 years old. The data is stored in a observations table, and looks something like:
| PatientId | Observation | Date | Value |
| 1 | DTAP #1 | 01/04/2009 | Done |
| 1 | DTAP #2 | 04/05/2009 | 0.5ml |
| 2 | HIB #1 | 02/28/2009 | Refused |
| 3 | HIB #1 | 03/04/2009 | Done |
For each of these vaccinations, I need to know when it was given, and whether it was refused or not. To do this, I created two formulas for each vaccination.
DTaP1Date:
If ({Command.Observation} StartsWith "DTAP"
Or {Command.Observation} StartsWith "DTP"
Or {Command.Observation} StartsWith "DPT")
And Right({Command.Observation}, 1) = "1" Then
{Command.Date}
DTaP1Val:
If ({Command.Observation} StartsWith "DTAP"
Or {Command.Observation} StartsWith "DTP"
Or {Command.Observation} StartsWith "DPT")
And Right({Command.Observation}, 1) = "1" Then
{Command.Value}
I realized that this was going to get far too wordy far too quickly, so I defined a Report Custom Function. After right-clicking on the custom function node in the Formula Workshop and choosing a name, I had the option to "Use Extractor" to pull the function definition from an existing formula. I tweaked the parameter names for readability, and then edited the function to be more generic.
DTaPCheck:
Function (stringVar strObs, stringVar strNumb)
(strObs StartsWith "DTAP"
Or strObs StartsWith "DTP"
Or strObs StartsWith "DPT")
And Right(strObs, 1) = strNumb
That simplified @DTaP1Date to:
If DTaPCheck({Command.Observation}, "1") Then
{Command.Date}
To summarize this, I grouped the report on PatientId, and created a new set of formulas to display the results from the formula above.
DTaP1Show:
Maximum({@DTaP1Date}, {Command.PatientId})
This is a bit more information than I need, though -- I just need to know if the vaccination was given after the child was 42 days old.
If Maximum({@DTaP1Date}, {Command.PatientId}) > DateAdd("d", 42, {Command.DateOfBirth}) Then
"Y"
Else
"N";
Actually, I lied - that's not all I need to know. Remember when I said above that some patients refuse the vaccination? For the purposes of this report, we need to keep track of whether they actually received the shot.
CheckRefusal():
Function (stringVar strObsVal)
Ucase(strObsVal) StartsWith "REF"
I would have liked to be able to pass in the function name and have it do the Maximum grouping in the function, but functions are designed to be independent of the data. Therefore, we have to do the grouping outside the function and pass in the result.
If Maximum({@DTaP1Date}, {Command.PatientId}) > DateAdd("d", 42, {Command.DateOfBirth}) Then
If CheckRefusal(Maximum{@DTaP1Val}, {Command.PatientId})) Then
"R"
Else
"Y"
Else
"N";
However, this raises a new problem. While the records that have a particular observation display just fine, the patients missing that observation have a blank value instead of an "N".
Thanks to some timely help over on the Crystal Reports forum, I made the following change to DTaP1Date:
If DTaPCheck({Command.OBSNAME}, "1") Then
{Command.OBSDATE}
Else
Date(1900, 01, 01);
Since our practice didn't exist in 1900, we don't have to worry about any real observations with that date coming through. To help ensure consistency, I created another Custom Function, slightly abusing the concept of null for the sake of simplicity.
NullDate:
Function ()
Date(1900, 01, 01)
DTaP1Date:
If DTaPCheck({Command.OBSNAME}, "1") Then
{Command.OBSDATE}
Else
NullDate();
So, by creating these formulas and functions, I was able to write my report with a minimum of duplication, and a high degree of flexibility for later changes.
