It's sum. And there's also an across if that makes a difference.

Interesting problem but with FOCUS there is always a solution:

The following input data:

TYPE A 0.00
TYPE A 0.00
TYPE A 0.00
TYPE A 0.00
TYPE A 0.00
TYPE B 1.00
TYPE B 2.00
TYPE B 3.00
TYPE B 4.00

Using the following focexec:

TABLE FILE SUMZERO
PRINT *
WHERE AMOUNT NE 0
ON TABLE HOLD AS HDTLZERO FORMAT FOCUS INDEX TYPE
END
-*
TABLE FILE SUMZERO
SUM AMOUNT AS SUMAMOUNT
BY TYPE
ON TABLE HOLD AS HSUMZERO FORMAT FOCUS INDEX TYPE
END
-*
JOIN CLEAR *
JOIN TYPE IN HSUMZERO TO ALL TYPE IN HDTLZERO AS J1.
-*
TABLE FILE HSUMZERO
PRINT AMOUNT IN 20
BY TYPE IN 1
ON TYPE SUBFOOT
" "
" <1 TOTAL " "
ON TABLE SET NODATA ' '
ON TABLE SET ALL ON
END

Produces the following results:

TYPE AMOUNT
---- ------
TYPE A

TOTAL TYPE A .00

TYPE B 4.00
3.00
2.00
1.00

TOTAL TYPE B 10.00

The first table request filters out zero amounts and holds results. The second table file request creates a total amount field by type and holds results. We then join the second request to the first and produce a report showing totals even when no detail records exist.

That's it.

Thanks for the suggestion. In the file table file statement:

TABLE FILE HSUMZERO
PRINT AMOUNT IN 20
BY TYPE IN 1
ON TYPE SUBFOOT
" "
" <1 TOTAL <20
" "
ON TABLE SET NODATA ' '
ON TABLE SET ALL ON
END 

What do "IN 20" and "IN 1" refer to?

Thanks,
Bethany

IN positions the report columns in the specified location. Usually used for Mainframe 132 column reports or PDF reports.

Thanks for the suggestion, but I'm not sure if it will work since I also have an across field of fiscal year.

Bethany

Try to post a part of you fex and an example of the data.

As Albert said, there is always an solution...

Here's a sample of the data:

  FOCLIST ACCT_UNIT ACCOUNT FISCAL_YEAR AMOUNT BTYPE SUB1 SUB2 SRT1 
1 200344 2830 2008 -161.00 Beginning Balance A Ending Balance A 
2 200344 2810 2008 33.79 Beginning Balance A Ending Balance A 
3 200344 2819 2008 .00 NONE Z Z G 
4 200344 2821 2008 .00 Transfer Revenue/Expenses, Net Ending Balance B 
5 200344 2811 2008 .00 Transfer Revenue/Expenses, Net Ending Balance B 
6 200344 2841 2008 .00 Transfer Revenue/Expenses, Net Ending Balance B 
7 200344 2849 2008 .00 NONE Z Z G 
8 200344 2840 2008 .00 Beginning Balance A Ending Balance A 
9 200344 2831 2008 .00 Transfer Revenue/Expenses, Net Ending Balance B 
10 200344 2839 2008 .00 Beginning Balance A Ending Balance A 
11 200344 4170 2008 .00 Net Assets Released Z Z E 
12 200344 5800 2008 .00 Expense Revenue/Expenses, Net Ending Balance D 
13 200344 5801 2008 .00 Expense Revenue/Expenses, Net Ending Balance D 
14 200344 5803 2008 .00 Expense Revenue/Expenses, Net Ending Balance D 
15 200344 5804 2008 .00 Expense Revenue/Expenses, Net Ending Balance D 

And the fex:

  
TABLE FILE HALLDAT
SUM
AMOUNT
BY ACCT_UNIT NOPRINT
BY SUB2 NOPRINT
BY SUB1 NOPRINT
BY SRT1  NOPRINT
BY BTYPE  NOPRINT
BY ACCOUNT
ACROSS HIGHEST FISCAL_YEAR AS ' '
WHERE BTYPE NE 'NONE';
ON ACCT_UNIT SUBHEAD
"<ACCT_UNIT "
ON SRT1 SUBHEAD
"<BTYPE "
ON BTYPE SUBTOTAL AMOUNT AS 'Total'
ON SUB2 SUBTOTAL AMOUNT AS '' WHEN SUB2 EQ 'Ending Balance';
ON SUB1 SUBTOTAL AMOUNT AS '' WHEN SUB1 EQ 'Revenue/Expenses, Net';
ON TABLE NOTOTAL
END

