?
Solved

Problem with Running Total in Crystal Reports 2008

Posted on 2014-07-16
11
Medium Priority
?
481 Views
Last Modified: 2014-12-02
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!
0
Comment
Question by:joylene6
  • 4
  • 3
  • 3
10 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 40201108
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
 
LVL 35

Expert Comment

by:James0628
ID: 40201618
mlmcc,

 You might want to add the CR topic.

 James
0
 
LVL 1

Author Comment

by:joylene6
ID: 40203034
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
How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

 
LVL 35

Expert Comment

by:James0628
ID: 40203421
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
 
LVL 1

Author Comment

by:joylene6
ID: 40205536
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 40205570
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
 
LVL 35

Expert Comment

by:James0628
ID: 40205595
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
 
LVL 1

Author Comment

by:joylene6
ID: 40215280
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 40215299
How are you calculating those values?

mlmcc
0
 
LVL 35

Accepted Solution

by:
James0628 earned 2000 total points
ID: 40215528
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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question