Sum of Group average 4

[talibm]

Hi all, I am trying unsuccesssfully to create a formula to sum the average of a field. I have created a worker productivity cross tab report which is based on services provided to our clients. One of the services we provide is group counseling. In a group there may be 10 clients and the total time for the service is 180 minutes or 3 hrs. The details section shows 180 minutes for each client, but the worker productivity is 3 hours. The report is grouped by worker, service code, start date, and total time. i need to sum the average for total which has been converted to hours.I created this formula for the group service:

If {SERVICECODES.SERVICECODE} = "Group" then
Average ({ACTIVITIES.TOTALTIME},{ACTIVITIES.TOTALTIME})/60.
It returns this:
Date start end no.Clients totaltime prod
3-1-08 9am 11am 10 1200 2hrs
3-2-08 1pm 4pm 5 900 3hrs

So for each group service i get the average or the actual productivity for the worker, but I can't get a sum of the average. Ive tried running totals but I can't get it to reset. How do I sum the results for productivity. I am trying to create a manual cross tab report but I can't figure out how to sum the average


Thanks

 

[lbass]

Oh, then you have to add that condition to the group summary, as in:

maximum({ACTIVITIES.TOTALTIME}, {ACTIVITIES.STARTTIME}, "By Minute")

Add this third condition wherever you reference that group.

-LB

 

[talibm]

LB you're the man! Thanks for your patience. It works perfectly. I'll do some testing and then I'm almost done. One last question. I have couple of questions about totals. Is it possible to total by month and by service code for each worker. Again thanks you so much. You're the absolute best. tm

 

[talibm]

Hi LB I hope that you get this post. For the past week or so I've tried to get total for each month and totals for each service code. If you could help again I would really appreciate it. Also could you provide me with the names of some good reference materials that would help me with crystal reports. Thnaks

 

[lbass]

Please show sample data that includes all groups and the detail section, and identify each report section and where you currently have formulas placed.

-LB

 

[talibm]

My report is grouped by worker, servicecode, startdate and starttime. In the starttime group I have the accumulation formula. I have the reset formula in the servicecode group header. In the servicecode group footer I have the formula for each month. Sample data is below
Worker1
Service CodeJan Feb Mar Apr May Jun ... Dec TOTAL
Case Mgmnt 10 10 15 20 21 10 20
Ind Counsl 6 11 0 12 12 8 18
Diag Assmnt 4 14 13 15 19 18 17
Group Couns 30 32 40 25 32 31 29
TOTALS
The detail section which includes each individual service is suppressed so is the startdate group and starttime group. If you want to see a sample of the detail section it is in my May 7th post above. If I could get the totals for the month in the footer of each worker and the total for each service in the service code group footer that would be great. Thanks

 

[talibm]

Hello LB, I hope you get this message. I went back to the accumulation formula and altered it for monthly totals. I altered the reset formula and placed it in the header for the workers group. It works great. The YTD totals were much simpler I just added the formulas for each month. Thanks a million for your help and also for letting me figure out that you had given me enough info to figure out the last part of this report. Thanks. T

 

[talibm]

Hi LB, I'm in need of some additional help with this report. Everything is working great, but now I have been asked if i can provide percentages for the service totals. This is what the report should show for each worker:
Service Code Jan Feb Mar...Dec TOTAL Percent
Case Mgmnt 10 10 15 20 55 24%
Ind Counsl 6 11 0 12 29 11%
Diag Assmnt 5 12 13 15 45 20%
Group Couns 30 20 40 10 100 44%
TOTAL 229
I tried simple dividing the total for each individual service code by the total for each worker by the calculation wasn't correct.
The report is grouped by worker,service code, startdate and starttime. Crystal 10 is the version that I am using. Thanks

 

[lbass]

What is your current formula for the total per service code? What is your current formula for the total per worker?

-LB

 

[talibm]

Below is the formula that you gave me to get the totals for each month and formulas were created to convert minutes to hours (whileprintingrecords;
numbervar totjan;
totjan/60). I just added them @{Jan}+ @{feb}... to get the totals for year.

