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

 

[talibm]

Sorry about the @ sign. All of the fields mentioned in my previous post are formula fields. The shared variable works when I run the subreport by itself. When I run the main report I get the division by zero error message. I placed the shared variable {@grtot} in the report footer of the subreport and it returns the {@grand_total} for the last worker in the main report's.

 

[talibm]

Just in cast my last report was confusing. I'll try to simply what happens.
The subreport works when I run it alone.

The value that the shared variable {@grtot} returns in the subreport footer is the worker {@grand_total} for the last worker in the report.

It doesn't work with the main report (division by zero error message).

Hope this helps. Thanks t

 

[lbass]

Please reread my last post.

-LB

 

[talibm]

I unsuppressed all sections of the subreport and the suppress blank subreport and I get division by zero error message.thanks

 

[lbass]

Is the subreport itself suppressed? Please explain exactly what you have suppressed and where you did it, because that is likely the problem--you are preventing the shared variable from being shared= through incorrect suppression.

-LB

 

[talibm]

In the main report:
suppressed all headers exceprt Report and worker
suppressed detailed section
suppressed footer for [startdate] and[starttime]
suppressed report footer

In the subreport I had suppressed all sections and suppressed blank report.

I tried suppressing all sections except report footer in subreport. Thanks

 

[lbass]

Please explain the steps you took to do this:

In the subreport I had suppressed all sections and suppressed blank report.

-LB

 

[talibm]

I opened the section expert and checked suppress for each section.
I then went to format -> subreport -> supress blank subreport

 

[lbass]

Can you verify:

1- That the subreport object itself is not suppressed.
2- That the section containing the subreport is not suppressed.

-LB

 

[talibm]

Sorry about the delay in responding. I had to leave for an out of town trip.

I don't think the subreport object is suppressed. I did not suppress it.

The section that contains the subreport is the header for the worker group is not suppressed.

It seems to me that it tryng to do the division create the percent before the {@grand_total} calculates which causes the division by zero.

Another possibility: When I ran the report after unsuppressing the sections and the blank report the report opened to the a blank page and then when to the next page and I saw the totals which were zero but when I went to the next page I got the Division by zero error message. Maybe it needs to reset {@grtot}.

Thanks t

 

[lbass]

Well, yes if the grtot can be zero then it has to be reset. Since this is the grtot per worker, I didn't think that was a possibility. Change the percent formula to:

whileprintingrecords;
shared numbervar grtot;
if grtot = 0 then
0 else
{@cm_total}%grtot

Then add a reset formula (in the main report) to the report header and to the worker group footer_b (a section inserted after the one that displays the calculation). This can be suppressed:

whileprintingrecords;
shared numbervar grtot;
grtot := 0;

-LB

 

[talibm]

Hi LB, I made the changes and all I got was zeros. You're the group total for a worker should never be zero becuase then that worker wouldn't show up in the report.

With being the case how could {@grtot} be zero? In the subreport report footer it displays the {@grtot} amount for the last worker so it would appear to be working.

Its seems to be performing the division before {@grtot} has calculated.

 

[lbass]

Please show the content of the formula {@grtot} in the subreport footer (again--I can't find it).

-LB

 

[talibm]

The content of {@grtot} is

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

The value that it returns in the subreport report footer is the same as the value of the {@grand_total} for the last worker in the report.

 

[lbass]

I still think this is a suppression problem. Please right click on the subreport and make sure the subreport itself is not suppressed and also go into the section expert and make sure the section containing the sub is not suppressed OR hidden.

Can we also assume that the layout looks like this:

RH - reset formula for shared variable
worker GH_a - sub with shared variable
activity group (header or footer): formula referencing the shared variable
worker GF - reset formula for shared variable.

And can we assume the sub is linked to the main report on the worker group?

-LB

 

[talibm]

Nothing in the subreport is suppressed the group header in the main report isn't suppressed or hidden.

The reset formula for the shared variable, {@reset_grtot},
whileprintingrecords;
shared numbervar grtot;
grtot := 0;
is in the report header of the main report and shows 0 when I run the report.

The group header of the main report contains the subreport which has the shared variable {@grtot}.
whileprintingrecords;
shared numbervar grtot := {@grand_total}
I also have that formula in my main report to compare values

In the activity group footer I have {@cm_percent}
whileprintingrecords;
shared numbervar grtot;
if grtot = 0 then
0 else
{@cm_total}%grtot

I added the reset formula {@reset_grtot} to the worker group footer. It was not there before.

And yes the sub is linked to the main report on the worker group. It seems that the report is calculating the {@cm_percent} before the {@grtot}. When I added 'if grtot = 0 then 0' the result is zero. Without that line I get 'division by zero'. Looking at {@grtot} in the report footer it has the sam value as {@grand_total} not 0. Thanks

 

[lbass]

What's in {@cm_total} then?

-LB

 

[talibm]

if {SERVICECODES.SERVICECODE} = "CDCMG01" then
{@April} + {@August} + {@December} + {@February} + {@january} + {@July} + {@June} +
{@March} + {@May} + {@November} +{@October}+ {@September}

 

[lbass]

I give up.

-LB

 

[talibm]

Thanks for all your help. I will take another look at it tomorrow and then move on. Maybe I will start over and attempt a mock cross tab report. Any I really appreciate all your help. Thanks