This message has been edited. Last edited by: Bethany,

Based on your fex, there are now rows that would be summed together since you have BY ACCOUNT and every record has a different ACCOUNT value. Try the MULTI_LINES option on the subtotal - doesn't give you an actual line with subtotals, but it only gives you a single line when there is only 1 sort value. Another suggestion would be to use SUBFOOT instead of SUBTOTAL and create conditional subfoots (subfeet??) to only display when the total value ne 0.

 PAGE 1 
 
  
 2008 2007 2006 2005 
ACCOUNT 
200344  
 
Beginning Balance  
 
2810 33.79 33.79 33.79 33.79 
2830 -161.00 -111.00 -46.00 .00 
2839 .00 -50.00 -65.00 -46.00 
2840 .00 .00 .00 .00 
Total Beginning Balance -127.21 -127.21 -77.21 -12.21 
Transfer  
 
2811 .00 .00 .00 .00 
2821 .00 .00 .00 .00 
2831 .00 .00 .00 .00 
2841 .00 .00 .00 .00 
Total Transfer .00 .00 .00 .00 
Revenue  
 
8010 . -50.00 -65.00 -46.00 
Total Revenue .00 -50.00 -65.00 -46.00 
Expense  
 
5800 .00 .00 .00 .00 
5801 .00 .00 .00 .00 
5803 .00 .00 .00 .00 
5804 .00 .00 .00 .00 
Total Expense .00 .00 .00 .00 
Revenue/Expenses, Net .00 -50.00 -65.00 -46.00 
Ending Balance -127.21 -177.21 -142.21 -58.21 
Net Assets Released  
 
4170 .00 .00 .00 .00 
8170 .00 .00 .00 .00 
Total Net Assets Released .00 .00 .00 .00 
 

I tried the conditional subfoot but once I add WHERE AMOUNT NE 0; to the fex to get rid of the lines where the amounts for all accounts (eg. account 2811) for all fiscal years is $0, I lose the subfoot as well.

Basically, in the Transfer section, I want to somehow show the subtotal is $0 without showing all the account detail of $0.

Any other ideas?

Using FOCUS (via SAVE Command), I would add a total record by acct_unit/fiscal year setting the account field to 9999 (last record in sort order), producing the following:

Currently
---------

3 200344 2819 2008 .00 NONE Z Z G
4 200344 2821 2008 .00 Transfer Revenue/Expenses, Net Ending Balance B
5 200344 2811 2008 .00 Transfer Revenue/Expenses, Net Ending Balance B
6 200344 2841 2008 .00 Transfer Revenue/Expenses, Net Ending Balance B
7 200344 2849 2008 .00 NONE Z Z G
8 200344 2840 2008 .00 Beginning Balance A Ending Balance A
9 200344 2831 2008 .00 Transfer Revenue/Expenses, Net Ending Balance B
10 200344 2839 2008 .00 Beginning Balance A Ending Balance A
11 200344 4170 2008 .00 Net Assets Released Z Z E
12 200344 5800 2008 .00 Expense Revenue/Expenses, Net Ending Balance D
13 200344 5801 2008 .00 Expense Revenue/Expenses, Net Ending Balance D
14 200344 5803 2008 .00 Expense Revenue/Expenses, Net Ending Balance D
15 200344 5804 2008 .00 Expense Revenue/Expenses, Net Ending Balance D

Added records
-------------
16 200344 9999 2008 -127.21 Total Beginning Balance
16 200344 9999 2008 .00 Total Transfer

etc.

Obviously, the subfoot command would need to be modified.

Good Luck.

You can include or exclude a section using WHERE TOTAL, but you can't include a subtotal or subfoot without including the associated data. (At least not in a single-pass fex.)

If there's not a compelling reasong to include the Transfer section if it totals 0 then use WHERE TOTAL AMOUNT NE 0 - this excludes the section with all the zeros, but leaves the individuals lines that are zero. This, however would eliminate section where accounts offset(e.g. 2811 is -100 and 2821 is + 100)

If you've got to have a line for transfers, maybe include a defined field to modify the account # if it has a zero balance and sort by that account. That way, all zero accounts get rolled into one line and MULTILINES would eliminate subtotals for the single line.

NEW_ACCOUNT/I4=IF AMOUNT EQ 0 THEN 9999 ELSE ACCOUNT;
then use BY NEW_ACCOUNT instead of BY ACCOUNT

Great idea Darin! I think with some formatting I can make this work.

Thank you ALL for your suggestions!

Bethany