[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 630
  • Last Modified:

Running Total Issue or Possibly Formula Issue in Crystal Reports 9

My issue involves two formula's - @TotalTime and @Converted Time and a Running Total (RTotal0). The setup for the Running Total can be found in my Code Snippet.

@TotalTime
{Data.ApptTime}/60

@Converted Time
numbervar x := {#RTotal0};
totext(truncate(x),0,"")+" Hrs "+
totext(remainder(x,truncate(x))*60,0,"") + " Mins"

A little background:

My report deals with a Doctors schedule. In the doctors schedule, a appointment is booked and is by default placed into Column 1. It always defaults to Column 1. If the provider elects to double book their time and schedule another patient at the exact same time, the second appointment hits column 2. It expands in essence. It is possible to have up to 4 appointments in one time slot. This is why we wait forever at the Doctors office - appointments get overbooked because patients cancel or no show frequently and the provider would rather have the patients wait then risk no appointment at all.

My Client wants the "TOTAL TIME" a provider was "scheduled" to see patients, not the time they actually saw the patients. So if the Provider was scheduled to see patients from 8 am to noon and had patients doublebooked for every time slot, I only want to report the overall time he saw patients regardless of the doublebooking, triplebooking or quadruplebooking. So the total time for my example should be 4 hours not 8 hours.

The good news is this is painfully close to being correct!

What is currently happening, is if the schedule contains more than 1 facility the time calculates wrong. If you look at the Screen Shot image I attached, the second facility reported what the Total time was. I think my issue may be in the running Total but not 100% sure. I need it to calculate time on each facility - {Data.Facility}.
Running Total (RTotal0)
 
Field to Summarize - @TotalTime 
Type of Summary - sum
 
Under Evaluate Section:
 
"Use a Formula":
(
onfirstrecord or
{Data.ResourceStart} <> previous({Data.ResourceStart}) or
{Data.Resource} <> previous({Data.Resource}) 
) and
minimum({Data.ApptKind},{Data.ResourceStart}) = 1 AND 
maximum({Data.Column},{Data.ResourceStart}) >= '1'
 
Reset on change of field {Data.Resource}

Open in new window

Report-Results.pdf
0
Jeff S
Asked:
Jeff S
  • 9
  • 9
1 Solution
 
MIKESoftware Solutions ConsultantCommented:
Looks like you need to RESET the running total to Zero for Each Facility.....? It is running the total for entire report.

How is your running total setup to reset...now..?

MikeV
0
 
Jeff SAuthor Commented:
Reset on change of field {Data.Resource} - but the Resource can also have multiple facilitys they travel to.

Morning at Hospital doing Surgery Cases
Afternoon Clinic treating patients

So I think it needs to factor in both not the one. But how?
0
 
MIKESoftware Solutions ConsultantCommented:
So do you actually need to subtotal by DOCTOR then...?
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Jeff SAuthor Commented:
by Doctor (called Resource) then also by that Facilty.

Doctor Jones

West Side Hospital - 4 hours
Pain Clinic - 4 hours

Total time today for Dr Jones = 8 hours against both Facilities.
0
 
MIKESoftware Solutions ConsultantCommented:
What are your GROUPS please...
0
 
Jeff SAuthor Commented:
Screen Shot attached with Groups ...
Group-Expert.pdf
0
 
MIKESoftware Solutions ConsultantCommented:
Why not create 2 running totals...ONE to handle the by Doctor Subtotals...and ONE to handle the by LOCATION subtotal....?

You can "overlay" the actual subtotal...and use....a suppression condition formula to display whichever one you need at whichever group you need...

MikeV
0
 
Jeff SAuthor Commented:
Could you elaborate more? I am not a Crystal GURU yet, but do know my way around it fairly well. How should I set up the Location Running Total? Like I structured the Resource, but change the reset? Im not 100% on the overlay of the subtotal you suggested.

Your Help by the way is deeply appreciated. I was worried no one was going to help me out.
0
 
MIKESoftware Solutions ConsultantCommented:
Yes same way...(I believe)...just changing the reset. You may need to test by trial and error.

For the overlay of the actual display Running Total Box...you can rightclick each display subtotal box...and format...then go to suppress...and use the DRILLDOWNGROUPLEVEL...to tell Crystal when to suppress the total and when NOT to suppress it....that is IF you need to suppress them...IF you want BOTH to display at all times....then you can do that as well..placing them in different areas in the group...????

MIkeV
0
 
MIKESoftware Solutions ConsultantCommented:
Oh and no problem on the HELP....bad thing is I've got BIG projects working..and i"m just trying to type help quickly so...hopefully I'm getting it right...lol

MikeV
0
 
Jeff SAuthor Commented:
Mike -

No rush at all .... only when you get a moment of free time ... trust me, this has been a project thats lingered for me. I also have multiple projects to kick out and try assisting on here as well, so I know how it goes. I am just thankful for the suggestions you offered so far. I will try and take your suggestions and apply them as well and see what mess I can create.

If you get a moment in the near future ... hit me up.

Thanks again,

Jeff
0
 
Jeff SAuthor Commented:
I had some minor issues with my formulas and Running Total so I modified them alittle:

@TotalTime

If {Data.ApptTime} = 0 then
    0
else
    {Data.ApptTime}

 @Converted Time

If {#RTotal0} = 0 then
    "--"
else
    ToText(Truncate({#RTotal0}/60),0,"") + " Hour(s), " + ToText(Remainder({#RTotal0},60),0,"") + " Min(s)"

 My running total stayed the same.......

Field to Summarize - @TotalTime
Type of Summary - sum
 
Under Evaluate Section:
 
"Use a Formula":
(
onfirstrecord or
{Data.ResourceStart} <> previous({Data.ResourceStart}) or
{Data.Resource} <> previous({Data.Resource})
) and
minimum({Data.ApptKind},{Data.ResourceStart}) = 1 AND
maximum({Data.Column},{Data.ResourceStart}) >= '1'
 
Reset on change of field {Data.Resource}

Some fake sample data:

 Resource = Francis, William MD

Facility 1: River Oaks Main Clinic

Facility 2: Western Medical Hospital

Date of Service = 10/25/2007

*** From 7:15 am to 11:45 am, Patients were treated at Facility 1: River Oaks Main Clinic *** (total time = 4 hours 30 min)

*** From 12:15 pm to 1:00 pm, Patients were treated at Facility 2: Western Medical Hospital *** (total time = 45 min)

*** From 1:00 pm to 3:15 pm, Patients were treated at Facility 1: River Oaks Main Clinic *** (total time = 2 Hours 15 min)

The report should tell me Total time at River Oaks Main Clinic = 6 Hours 45 min and 45 min for Western Medical Hospital. What it currently is doing, is reporting the 6 Hours 45 min on the River Oaks Main Clinic and 7 Hours 30 min on the Western Medical Hospital. The time for this facility should be 45 minutes, yet its taking the full provider time and throwing it into this second facility. I am assuming its because the Running total tells it "Reset on change of field {Data.Resource}". Somehow, I need it to evaluate on both the Resource and the Facility. Any suggestions?
0
 
MIKESoftware Solutions ConsultantCommented:
2 separate RUNNING TOTALS....???
0
 
Jeff SAuthor Commented:
I could ... but how do I have it display right on the report? Same criteria on second Running Total but change reset on change to {Data.Facility}?
0
 
MIKESoftware Solutions ConsultantCommented:
Yes....if you need 2 different subtotals in a RUNNING total...then just create the running totals SEPARATELY...and then,...we're back to my previous post of just doing a CONDITIONAL FORMULA on the DISPLAYING of the 2.

Like I said...you can have them appear in separate areas,...maybe right next to each other......or.... you can OVERLAY the actual display fields and ONLY display the correct running total where and when it needs to display.

Make Sense...?
0
 
Jeff SAuthor Commented:
ok good news .... I dropped in the FacilityID and ResourceID into report and incorporated that into my report. On the change of field, I dropped in my newly created formula @ForRunningTotal.

@ForRunningTotal =
toText({Data.FacilityId}) + toText({Data.ResourceId})

Now I am off 3 hours on my first facility and correct on my second facility. Not to sure what the issue is just yet in the time calculation.
0
 
MIKESoftware Solutions ConsultantCommented:
Well...looks like you are almost there..sounds great!
0
 
Jeff SAuthor Commented:
Got it finally. Thanks
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now