The following is a FML report that displays the ROW-TOTAL at the end of the rows (as expected). I would like it to appear in the first column prior to the ACROSS CORPCUSTNAME. The usual way to do this is to use a multi verb, but I don't know a way to do this in a FML report.


[ code ]

TABLE FILE VWCPR_DATA
SUM TOTALAMOUNT ACROSS HIGHEST CORPCUSTNAME AS ''
WHERE ( VWCPR_DATA.VWCPR_DATA.YEARPERIOD EQ '201810' )
AND ( VWCPR_DATA.VWCPR_DATA.COSTCENTER EQ '222' )
AND ( VWCPR_DATA.VWCPR_DATA.CORPCUSTID EQ 'TEST CUST' );
FOR GROUPCODE
1 AS 'TOTAL REVENUE' LABEL REV OVER
2 AS 'TOTAL EXPENSES' LABEL EXP OVER
3 AS 'REGIONAL ALLOCATION' LABEL REG OVER
BAR OVER
RECAP PROFT=REV-EXP+REG; AS 'Profit' OVER
RECAP PROFTPCT/D8.2=PROFT/REV; AS 'Profit %'
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE SET BYDISPLAY ON
ON TABLE ROW-TOTAL
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
END

[ /code ]

This message has been edited. Last edited by: David M,

If you consider the example within the documentation -

TABLE FILE FINANCE
HEADING CENTER
"COMPARATIVE ASSET SHEET </2"
   SUM AMOUNT ACROSS HIGHEST YEAR
   WHERE YEAR EQ '1983' OR '1982' 
 FOR ACCOUNT
 1000 OR 1010 TO 1050 AS 'TOTAL' OVER
 1000           AS 'UTILITY PLANT'                 LABEL   UTP     OVER 
 1010 TO 1050   AS 'LESS ACCUMULATED DEPRECIATION' LABEL   UTPAD   OVER 
 BAR                                                               OVER 
 RECAP UTPNET = UTP-UTPAD; AS 'TOTAL PLANT-NET'                    OVER
   BAR                                                               OVER
    2000 TO 3999  AS 'INVESTMENTS'                   LABEL   INV     OVER 
 "CURRENT ASSETS"                                                  OVER
    4000          AS 'CASH'                          LABEL   CASH    OVER
    5000 TO 5999  AS 'ACCOUNTS RECEIVABLE-NET'       LABEL   ACR     OVER
    6000          AS 'INTEREST RECEIVABLE'           LABEL   ACI     OVER
    6500          AS 'FUEL INVENTORY'                LABEL   FUEL    OVER
    6600          AS 'MATERIALS AND SUPPLIES'        LABEL   MAT     OVER
    6900          AS 'OTHER'                         LABEL   MISC    OVER
    BAR                                                              OVER
   RECAP TOTCAS=CASH+ACR+ACI+FUEL+MAT+MISC;AS 'TOTAL CURRENT ASSETS' OVER
    BAR                                                              OVER
    7000          AS 'DEFERRED DEBITS'               LABEL   DEFDB   OVER
	BAR                                                              OVER 
 RECAP TOTAL = UTPNET+INV+TOTCAS+DEFDB; AS 'TOTAL ASSETS'          OVER
    BAR AS '='
    FOOTING
    "</2 *** PRELIMINARY ASSET SHEET BASED ON UNAUDITED FIGURES ***"
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE SET BYDISPLAY ON
ON TABLE ROW-TOTAL
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
END

and compare it to the output from this -

DEFINE FILE FINANCE
  YEAR_TOT/D12 = IF YEAR IN ('1982','1983') THEN AMOUNT ELSE 0;
  YEAR_1982/D12 = IF YEAR IN ('1982') THEN AMOUNT ELSE 0;
  YEAR_1983/D12 = IF YEAR IN ('1983') THEN AMOUNT ELSE 0;
