Problem with Running Total in Crystal Reports 2008

Hello All,
I am working on a Crystal Report using Crystal Reports 2008.  I have encountered a problem that I just haven’t been able to resolve on my own, and request your assistance.  I hope this description doesn’t receive too many “tl;dr’s”, just don’t want to leave out any critical details.

Background:

I am querying an inpatient pharmacy database for a requested report that would tally the total number of doses and the total days of therapy for antibiotic orders; these metrics are defined as:

Total Number of Doses:  A count of all antibiotics doses documented as “Administered”.
 
Total Days of Therapy: A count of total days of therapy, where a day of therapy is comprised of at least one dose being documented as “Adminstered”.

Example:  Using the table below, where A = Adminstered, NA = Not Adminstered, and Blank = IsNull, then the Total Number of Doses would be 18 doses, and the Total Days of Therapy would be 6 (six days where at least one dose was received six out of seven days).

                    Sun      Mon      Tues     Wed      Thur      Fri      Sat
1st Dose      |  A       |  A        |  NA      | A         | A      | NA      | A
2nd Dose     | NA      |  A       |   A       |             | A      | NA      | NA
3rd Dose      |  A       |  A       |   A       | NA        | A      |           | A
4th Dose      |  A       |  A       |   A       | NA        | A      | NA      | A

Current Report Details:

The report consists of two tables (linked with an inner join, unenforced) with five pertinent fields from these tables:
PHARMACY.OCCURRENCE_DATE (Date each dose is scheduled for) ; PHARMACY.OCCURRENCE_TIME (Time each dose is scheduled for); DOCUMENTED_DATE (Date each dose was documented); PHARMACY.DOCUMENTED_TIME (Time each dose was documented); PHARMACY.ADMIN_STATUS (Status of a given dose; can be ADMINISTERED, NOT ADMINISTERED, or Blank /IsNull, meaning it was not documented at all);

The report is grouped by:
-  PHARMACY.ORDERS_PATIENT_NUMB
    -  PHARMACY.ORDER_NUM
        -  PHARMACY.OCCURENCE_DATE
            -      PHARMACY.OCCURENCE_TIME

Actions:
 -  First I created a formula field (@Binary_Rep_of_Admin) that assigned a “binary” value each of the three possible values in   that that field as follows:
If {PHARMACY.ADMIN_STATUS} = "ADMIN" then "1" else
If {PHARMACY.ADMIN_STATUS} = "NOTADMIN" then "0" else
If {PHARMACY.ADMIN_STATUS} = "IsNull" then "0"

 -  I then created a second formula field (@Conv_DistCnt_of_Admin) that converted the field from a string to a number, as follows:
ToNumber({@Binary_Rep_of_Admin})

-  Next I created a Summary on formula field @Conv_DistCnt_of_Admin that provides the total number of doses.  This is WORKING, very consistently.

-  Finally, I created a Running Total on @Conv_DistCnt_of_Admin, with the Running Total config details as follows:

Field to summarize: @Conv_DistCnt_of_Admin
Evaluate on Change of Group: PHARMACY.OCCURENCE_DATE
Reset on Change of Group: PHARMACY.ORDER_NUM

Problem:

The “Total Days of Therapy” Running Total field is working as desired but only about two-thirds of the time.  Every once in a while, it incorrectly reports the number by one less than it should be, and I believe that in these cases, Crystal is skipping the first record.  I am aware of the function “OnFirstRecord”, but do not know the syntax to insert in this report, nor do I know where I should insert it.

Any and all assistance would be GREATLY appreciated in resolving this!
LVL 1
joylene6Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
Is the field really NULL or do you have an blank string or the string IsNull?

If it is NULL you have to make the NULL test first.

mlmcc
0
James0628Commented:
mlmcc,

 You might want to add the CR topic.

 James
0
joylene6Author Commented:
Hello, mlmcc.  Thanks for your reply.

I've double checked the report, specifically formula field @Binary_Rep_of_Admin and tried to run the report with both "IsNull" and blank (I used "" for blank).  Neither configuration made a difference in those instances where the the Running Total was skipping a record.

Am I missing something in your question, or did that answer it?

Thanks!

P.S. - Thank you to James06289 for helping clarify my initial inquiry's topic.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