whileprintingrecords;
numbervar totjan;
numbervar totfeb;
numbervar totmar;
numbervar totapr;
numbervar totmay;
numbervar totjun;
numberVar totjul;
numbervar totaug;
numbervar totsep;
numbervar totoct;
numbervar totnov;
numbervar totdec;
if month ({ACTIVITIES.STARTDATE}) = 1 and
year ({ACTIVITIES.STARTDATE}) = 2008 then (
if {SERVICECODES.SERVICECODE} = "CDCOU05" or {SERVICECODES.SERVICECODE} = "CDIOP01" or
{SERVICECODES.SERVICECODE} = "MARPCOU05" or {SERVICECODES.SERVICECODE} = "MARPIOP01" then
totjan := totjan + maximum ({ACTIVITIES.TOTALTIME}, {ACTIVITIES.STARTTIME}, "by minute") else
if {SERVICECODES.SERVICECODE} <> "CDCOU05" or {SERVICECODES.SERVICECODE} <> "CDIOP01" or
{SERVICECODES.SERVICECODE} <> "MARPCOU05" or {SERVICECODES.SERVICECODE} <> "MARPIOP01" then
totjan := totjan + sum ({ACTIVITIES.TOTALTIME}, {ACTIVITIES.STARTTIME}, "by minute")) else
if month ({ACTIVITIES.STARTDATE}) = 2 and
year ({ACTIVITIES.STARTDATE}) = 2008 then (
if {SERVICECODES.SERVICECODE} = "CDCOU05" or {SERVICECODES.SERVICECODE} = "CDIOP01" or
{SERVICECODES.SERVICECODE} = "MARPCOU05" or {SERVICECODES.SERVICECODE} = "MARPIOP01" then
totfeb := totfeb + maximum ({ACTIVITIES.TOTALTIME}, {ACTIVITIES.STARTTIME}, "by minute") else
if {SERVICECODES.SERVICECODE} <> "CDCOU05" or {SERVICECODES.SERVICECODE} <> "CDIOP01" or
{SERVICECODES.SERVICECODE} <> "MARPCOU05" or {SERVICECODES.SERVICECODE} <> "MARPIOP01" then
totfeb := totfeb + sum ({ACTIVITIES.TOTALTIME}, {ACTIVITIES.STARTTIME}, "by minute"))

 

[lbass]

And how did you get the total per worker?

-LB

 

[talibm]

The report is grouped by worker with this formula @{reset} in the group header for the worker
whileprintingrecords;
numbervar sumjan;
numbervar sumfeb;
numbervar summar;
numbervar sumapr;
numbervar summay;
numbervar sumjun;
numberVar sumjul;
numbervar sumaug;
numbervar sumsep;
numbervar sumoct;
numbervar sumnov;
numbervar sumdec;
if month ({ACTIVITIES.STARTDATE}) = 1 and
year ({ACTIVITIES.STARTDATE}) = 2008 then (
if {SERVICECODES.SERVICECODE} = "CDCOU05" or {SERVICECODES.SERVICECODE} = "CDIOP01" or
{SERVICECODES.SERVICECODE} = "MARPCOU05" or {SERVICECODES.SERVICECODE} = "MARPIOP01" then
sumjan := sumjan + maximum ({ACTIVITIES.TOTALTIME}, {ACTIVITIES.STARTTIME}, "by minute") else
if {SERVICECODES.SERVICECODE} <> "CDCOU05" or {SERVICECODES.SERVICECODE} <> "CDIOP01" or
{SERVICECODES.SERVICECODE} <> "MARPCOU05" or {SERVICECODES.SERVICECODE} <> "MARPIOP01" then
sumjan := sumjan + sum ({ACTIVITIES.TOTALTIME}, {ACTIVITIES.STARTTIME}, "by minute"))
The formula I gave you earlier was to reset the totals this the sum for each worker.

 

[lbass]

No, the earlier formula was not a reset, and no this is not a formula for the total. Just right click on the formula in the lower right for Total of all months across all services per customer and see what the formula is there.

-LB

 

[talibm]

Ok how is this
Gropu Counseling is//
if {SERVICECODES.SERVICECODE} = "CDCOU05" then
{@April} + {@August} + {@December} + {@February} + {@january} + {@July} + {@June} +
{@March} + {@May} + {@November} +{@October}+ {@September}
Individual Counseling is//
if {SERVICECODES.SERVICECODE} = "CDCOU01" then
{@April} + {@August} + {@December} + {@February} + {@january} + {@July} + {@June} +
{@March} + {@May} + {@November} +{@October}+ {@September}
and so on for each service.
The Grand total for each worker is//
{@Apr_tot} +{@Aug_tot} + {@Dec_tot} + {@Feb_tot} +{@Jan_tot} + {@July_tot} + {@Jun_tot} +
{@Mar_tot} + {@May_tot} + {@Nov_tot} + {@Oct_tot} + {@Sep_tot}.
I hope this is what you're asking for. Thanks

 

