Group selection and 2 running totals

[DNEELY]

I am trying to use two running totals in a group and have the amounts for one not be included in the report totals when the other running total for the group = 0.00 I used group selection and this formula Sum({Encumbrance Amt})<>0.00. Currently these groups are not showing on the report, but the totals for the report reflect all these amounts. Below is an example of a group for which I want to remove the expenditure amounts from all totals on the report.
EX: Expenditure Amt Encumbrance Amt
200.00
100.00 (100.00)
100.00 (100.00)
-------- ---------
200.00 0.00

 

[JeffreyMorrison]

Create two formulas:

Place the first in your group footer line:

WhilePrintingRecords;
numberVar Test ;
if Sum ({Encumbrance Amt}, {&quot;Your group&quot;}) <> 0 then
Test := Test + Sum ({ Expenditure Amt}, {&quot;Your Group&quot;})

Place the second in your report footer line:

WhilePrintingRecords;
numbervar Test;
Test;

 

[DNEELY]

Thank you for answering this question. I got little error messages when I was creating the first formula which ended up exactly as below before I got the message !The summary/running total field could not be created.

WhilePrintingRecords;
numberVar Test;
if Sum ({@Encumbrance Amt}, GroupName ({@Document)<>&quot;0&quot then
Test:=Test + Sum ({@Expenditure Amt}, GroupName ({Document}) )

 

[JeffreyMorrison]

Is your {@Encumbrance Amt}field a number or a string? Are you able to right click on the field and insert a subtotal in your group?

 

[DNEELY]

The {@Encumbrance Amt} field is a number. I cannot right click on the field and insert a subtotal, but I can insert a summary in the group. When creating the 1st formula I had to use quotes around the 0 to get rid of the error message &quot;expecting a string&quot;. There is a whole group of us working on this, and one person suggested writing a formula that says if the sum of the Encumbrance Amt = 0 subtract the the sum of the Expenditure Amt from the totals.

 

[rogar]

<<if Sum ({@Encumbrance Amt}, GroupName ({@Document)<>&quot;0&quot then...>>
You have a misplaced parenthesis in this expression which caused the error message &quot;expecting a string&quot;. You should NOT have used quotes. The correct expression should be:

if Sum ({@Encumbrance Amt}, GroupName ({@Document)))<>0 then...

Also, is a direct comparison with zero appropriate? You might need to use &quot;<1&quot; or < 0.01&quot; if the value might be a small number as a result of rounding or conversion.

 

[DNEELY]

Now the first formula is exactly as below, but still the error message !The summary/running total field could not be created appears.

WhilePrintingRecords;
numberVar Test;
if Sum ({@ENCUMBRANCE AMT},GroupName ({@Document}))<>0 then
Test:=Test + Sum ({@EXPENDITURE AMT},GroupName ({@Document}) )

 

[rogar]

Post the formula for {@ENCUMBRANCE AMT}. You cannot sum a formula that contains another summary or the expression &quot;WhilePrintingRecords&quot;. If you place {@ENCUMBRANCE AMT} in a detail section and right-click, can you insert a summary there?

 

[DNEELY]

Below is the formula for {@ENCUMBRANCE AMT}.

IF {FR_HX_REPOS.COMPTR_GEN_LEDG_CD} = [&quot;9001&quot;, &quot;9002&quot;]
THEN {FR_HX_REPOS.XACTION_AMT} ELSE 0

I can place {@ENCUMBRANCE AMT} in the detail section and insert a summary that shows under the magnifying glass as Sum of @ENCUMBRANCE AMT (number).

 

[rogar]

You apparently have a group based on the formula {@Document}. Can you insert a summary of {@ENCUMBRANCE AMT}in that group footer? If you can, then that summary field will appear in the Fields list of the Formula Editor and can be selected when you edit the formula that is giving you the error message. If you cannot create the summary in the design view, then the error message is correct and the summary cannot be created.

What is the formula {@Document}?

 

[DNEELY]

Yes, a summary of {@ENCUMBRANCE AMT} is in the group footer of {@Document}. I can see that summary field in the Fields list of the Formula Editor.

The formula {@Document} is

if {FR_HX_REPOS.XACTION_CD} = &quot;210&quot;
then {FR_HX_REPOS.CURR_DOC_CD}
else {FR_HX_REPOS.REF_DOC_CD}

 

[rogar]

You should be able to edit or re-enter the formula (that produced the error that started this thread), selecting the summary field from the Fields list - if it's there, then the &quot;The summary/running total field could not be created&quot; message makes no sense.

Um. Unless it's the &quot;while printing records&quot; statement that causes the error.

 

[DNEELY]

Seems like we have looked this over pretty good and it just doesn't work. Maybe it has to do with the passes. I'll just try to work around it. Thanks for your input!