I’ve created a focexec to display a report of fees earned in a calendar year. In this case,
I want to query the database for two years at once.

In the first table request so I coded:
WHERE FACYY EQ '2004' OR '2005'
ON TABLE HOLD AS HOLDMGA FORMAT FOCUS
to load all the data to a hold file.

In the report request I coded

TABLE FILE FEES
SUM XX/C YTD/C
ACROSS BLAH_SORT NOPRINT
ACROSS BLAHBLAH/C AS ''
BY YEAR NOPRINT
BY BLAH_SORT NOPRINT
BY HELLODOLLY AS FEE
ON YEAR PAGE-BREAK SUMMARIZE

FOOTING BOTTOM
. . . .
END
-RUN

I am getting the expected output like:

Little Bopper Corporation
Calendar Year Fee Report For 2004

Fee MTD YTD
---- ------ ------
---- ------ ------
---- ------ ------
---- ------ ------
---- ------ ------
* Total Year 2004 1,234

Little Bopper Corporation
Calendar Year Fee Report For 2005

Fee MTD YTD
---- ------ ------
---- ------ ------
---- ------ ------
---- ------ ------
---- ------ ------
* Total Year 2005 4,321


Little Bopper Corporation
Calendar Year Fee Report For ????

Fee MTD YTD
---- ------ ------
---- ------ ------
---- ------ ------
---- ------ ------
---- ------ ------
* Total 5,555

I am trying to get a date range (2004-2005)
to show on the Final Page of the Report (to replace
the ???).

Maybe I could do this with dialogue manager,
But I wondered if anyone has played with
Mainframe FOCUS to get the date(s) to show when
reporting on multiple sort values?

Suggestions anyone?

Thanks!

Tom,

I can't think of a method using just a single TABLE request, but you could perform the first part breaking on the year with the heading of each page to show the year value, but do not perform the summary. Then have a second request to provide the summary report giving the totals with a fixed heading showing for all years.

T

For example -

DEFINE FILE GGSALES
  YEAR/YY = DATE;
END
TABLE FILE GGSALES
SUM DOLLARS BUDDOLLARS
BY YEAR NOPRINT PAGE-BREAK
BY REGION
BY ST
WHERE DATE FROM '19960101' TO '19971231'
HEADING
"</1 Dollar detail by Region for <YEAR </1"
ON YEAR PAGE-BREAK
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE-NUM OFF
END
-RUN
TABLE FILE GGSALES
SUM DOLLARS BUDDOLLARS
BY REGION
BY ST
WHERE DATE FROM '19960101' TO '19971231'
HEADING
"</1 Dollar detail by Region for <MIN.YEAR to <MAX.YEAR </1"
ON YEAR PAGE-BREAK
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE-NUM OFF
END
-RUN

Tom,
If I understand correctly what you're looking for, this should work:

ON TABLE SUMMARIZE AS 'TOTAL 2004-2005'

or

ON TABLE SUMMARIZE AS 'TOTAL &YEAR1 - &YEAR2'

Greg

quote:

BY YEAR NOPRINT PAGE-BREAK

Gregv,

I tried your method and I get an error for using the AS statement.

Tomsweb

quote:

TABLE FILE GGSALES
SUM DOLLARS BUDDOLLARS
BY YEAR NOPRINT PAGE-BREAK
BY REGION
BY ST
WHERE DATE FROM '19960101' TO '19971231'
HEADING
"ON YEAR PAGE-BREAK
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE-NUM OFF
END

Tony,

In you upper code snippet I have added
...
BY YEAR NOPRINT
BY YEAR NOPRINT PAGE-BREAK
This give me
* TOTAL YEAR 2004 999,999
and
* TOTAL YEAR 2005 999,999
on the totals page for the individual year.
and the end I get a Grand Total ....

In the lower code snippet I get the 2 year (2004-_) totals for each record, but no Grand Total.

Thanks,
Tomsweb

om,

Apologies, firstly you do not need the ON YEAR PAGE-BREAK in the second report as there is no supporting BY YEAR.

In your statement above you do not require both statements -

quote:

BY YEAR NOPRINT
BY YEAR NOPRINT PAGE-BREAK

Just the second one will do.

Lastly, there is no summarize on the example I gave you so you would have to add one or do you have an ON TABLE NOTOTAL ?

T

Tom,
Here's a better example of what I was thinking.

-SET &YEAR1='1996';
-SET &YEAR2='1997';
DEFINE FILE GGSALES
YEAR/YY=DATE;
END
TABLE FILE GGSALES
SUM DOLLARS BUDDOLLARS
BY YEAR PAGE-BREAK SUMMARIZE
BY REGION
BY ST
WHERE YEAR FROM '&YEAR1.EVAL' TO '&YEAR2.EVAL'
HEADING
"YEAR ON TABLE SUMMARIZE AS 'TOTAL &YEAR1.EVAL - &YEAR2.EVAL'
END