Running totals picking up all figures

[stcholly]

V10.0 - I'm trying to create a running total at the end of group (by year of project) to sum the revised contract amount. Using section expert, I've suppressed based on the following formula:

{@YTD Costs} = 0 and {@YTD Work Billed} = 0 and {@Over-Billing} = 0 and {@Under-Billing} = 0

I'm trying to do a running total to only pick up jobs that meet the above criteria, but I'm getting all jobs (even those that are suppressed) in my total. How can I omit the supressed jobs in my running total?

 

[lbass]

In the running total evaluation section, choose "use a formula" and enter:

not(
{@YTD Costs} = 0 and
{@YTD Work Billed} = 0 and
{@Over-Billing} = 0 and
{@Under-Billing} = 0
)

-LB

 

[stcholly]

I rec'd the following message:

A running total cannot refer to a print time formula. Details: @Over-Billing

 

[lbass]

You should always share the contents of any nested formulas in your posts, and now we need to know the contents of {@Over-Billing}.

-LB

 

[stcholly]

Sorry about that....It's Monday and USA lost...<smile>

Over-Billing formula is:

IF {@JTD Revenue Earned} < Sum ({@JTD Work Billed}, {CURRENT_JCT_TRANSACTION.Job})

Then Sum ({@JTD Work Billed}, {CURRENT_JCT_TRANSACTION.Job}) - {@JTD Revenue Earned}

 

[lbass]

ALL nested formulas.

-LB

 

[stcholly]

JTD Revenue Earned Formula:
IF {MASTER_JCM_JOB.Percent_Complete} < 100
Then {@% Complete * Revenue}
Else Sum ({@JTD Work Billed}, {CURRENT_JCT_TRANSACTION.Job})

JTD Worked Billed Formula:
IF {CURRENT_JCT_TRANSACTION.Transaction_Type} = "Work Billed" and {CURRENT_JCT_TRANSACTION.Accounting_Date} <= {?Cut Off Date}
Then {CURRENT_JCT_TRANSACTION.Amount}

JTD Revenue Earned Formula:
IF {MASTER_JCM_JOB.Percent_Complete} < 100
Then {@% Complete * Revenue}
Else Sum ({@JTD Work Billed}, {CURRENT_JCT_TRANSACTION.Job})

%Complete * Revenue Formula:
{MASTER_JCM_JOB.Percent_Complete}/100*{MASTER_JCM_JOB.Revised_Contract_Amount}

 

[lbass]

I'm not sure what the issue is, although sometimes too many layers of nesting can be a problem. Is {current_jct_transaction.job} your only group and is it the year group you mentioned? Otherwise what is the group structure of your report?

-LB

 

[stcholly]

Three Groups:
GP 1 - Year of Job (which is first two digits of Job#)
Formula: Left ({MASTER_JCM_JOB.Job},2)
GP 2 - Transactions by Job {current_jct_transaction.job}
GP 3 - Job {master_jcm_job.job}

I was able to get my totals to work for each job, but I'm not having any luck getting my colums to total by "year" and then I'll need to do report totals in the report footer.

 

[lbass]

How did you get the correct totals by job? If you then only need to add the correct totals at the year and report level, try this, assuming that you got the correct total by job using a running total {#jobtotal}:

//{@reset} to be placed in GH#1:
whileprintingrecords;
numbervar yrtot := 0;

//{@accum} to be placed in the GF#3 (or is this at the GF#2 level?--wherever your working running total displays):
whileprintingrecords;
numbervar yrtot := yrtot + {#jobtotal};
numbervar grtot := grtot + {#jobtotal};

//{@displyrtot} to be placed in GF#1:
whileprintingrecords;
numbervar yrtot;

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

-LB

 

[stcholly]

Many of the job total are built into the software. For those that weren't, I was able to sum the individual transaction, where certain criteria were met. I have not used running totals up to this point.

 

[lbass]

Please show the formula you used to sum the individual transaction "where certain criteria are met" and explain which group this summarizes.

-LB

 

[stcholly]

In the Details Section (which is all supressed) are the following formulas:

Job to Date Costs (as of cut-off prompt):
IF {CURRENT_JCT_TRANSACTION.Transaction_Type} in ["AP cost","JC cost","PR cost","EQ Cost","IV Cost"]
and ({CURRENT_JCT_TRANSACTION.Accounting_Date} <= {?Cut Off Date}) then {CURRENT_JCT_TRANSACTION.Amount}

YTD costs (as of cut-off):
IF {CURRENT_JCT_TRANSACTION.Accounting_Date} in {@Year Begin Date} to {?Cut Off Date}
AND {CURRENT_JCT_TRANSACTION.Transaction_Type} in ["AP cost","JC cost","PR cost","EQ Cost","IV Cost"] then {CURRENT_JCT_TRANSACTION.Amount}

Job to Date Work Billed:
IF {CURRENT_JCT_TRANSACTION.Transaction_Type} = "Work Billed" and {CURRENT_JCT_TRANSACTION.Accounting_Date} <= {?Cut Off Date}Then {CURRENT_JCT_TRANSACTION.Amount}

These all sum to the GF2 section, which gives me totals in columns by job. In the GF1 section, I'm trying to sum by Year. The RF will sum all jobs.

The formulas that I'm having difficulty getting to sum by year are those that are formulas that appear only in the GF2 section. Most are listed in the above post.

ie. Over-Billings:
IF {@JTD Revenue Earned} < Sum ({@JTD Work Billed}, {CURRENT_JCT_TRANSACTION.Job})Then Sum ({@JTD Work Billed}, {CURRENT_JCT_TRANSACTION.Job}) - {@JTD Revenue Earned}

System tells me this field cannot be summarized. The criteria I'm trying to use, to determine which jobs show are those jobs which either have YTD Costs Recognized, YTD Revenue Recognized, Over-billings or Underbillings. If one of these criteria is met, then the amounts should be included in my totals.

 

[lbass]

Okay, then try these formulas:

//{@reset} to be placed in GH#1:
whileprintingrecords;
numbervar yrtot := 0;

//{@accum} to be placed in the GF#2:
whileprintingrecords;
numbervar yrtot;
numbervar grtot;
if not(
{@YTD Costs} = 0 and
{@YTD Work Billed} = 0 and
{@Over-Billing} = 0 and
{@Under-Billing} = 0
) then
yrtot := yrtot + {@Overbillings};
grtot := grtot + {@Overbillings};

//{@displyrtot} to be placed in GF#1:
whileprintingrecords;
numbervar yrtot;

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

Note that if your datatype is currency, you need change all "numbervar" to "currencyvar".

You could then create separate variables if you need to repeat this process for other summary formulas.

-LB

 

[stcholly]

This worked! Except....The {@accum}, and thus the {@displyrtot} in GF#1, does not seem to reset at the end of the year. Did I overlook something? (Note: I've had beginner Crystal training only, so these formulas are a little outside my realm of training).

Also, I want to sincerly thank you for your help with this!

 

[lbass]

Did you add this formula into the group header?

//{@reset} to be placed in GH#1:
whileprintingrecords;
numbervar yrtot := 0;

Note that the {@accum} formula will show the accumulating grand total as this accumulation is the last clause in that formula. You should be suppressing this formula so that it does not display. However, the {@displyrtot} should display the year total correctly. Make sure that the variable names are the same in each set of formulas.

-LB

 

[stcholly]

It worked ~ Thank you!