END
TABLE FILE FINANCE
HEADING CENTER
"COMPARATIVE ASSET SHEET </2"
   SUM YEAR_TOT AS 'Total'
       YEAR_1983 AS '1983'
	   YEAR_1982 AS '1982'  
   WHERE YEAR EQ '1983' OR '1982' 
 FOR ACCOUNT
 1000 OR 1010 TO 1050 AS 'TOTAL' OVER
 1000           AS 'UTILITY PLANT'                 LABEL   UTP     OVER 
 1010 TO 1050   AS 'LESS ACCUMULATED DEPRECIATION' LABEL   UTPAD   OVER 
 BAR                                                               OVER 
 RECAP UTPNET = UTP-UTPAD; AS 'TOTAL PLANT-NET'                    OVER
   BAR                                                               OVER
    2000 TO 3999  AS 'INVESTMENTS'                   LABEL   INV     OVER 
 "CURRENT ASSETS"                                                  OVER
    4000          AS 'CASH'                          LABEL   CASH    OVER
    5000 TO 5999  AS 'ACCOUNTS RECEIVABLE-NET'       LABEL   ACR     OVER
    6000          AS 'INTEREST RECEIVABLE'           LABEL   ACI     OVER
    6500          AS 'FUEL INVENTORY'                LABEL   FUEL    OVER
    6600          AS 'MATERIALS AND SUPPLIES'        LABEL   MAT     OVER
    6900          AS 'OTHER'                         LABEL   MISC    OVER
    BAR                                                              OVER
   RECAP TOTCAS=CASH+ACR+ACI+FUEL+MAT+MISC;AS 'TOTAL CURRENT ASSETS' OVER
    BAR                                                              OVER
    7000          AS 'DEFERRED DEBITS'               LABEL   DEFDB   OVER
	BAR                                                              OVER 
 RECAP TOTAL = UTPNET+INV+TOTCAS+DEFDB; AS 'TOTAL ASSETS'          OVER
    BAR AS '='
    FOOTING
    "</2 *** PRELIMINARY ASSET SHEET BASED ON UNAUDITED FIGURES ***"
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
END

You'll see one way of achieving what you want.

There are probably more ways of doing it and potentially without hardcoding values and I expect there will be multiple versions arriving shortly

T

Tony,

Thank you for the help on this, I'm still learning the FML language. Unfortunately my ACROSS statement is not a fixed number. I was able to get it to work by using a HOLD file.

[ code ]

-DEFAULTH &YEARPERIOD='_FOC_NULL';
-DEFAULTH &COMPANY='_FOC_NULL';
-DEFAULTH &COSTCENTER='_FOC_NULL';
-DEFAULTH &CORPCUSTID='_FOC_NULL';
-DEFAULTH &TOT_REV='_FOC_NULL';
-DEFAULTH &TOT_PROFIT='_FOC_NULL';
-DEFAULTH &TOT_PROF_PERCENT='_FOC_NULL';
-DEFAULTH &OUTPUT='HTML';
-DEFAULTH &OUTPUT2='HTML';

-SET &OUTPUT2=IF &OUTPUT EQ 'XLSX OPEN NOBREAK' THEN 'XLSX CLOSE' ELSE &OUTPUT;

-* -- SUMMARY Section -----------------------------------------------------------------------------------------------
-* Made use of FML (Financial Modeling Language - FOR syntax) in the first query that creates a HOLD file.
-* The RECAP syntax allows me to take values from previous lines.
-* In order to create the row total in the first column I need to use a multi-verb query.
-* Since I can't create a multi-verb while using FML I created a HOLD file first.
-* In order to get the sorts correct I've prefixed the GROUPCODE and later used EDIT to remove it when displayed.
-* ------------------------------------------------------------------------------------------------------------------
TABLE FILE VWCPR_DATA
SUM
TOTALAMOUNT
BY VWCPR_DATA.VWCPR_DATA.CORPCUSTNAME
WHERE ( VWCPR_DATA.VWCPR_DATA.YEARPERIOD EQ '&YEARPERIOD' )
AND ( VWCPR_DATA.VWCPR_DATA.COSTCENTER EQ '&COSTCENTER' )
AND ( VWCPR_DATA.VWCPR_DATA.CORPCUSTID EQ &CORPCUSTID );
FOR GROUPCODE
1 AS '1-TOTAL REVENUE' LABEL REV OVER
2 AS '2-TOTAL EXPENSES' LABEL EXP OVER
3 AS '3-REGIONAL ALLOCATION' LABEL REG OVER
RECAP PROFT/P12.2=REV-EXP+REG; AS '4-PROFIT' OVER
RECAP PROFTPCT/P12.2=PROFT/REV; AS '5-PROFIT %'
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS HOLD_SUM FORMAT FOCUS
-RUN