[lbass]

Well, I don't have a clue what the content of {@Apr_Total} is or any of the others. Please show the contents of one of the formulas contributing to the grand total.

-LB

 

[talibm]

The {apr_Total} is

whileprintingrecords;
numbervar totapr;
totapr/60

Which comes from the following:

if month ({ACTIVITIES.STARTDATE}) = 4 and
year ({ACTIVITIES.STARTDATE}) = 2008 then (
if {SERVICECODES.SERVICECODE} = "CDCOU05" or {SERVICECODES.SERVICECODE} = "CDIOP01" or
{SERVICECODES.SERVICECODE} = "MARPCOU05" or {SERVICECODES.SERVICECODE} = "MARPIOP01" then
totapr := totapr + maximum ({ACTIVITIES.TOTALTIME}, {ACTIVITIES.STARTTIME}, "by minute") else
if {SERVICECODES.SERVICECODE} <> "CDCOU05" or {SERVICECODES.SERVICECODE} <> "CDIOP01" or
{SERVICECODES.SERVICECODE} <> "MARPCOU05" or {SERVICECODES.SERVICECODE} <> "MARPIOP01" then
totapr := totapr + sum ({ACTIVITIES.TOTALTIME}, {ACTIVITIES.STARTTIME}, "by minute"))
The {apr_Total} is placed in the group footer for the worker group. In earlier posts you showed me how to set the sums for each servicecode which was placed in the group footer for service codes. I get the total for each group, servicecode, here and I get the monthly totals from the formula above. If you could look a our earlier posts that might help. You gave me the formulas except for the totals. These I copied and modified to use for the worker group totals. Thanks for your patience.

 

[lbass]

I do not have the time or patience to revisit this entire thread again. Sorry. The problem is now that the totals are not available until the group footer, so you cannot use the grand total for the percentages that appear for each activity. I'm guessing that this report could have been done in some less complicated way, but since you are this far down the road, I would set the grand total up as a shared variable, e.g.:

whileprintingrecords;
shared numbervar grtot := {@yourgrandtotalformula};

Then save the report as a subreport that is placed in the worker group header, and link the sub to the main report on the worker group. The shared variable formula should be in the report footer of the subreport. Then suppress all sections within the subreport, format the subreport (format->subreport->subreport tab->"suppress blank subreport".

Then in the main report in the activity group footer where you have the total per activity, create a formula like this:

whileprintingrecords;
shared numbervar grtot;
{@yourservicegrouptotal}%grtot

-LB

 

[talibm]

Hello LB, I tried your suggestion but I get an error "Division by Zero". I checked the subreport's report footer and it contains the grand total for the last worker in the report. I created a formula field {@grtot}
whileprintingrecords;
shared numbervar grtot := {grand_total};
I saved the report as a subreport and placed it in the group header for the workers group suppressed all sections and then suppressed the blank report.
Next i created a formula {@cm_total}
whileprintingrecords;
shared numbervar grtot;
{@cm_total}%grtot
When I try to run the report I get the Division by Zero Error message. Thanks again for your help and your patience.
t

 

[lbass]

I don't understand what {grand_total} is in your subreport formula. Does it display the correct amount in the subreport? Also, note that you CANNOT suppress the subreport object itself OR the section that the sub is located in--or the shared variable will not work.

Where did you place the formula {@cm_total}?

-LB

 

[talibm]

The {grand_total} field is where I added the all the totals for each month ({jantot}+ febtot + martot ...).

The subreport displays all the correct amounts just as in the main report. I placed the {cm_total} in the activity footer of the main report. I did suppress all sections in the subreport and suppressed blank report.

The shared variable, {grtot}, does display in the subreport footer. It displays the same amount as the {grand_total} for the last worker.

thanks t

 

[lbass]

You should indicate a formula by using the @ sign to distinguish it from a database field. Please unsuppress the subreport sections and the suppress blank subreport--all suppressions on the sub, and then report back about whether the shared variable is now working.

-LB