Hi all,

I'm doing a rewrite project of stored procedures and java into WF, and some of the formatting causes me to go bonkers...

I have the code something like...

TABLE FILE CAR
SUM
MODEL
ACROSS COUNTRY
BY SEATS
ON SEATS SUBTOTAL
ON TABLE SET SUBTOTALS ABOVE
END

Now this works fine, but the spec is causing I want the "TOTAL" row to be at the bottom of the page and not as the first row. I've seen a few McGyver's but the ACROSS is confusing me.

(Probably its all self-evident and I just need more coffee)

This message has been edited. Last edited by: <Kathryn Henning>,

Play with this.

TABLE FILE CAR
SUM
MODEL SALES
ACROSS COUNTRY
BY SEATS
ON SEATS SUBTOTAL
ON TABLE SET SUBTOTALS ABOVE
ON TABLE NOTOTAL
ON TABLE SUBFOOT
"TOTAL <TOT.SALES
END
  

Hi, yeah, I've tried the subfoot but its not quite cracking it.

TABLE FILE CAR
SUM
SALES
ACROSS COUNTRY
BY SEATS
ON SEATS SUBTOTAL
ON TABLE SET SUBTOTALS ABOVE
ON TABLE ROW-TOTAL AS 'Total Seats'
ON TABLE COLUMN-TOTAL AS 'Total Country'
END

Now this has the content I want, only I'd need to be able to have that "Total Country" row as a footer instead of it being the first row.

This message has been edited. Last edited by: Hank W.,

Another approach:

DEFINE FILE CAR
 SEATS_TEXT/A15 = FPRINT(SEATS,'I3','A15');
END
-*
TABLE FILE CAR
 SUM SALES
 COMPUTE SEATS_TEXT/A15 = 'Total Country';
 BY COUNTRY
 ON TABLE HOLD AS TOT_SALES
END
-*
TABLE FILE CAR
 SUM SALES
 ACROSS COUNTRY AS '# of Seats'
 BY SEATS_TEXT  AS ''
 ON SEATS_TEXT SUBTOTAL AS 'Subtotal' WHEN SEATS_TEXT NE 'Total Country';
 ON TABLE ROW-TOTAL AS 'Total Seats'
 ON TABLE SET ACROSSTITLE SIDE
 ON TABLE SET SUBTOTALS ABOVE
 ON TABLE NOTOTAL
 ON TABLE SET STYLE *
  TYPE=ACROSSTITLE, JUSTIFY=LEFT, $
  TYPE=DATA, COLUMN=SEATS_TEXT, JUSTIFY=CENTER, $
 ENDSTYLE
-*
 MORE
 FILE TOT_SALES
END

Very good Dan.

Hank please notice you have to have the NOTOTAL so it won't total up top.

@ Prarie - Thanks!

@ Hank - The placement of the totals at the bottom of the report depends on 'Total Country' sorting to the bottom of the other SEATS_TEXT values. Depending on the data you are actually using, this may or may not be the case. You could add a sort field to the data and NOPRINT it as the first BY, but the easiest way is to add a space to the first position of all the SEATS_TEXT values except the one for totals.

SEATS_TEXT/A15 = ' ' | FPRINT(SEATS,'I3','A14');

This message has been edited. Last edited by: Dan Satchell,

Thanks Prarie & Dan

Hi All,

I made the original by CAR, but its a bit bad example as using the GGSALES table might make more sense.

OK, so the client spec is:
- Sales (DOLLARS)
- BY REGION (subtotal row) split by MONTH (name)
- ACROSS PRODUCT
with row and column totals (grand total in the bottom)

  DEFINE FILE GGSALES
MONTH/M=DATE;
MONTH_NM/A15 = DECODE MONTH
(1 'January' 
2 'February' 
3 'March'   
4 'April'   
5 'May'     
6 'June'     
7 'July'    
8 'August'  
9 'September'
10 'October' 
11 'November'
12 'December');
END

TABLE FILE GGSALES
SUM DOLLARS 
COMPUTE REGION/A11 ='ZZTop';
COMPUTE MONTH/M='';
COMPUTE MONTH_NM/A15='Grand Total';
BY PRODUCT
ON TABLE HOLD AS TOT_SALES
END

TABLE FILE GGSALES
SUM DOLLARS AS ''
BY REGION NOPRINT SUBTOTAL AS 'Total Region' WHEN REGION NE 'ZZTop';
BY MONTH NOPRINT  
BY MONTH_NM AS '' 
ACROSS PRODUCT
ON TABLE ROW-TOTAL AS 'Total Month'
ON TABLE SET SUBTOTALS ABOVE
ON TABLE NOTOTAL
 MORE
FILE TOT_SALES 
END 

This is without any formatting just to keep it simple.

- The month number is required to keep the sorting order.

- For the Region, I used a spoof that definitely doesn't show up, and is the alphabetically last one. It may require some extra work depending on your data.

(One gets these clairvoyant moments going to the Summit 2015 in London and talking FOCUS with some odd fellows like Anthony Alsford )

Hmmm, so I'm an odd fellow eh "Hank"

Of course, you use the "Mtr" format to give you the full month name with leading caps, but then you would have to leave the "Grand Total" blank so as not to receive a "bad date" value error.

DEFINE FILE GGSALES
MONTH/Mtr=DATE;
END

TABLE FILE GGSALES
SUM DOLLARS 
COMPUTE REGION/A11 ='ZZTop';
COMPUTE MONTH/Mtr='';
BY PRODUCT
ON TABLE HOLD AS TOT_SALES
END

TABLE FILE GGSALES
SUM DOLLARS AS ''
BY REGION NOPRINT SUBTOTAL AS 'Total Region' WHEN REGION NE 'ZZTop';
BY MONTH AS ''  
ACROSS PRODUCT
ON TABLE ROW-TOTAL AS 'Total Month'
ON TABLE SET SUBTOTALS ABOVE
ON TABLE NOTOTAL
MORE
FILE TOT_SALES 
END

T