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,"")+"
totext(remainder(x,truncat
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}.
@TotalTime
{Data.ApptTime}/60
@Converted Time
numbervar x := {#RTotal0};
totext(truncate(x),0,"")+"
totext(remainder(x,truncat
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}
ASKER
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?
Morning at Hospital doing Surgery Cases
Afternoon Clinic treating patients
So I think it needs to factor in both not the one. But how?
So do you actually need to subtotal by DOCTOR then...?
ASKER
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.
Doctor Jones
West Side Hospital - 4 hours
Pain Clinic - 4 hours
Total time today for Dr Jones = 8 hours against both Facilities.
What are your GROUPS please...
ASKER
Screen Shot attached with Groups ...
Group-Expert.pdf
Group-Expert.pdf
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Your Help by the way is deeply appreciated. I was worried no one was going to help me out.
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
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
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
MikeV
ASKER
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
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
ASKER
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}
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.ResourceSta
{Data.Resource} <> previous({Data.Resource})
) and
minimum({Data.ApptKind},{D
maximum({Data.Column},{Dat
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?
@TotalTime
If {Data.ApptTime} = 0 then
0
else
{Data.ApptTime}
@Converted Time
If {#RTotal0} = 0 then
"--"
else
ToText(Truncate({#RTotal0}
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.ResourceSta
{Data.Resource} <> previous({Data.Resource})
) and
minimum({Data.ApptKind},{D
maximum({Data.Column},{Dat
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?
2 separate RUNNING TOTALS....???
ASKER
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}?
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...?
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...?
ASKER
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.
@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.
Well...looks like you are almost there..sounds great!
ASKER
Got it finally. Thanks
How is your running total setup to reset...now..?
MikeV