-* When I use the multi-verb below to get my row totals, the Profit Percent will also be a row total,
-* which is wrong. So I'm going to read Total Revenue and Total Profit into variables to get the percent.
-* Read the total revenue into a variable
TABLE FILE HOLD_SUM
SUM TOTALAMOUNT AS TOT_REV
WHERE E02 EQ '1-TOTAL REVENUE'
ON TABLE HOLD AS HOLD_REV
END
-RUN
-READFILE HOLD_REV

-* Read the total profit into a variable
TABLE FILE HOLD_SUM
SUM TOTALAMOUNT AS TOT_PROFIT
WHERE E02 EQ '4-PROFIT'
ON TABLE HOLD AS HOLD_PROFIT
END
-RUN
-READFILE HOLD_PROFIT

-* Set the profit percent
-SET &TOT_PROF_PERCENT=&TOT_PROFIT / &TOT_REV;

-* Need to make the customer "Open Space" the first customer in the across, so I created CORPCUSTNAME_SORTED.
DEFINE FILE HOLD_SUM
ACC_DISP/A50=EDIT(E02,'$$99999999999999999999');
CFORMAT/A8=DECODE E02('5-PROFIT %' 'P12.1%' ELSE 'P12C');
CORPCUSTNAME_SORTED/A50=IF CORPCUSTNAME EQ 'Open Space' THEN '- Open Space -' ELSE CORPCUSTNAME;
END
TABLE FILE HOLD_SUM
SUM
COMPUTE GTOTALAMOUNT=IF E02 EQ '5-PROFIT %' THEN '&TOT_PROF_PERCENT' ELSE TOTALAMOUNT; NOPRINT
GTOTALAMOUNT/CFORMAT AS 'Acct Total'
BY E02 NOPRINT
BY ACC_DISP

SUM
TOTALAMOUNT/CFORMAT AS ''
BY E02 NOPRINT
BY ACC_DISP AS 'Account'
ACROSS CORPCUSTNAME_SORTED AS ''

ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE SET BYDISPLAY ON
ON TABLE PCHOLD FORMAT &OUTPUT
-*ON TABLE PCHOLD FORMAT XLSX OPEN NOBREAK
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/WFC/Repository/NFI_Distribution/Hidden_Content/Themes/Detail_Reports.sty,
$
TYPE=REPORT,
LINES-PER-PAGE=UNLIMITED,
WINDOW-DISPLAY=TAB,
$
TYPE=TITLE, COLUMN=N4, SIZE=8, WRAP=OFF,BORDER-BOTTOM=LIGHT,BORDER-BOTTOM-COLOR='BLACK',$
TYPE=DATA,
BORDER-TOP=LIGHT,
BORDER-BOTTOM=LIGHT,
BORDER-LEFT=LIGHT,
BORDER-RIGHT=LIGHT,
BORDER-TOP-COLOR='BLACK',
BORDER-BOTTOM-COLOR='BLACK',
BORDER-LEFT-COLOR='BLACK',
BORDER-RIGHT-COLOR='BLACK',
$
TYPE=ACROSSVALUE,
FONT='TREBUCHET MS',
SIZE=8,
COLOR='WHITE',
BACKCOLOR='RGB(34 77 124)',
WRAP=ON,
$
TYPE=DATA,
COLUMN=N2,
WRAP=OFF,
SQUEEZE=2.0,
$
ENDSTYLE
END

[ /code ]