Total on Formula 3

[YANKRAY]

Crystal 9.0

I am using this formula for a Group 2 total.

{#Estimated Hours} - {@Earned Value}

Now I need to subtotal for Group 1 and a Grand Total.

What is the formula for Group 1 and Grand totals?

 

[dgillz]

{#Estimated Hours} is a running total field, and you cannot summarize a running total field. You need to create another running total field that resets itself at a different time, then subtract the summary of the formula field {@Earned Value} from it.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
askdon@srhconsulting.com

http://www.srhconsulting.com

 

[YANKRAY]

I am not exactly sure what you mean?
Can you give me a little more guidance?

 

[lbass]

The symbol # indicates a running total. Since the running total expert has a built-in reset component, new running totals have to be developed for the Group #1 and report levels, where you change the resets. For Group #1 total, you would change the reset to "on change of group"->Group #1. For the report total, you would use "Reset Never".

You need to share the contents of {@Earned Value} and explain whether this needs to be summarized in some way before anyone can recommend how your formula should be created for group #1 and the grand total.

-LB

 

[YANKRAY]

This is the contents of {@Earned Value}.

If {WO_MASTER.ORDER_STATUS} = "O" and {@Variance} <= 0 then
.9 * ({#Estimated Hours}) else
If {WO_MASTER.ORDER_STATUS} = "O" and {@Variance} > 0 then
Sum ({@Actual Hours}, {SR_VISIT_WORK_CARD.WORK_ORDER_NO}) else
If {WO_MASTER.ORDER_STATUS} = "C" then {#Estimated Hours}
else 0.

LB - this is the same report you gave me help on in thread 149-938548. I have work orders that employees charge time to. All the work orders are given an estimated hours to complete. I am creating an earned value report to show the estimated remaining hours depending whether the work order is open and still has time remaining or open and the employee has exceeded the time.

 

[YANKRAY]

I do need to summarize {@Earned Value} at the Group 1 Level and as a Grand Total.

 

[lbass]

I don't know the name of your orignal formula. Let's call the following formula {@grp2subtotal}:

{#Estimated Hours} - {@Earned Value}

What summary do you want to apply? Do you want to just add the results of this formula at the group 1 level and report level? If so, then create four formulas:

//{@reset} to be placed in the group #1 header and suppressed:
whileprintingrecords;
numbervar grp1subtot := 0;

//{@accum} to be placed in the group #2 footer and suppressed:
whileprintingrecords;
numbervar grp1subtot := grp1subtot + {@grp2subtotal};
numbervar grtot := grtot + {@grp2subtotal};

//{@displaygrp1subtot} to be placed in the group #1 footer:
whileprintingrecords;
numbervar grp1subtot;

//{@displaygrtot} to be placed in the report footer:
whileprintingrecords;
numbervar grtot;

-LB

 

[YANKRAY]

LB

As always, the perfect solution!

I was able to use this on the other columns that were giving me trouble. My report looks good. All I have to do is verify some of the results.

Thank-you very much!

Ray

 

[YANKRAY]

I spoke too soon about being finished with this report.

Another problem with the totals.

I have a formula {@EV%} written as:

If {#Estimated Hours} = 0 then 0
else ({@Earned Value}/{#Estimated Hours})*100

This give me a percent complete based on Group2 work orders.
I need to average these for the Group1 total.
Whenever I try to use the formula {@EV%}, I get the message "This field cannot be summarazed".

I thought a running total average would work, but the formula {@EV%} is not available for a running total.

How can I get an average of {@EV%} for Group2 at the Group1 Level. A Grand total would be usefull but not required for the report.

 

[lbass]

You would use variables again:

//{@resetave} to be placed in the group 1 header:
whileprintingrecords;
numbervar sumev := 0;
numbervar grp2cnt := 0;

//{@accumave} to be placed in the group 2 footer:
whileprintingrecords;
numbervar sumev := sumev + {@EV%};
numbervar grp2cnt := grp2cnt + 1;

//{@displave} to be placed in the group 1 footer:
whileprintingrecords;
numbervar sumev;
numbervar grp2cnt;
sumev/grp2cnt

-LB