James0628Commented:
Apparently PHARMACY.ADMIN_STATUS can be "ADMIN", "NOTADMIN", or something else.

 What, exactly, is the "something else"?  Is it blank, or the string "IsNull", or is it actually null?


 You have a group on PHARMACY.OCCURENCE_DATE, which implies that there can be more than one record with the same date.  Will ADMIN_STATUS be the same in all of the records for a given date?

 If not, then the result of the running total will depend on which record happens to be first in each OCCURENCE_DATE group.  That's the record that will be "evaluated" when the group changes.

 James
0
joylene6Author Commented:
Hello James,

I ran a null test on the PHARMACY.ADMIN_STATUS field, and found that it is blank instead of IsNull.

To answer your question re: ADMIN_STATUS, there can be anywhere from 1 to 6 records for a given date, with any possible mixture of ADMIN, NOT ADMIN, or Blank.

Thanks for you help thus far!
0
mlmccCommented:
For counting purposes you could just use

If {PHARMACY.ADMIN_STATUS} = "ADMIN" then
    "1"
else
    "0"

or even this and just sum the values
If {PHARMACY.ADMIN_STATUS} = "ADMIN" then
    1
else
    0
0
James0628Commented:
According to your first post, you set up your running total to only evaluate once per OCCURENCE_DATE group.  That means that you're only including one value (0 or 1) for each group.  Assuming that OCCURENCE_DATE is just a date, or you're grouping by day (not date and time), then you've got 1 group for each day, so you're only including one value for each day.  If that's really what you want, but you have different values for the same date, how do you decide which value to use?

 If you actually want to include every record (not just 1 per day), then it seems like a simple summary, as mlmcc suggested, would work.  Or, using your running  total, change it to evaluate for each record.

 James
0
joylene6Author Commented:
All,

Apologies for the delay in getting back to you on this.  I was trying to wordsmith a succinct explanantion of the issue that I am having, and decided that nothing is more succinct than a screenshot.

Please see the attached screen shot, detailing my issue.  The running total is located in the summary field in the lower right corner, labeled "Total Days of Therapy".  It should value "1" for each day where a dose was administered.  As you can see, there were six days where at least one dose was adminstered, and thus the "Total Days of Therapy" should register as "6.00" instead of "5.00".  The "Total Days of Therapy" Running Total is working in about 2/3 of the case that I have validated thus far, and in the cases where it is not accurate, it is always 1 less than it should be.  This is the reason why I believe it's skipping the first record, and I cannot identify why.

The summary "Total Doses" is working correctly in all cases that I have validated thus far.

Please advise, what additional information do I need to provide to troubleshoot this?
ABX-Stewardship-Report-ScreenCap.PNG
0
mlmccCommented:
How are you calculating those values?

mlmcc
0
James0628Commented:
Like I said, if you're grouping on the date, without the time, then you've got one group per day.  And if your running total is set to be evaluated on "change of group", it will only look at the first record in each group.  In your screenshot, 06/09 has a 0 in the first record for that day, so the running total will get a 0 for that day.

 If you could sort the records in the group so that a "1" record was first, that would probably fix the running total.  You probably don't want to do that, but, as a test, you could change the report to sort, descending, by that 1/0 column, and see if that fixes the running total.

 As I understand it, you want to add 1 for every day (group) that includes a "1" record.  Since you don't just want a total of the "1" records, and you don't know where the "1" records will be in the group, you may have to use formulas and variables for that.

 From your first post, it seems that you're trying to create a total for the ORDER_NUM group.

 Create a formula like the following (call it whatever you like) and put that formula in the report header, and in the ORDER_NUM group header:

Global NumberVar total_doses;
total_doses := 0;
""

 That declares the variable, and putting it in the ORDER_NUM group header resets the total for each new ORDER_NUM.  The "" at the end just means that the formula won't produce any visible output on the report.


 Create a formula like the following and put it in the OCCURENCE_DATE group header or footer:

Global NumberVar total_doses;
if Sum ({@Conv_DistCnt_of_Admin}, {PHARMACY.OCCURENCE_DATE}) > 0 then
  total_doses := total_doses + 1;
""

 That adds 1 to total_doses if the OCCURENCE_DATE group includes any "1" records.


 Create a formula like the following and put it in the ORDER_NUM group footer to output the count:

Global NumberVar total_doses



 You said that the report is also grouped by ORDERS_PATIENT_NUMB.  If you also want totals for that group, or grand totals for the entire report, you can use other variables and similar formulas.

 James
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.