In FOCUS Since 1983 ~ from FOCUS to WebFOCUS. Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206 |
TABLE FILE CAR SUM DEALER_COST RETAIL_COST SALES BY LOWEST COUNTRY NOPRINT BY LOWEST CAR BY LOWEST MODEL ON COUNTRY SUBTOTAL AS '' ON TABLE SET PAGE-NUM NOLEAD ON TABLE NOTOTAL ON TABLE PCHOLD FORMAT HTML ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * INCLUDE = endeflt, $ ENDSTYLE END
TABLE FILE CAR SUM DEALER_COST RETAIL_COST SALES BY LOWEST COUNTRY NOPRINT BY LOWEST CAR NOPRINT BY LOWEST MODEL NOPRINT ON CAR SUBFOOT "<ST.DEALER_COST<ST.RETAIL_COST<ST.SALES" ON TABLE SET PAGE-NUM NOLEAD ON TABLE NOTOTAL ON TABLE PCHOLD FORMAT HTML ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * INCLUDE = endeflt, $ TYPE=SUBFOOT, BY=2, HEADALIGN=BODY, $ TYPE=SUBFOOT, BY=2, LINE=1, OBJECT=FIELD, ITEM=1, COLSPAN=1, JUSTIFY=RIGHT, $ TYPE=SUBFOOT, BY=2, LINE=1, OBJECT=FIELD, ITEM=2, COLSPAN=1, JUSTIFY=RIGHT, $ TYPE=SUBFOOT, BY=2, LINE=1, OBJECT=FIELD, ITEM=3, COLSPAN=1, JUSTIFY=RIGHT, $ ENDSTYLE END
Frank | |
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7 |
SET HOLDLIST = PRINTONLY SET ASNAMES = ON -* TABLE FILE CAR SUM COMPUTE BLANK_COL/A1 = ''; AS '' DEALER_COST RETAIL_COST SALES BY LOWEST COUNTRY NOPRINT BY LOWEST CAR NOPRINT BY LOWEST MODEL NOPRINT ON CAR SUBFOOT "Total<ST.DEALER_COST<ST.RETAIL_COST<ST.SALES" ON TABLE SET PAGE-NUM NOLEAD ON TABLE NOTOTAL ON TABLE PCHOLD FORMAT HTML ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * INCLUDE = endeflt ,$ TYPE=SUBFOOT, HEADALIGN=BODY ,$ TYPE=SUBFOOT, OBJECT=FIELD, JUSTIFY=RIGHT ,$ ENDSTYLE ENDThis message has been edited. Last edited by: Dan Satchell,
-* A way to count rows; relies on terrible styling trick -* For subtotals, summarize, recaps -* you can't CNT. alpha data, but you can count fields containing numeric data -* You also can't show info about hidden fields in the sub-total/summarize/recap lines -* Usual workaround is to use a subfoot, and tally a hidden field, and then position items, however then you have to position them. -* "hiding" a column of numbers to count may allow for more versatile reporting. Note--this example shows doing so when your rows -* alternate background colors. If you don't alternate, you only need one line in the STYLE, and you do not need to use an IF with the INC field. TABLE FILE CAR SUM COMPUTE INC/I1=IF LAST INC EQ 1 THEN 0 ELSE 1; AS ' ' DEALER_COST RETAIL_COST SALES BY LOWEST COUNTRY NOPRINT BY LOWEST CAR BY LOWEST MODEL NOPRINT ON COUNTRY SUBTOTAL CNT. INC SUM. DEALER_COST RETAIL_COST SALES AS '' ON TABLE SET PAGE-NUM NOLEAD ON TABLE NOTOTAL ON TABLE PCHOLD FORMAT HTML ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * INCLUDE = endeflt, $ TYPE=DATA, COLUMN=INC, COLOR=RGB(235 235 240), WHEN=INC EQ 0,$ TYPE=DATA, COLUMN=INC, COLOR=WHITE, WHEN=INC NE 0,$ ENDSTYLE END
DEFINE FILE CAR DUMMY/A20=''; END TABLE FILE CAR SUM DEALER_COST RETAIL_COST SALES BY DUMMY AS '' BY LOWEST COUNTRY NOPRINT BY LOWEST CAR NOPRINT BY LOWEST MODEL NOPRINT ON CAR SUBFOOT "<+0 Total <ST.DEALER_COST<ST.RETAIL_COST<ST.SALES" ON TABLE SET PAGE-NUM NOLEAD ON TABLE NOTOTAL ON TABLE PCHOLD FORMAT HTML ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * INCLUDE = endeflt, $ TYPE=SUBFOOT, BY=3, HEADALIGN=BODY, $ TYPE=SUBFOOT, BY=3, LINE=1, OBJECT=FIELD, ITEM=1, COLSPAN=1, JUSTIFY=RIGHT, $ TYPE=SUBFOOT, BY=3, LINE=1, OBJECT=FIELD, ITEM=2, COLSPAN=1, JUSTIFY=RIGHT, $ TYPE=SUBFOOT, BY=3, LINE=1, OBJECT=FIELD, ITEM=3, COLSPAN=1, JUSTIFY=RIGHT, $ TYPE=SUBFOOT, BY=3, LINE=1, OBJECT=FIELD, ITEM=4, COLSPAN=1, JUSTIFY=RIGHT, $ ENDSTYLE END
quote:Syntax: How to Identify an Entire Total or Subtotal Row
TYPE=type, [BY=sortcolumn]
where:
type
Identifies a subtotal or total. Select from:
GRANDTOTAL which is a grand total (generated by COLUMN-TOTAL, SUBTOTAL, SUB-TOTAL, RECOMPUTE, or SUMMARIZE).
SUBTOTAL which is a subtotal (generated by SUBTOTAL, SUB-TOTAL, RECOMPUTE, or SUMMARIZE).
RECAP which is a subtotal calculation (generated by ON sortfield RECAP or ON sortfield COMPUTE).
BY
When there are several subtotal commands, each associated with a different vertical sort (BY) column, this enables you to identify which of the subtotal commands you wish to format.
sortcolumn
Specifies the vertical sort (BY) column associated with one of the several subtotal in the report commands. Use the field name to identify the sort column.
DEFINE FILE CAR SORT/I8=1; END TABLE FILE CAR SUM DEALER_COST RETAIL_COST BY COUNTRY BY CAR BY SORT BY MODEL ON TABLE HOLD AS DTLS END DEFINE FILE CAR SORT/I8=2; MODEL/A24='Total'; END TABLE FILE CAR SUM DEALER_COST RETAIL_COST BY COUNTRY BY CAR BY SORT BY MODEL ON TABLE HOLD AS SBTTL END TABLE FILE DTLS SUM DEALER_COST RETAIL_COST BY COUNTRY BY CAR BY SORT BY MODEL ON TABLE HOLD AS TGTHR MORE FILE SBTTL END TABLE FILE TGTHR SUM DEALER_COST RETAIL_COST BY COUNTRY BY CAR BY SORT BY MODEL END
-SET &SORTBY1_TITLE = &SB1; -SET &SB1 = DECODE &SB1 - ('CUST' 'D_CUSTNUM' - 'BOL' 'DM_CUSTOMER_INVOICE.DIM_BOL.BOLNUMBER' - 'LOC' 'DM_CUSTOMER_INVOICE.DIM_LOCATION.LOCATIONNAME' - 'XREF' 'DM_CUSTOMER_INVOICE.DIM_CUSTOMER.XREFNUM' - 'PART' 'DM_CUSTOMER_INVOICE.DIM_SHOP.PARTNUMBER' - 'CUSTPO' 'DM_CUSTOMER_INVOICE.DIM_SHOP.CUSTOMERPONUMBER' - 'SHOP' 'DM_CUSTOMER_INVOICE.DIM_SHOP.SHOPORDERNUMBER' - ELSE 'FOC_NONE'); -SET &ST1 = IF ('&ST1.EVAL' EQ 'Y' AND &SB1 NE FOC_NONE) - THEN ' ON ' | &SB1 | ' ' | 'SUBTOTAL DM_CUSTOMER_INVOICE.FACT_FG.WEIGHT ' | &SUBTOT_PAID | ' ' | &SUBTOT_INV | ' AS ' | '''' | '*TOTAL ' | &SORTBY1_TITLE | '''' - ELSE ''; -SET &SB1 = IF &SB1 NE FOC_NONE - THEN 'BY ' | &SB1 | ' AS ' | &SB1 | ' NOPRINT' - ELSE ''; -SET &SORTBY2_TITLE = &SB2; -SET &SB2 = DECODE &SB2 - ('CUST' 'D_CUSTNUM' - 'BOL' 'DM_CUSTOMER_INVOICE.DIM_BOL.BOLNUMBER' - 'LOC' 'DM_CUSTOMER_INVOICE.DIM_LOCATION.LOCATIONNAME' - 'XREF' 'DM_CUSTOMER_INVOICE.DIM_CUSTOMER.XREFNUM' - 'PART' 'DM_CUSTOMER_INVOICE.DIM_SHOP.PARTNUMBER' - 'CUSTPO' 'DM_CUSTOMER_INVOICE.DIM_SHOP.CUSTOMERPONUMBER' - 'SHOP' 'DM_CUSTOMER_INVOICE.DIM_SHOP.SHOPORDERNUMBER' - ELSE 'FOC_NONE'); -SET &ST2 = IF ('&ST2.EVAL' EQ 'Y' AND &SB2 NE FOC_NONE) - THEN 'ON ' | &SB2 | ' ' | 'SUBTOTAL DM_CUSTOMER_INVOICE.FACT_FG.WEIGHT ' | &SUBTOT_PAID | ' ' | &SUBTOT_INV | ' AS ' | '''' | '*TOTAL ' | &SORTBY2_TITLE | '''' - ELSE ''; -SET &SB2 = IF &SB2 NE FOC_NONE - THEN 'BY ' | &SB2 | ' NOPRINT' - ELSE ''; -SET &SORTBY3_TITLE = &SB3; -SET &SB3 = DECODE &SB3 - ('CUST' 'D_CUSTNUM' - 'BOL' 'DM_CUSTOMER_INVOICE.DIM_BOL.BOLNUMBER' - 'LOC' 'DM_CUSTOMER_INVOICE.DIM_LOCATION.LOCATIONNAME' - 'XREF' 'DM_CUSTOMER_INVOICE.DIM_CUSTOMER.XREFNUM' - 'PART' 'DM_CUSTOMER_INVOICE.DIM_SHOP.PARTNUMBER' - 'CUSTPO' 'DM_CUSTOMER_INVOICE.DIM_SHOP.CUSTOMERPONUMBER' - 'SHOP' 'DM_CUSTOMER_INVOICE.DIM_SHOP.SHOPORDERNUMBER' - ELSE 'FOC_NONE'); -SET &ST3 = IF ('&ST3.EVAL' EQ 'Y' AND &SB3 NE FOC_NONE) - THEN 'ON ' | &SB3 | ' ' | 'SUBTOTAL DM_CUSTOMER_INVOICE.FACT_FG.WEIGHT ' | &SUBTOT_PAID | ' ' | &SUBTOT_INV | ' AS ' | '''' | '*TOTAL ' | &SORTBY3_TITLE | '''' - ELSE ''; -SET &SB3 = IF &SB3 NE FOC_NONE - THEN 'BY ' | &SB3 | ' NOPRINT' - ELSE ''; -TYPE &ST3 -TYPE &SB3 -SET &SORTBY4_TITLE = &SB4; -SET &SB4 = DECODE &SB4 - ('CUST' 'D_CUSTNUM' - 'BOL' 'DM_CUSTOMER_INVOICE.DIM_BOL.BOLNUMBER' - 'LOC' 'DM_CUSTOMER_INVOICE.DIM_LOCATION.LOCATIONNAME' - 'XREF' 'DM_CUSTOMER_INVOICE.DIM_CUSTOMER.XREFNUM' - 'PART' 'DM_CUSTOMER_INVOICE.DIM_SHOP.PARTNUMBER' - 'CUSTPO' 'DM_CUSTOMER_INVOICE.DIM_SHOP.CUSTOMERPONUMBER' - 'SHOP' 'DM_CUSTOMER_INVOICE.DIM_SHOP.SHOPORDERNUMBER' - ELSE 'FOC_NONE'); -SET &ST4 = IF ('&ST4.EVAL' EQ 'Y' AND &SB4 NE FOC_NONE) - THEN 'ON ' | &SB4 | ' ' | 'SUBTOTAL DM_CUSTOMER_INVOICE.FACT_FG.WEIGHT ' | &SUBTOT_PAID | ' ' | &SUBTOT_INV | ' AS ' | '''' | '*TOTAL ' | &SORTBY4_TITLE | '''' - ELSE ''; -SET &SB4 = IF &SB4 NE FOC_NONE - THEN 'BY ' | &SB4 | ' NOPRINT' - ELSE ''; -TYPE &SB4 -SET &SORTBY5_TITLE = &SB5; -TYPE &SB5 -SET &SB5 = DECODE &SB5 - ('CUST' 'D_CUSTNUM' - 'BOL' 'DM_CUSTOMER_INVOICE.DIM_BOL.BOLNUMBER' - 'LOC' 'DM_CUSTOMER_INVOICE.DIM_LOCATION.LOCATIONNAME' - 'XREF' 'DM_CUSTOMER_INVOICE.DIM_CUSTOMER.XREFNUM' - 'PART' 'DM_CUSTOMER_INVOICE.DIM_SHOP.PARTNUMBER' - 'CUSTPO' 'DM_CUSTOMER_INVOICE.DIM_SHOP.CUSTOMERPONUMBER' - 'SHOP' 'DM_CUSTOMER_INVOICE.DIM_SHOP.SHOPORDERNUMBER' - ELSE 'FOC_NONE'); -SET &ST5 = IF ('&ST5.EVAL' EQ 'Y' AND &SB5 NE FOC_NONE) - THEN 'ON ' | &SB5 | ' ' | 'SUBTOTAL DM_CUSTOMER_INVOICE.FACT_FG.WEIGHT ' | &SUBTOT_PAID | ' ' | &SUBTOT_INV | ' AS ' | '''' | '*TOTAL ' | &SORTBY5_TITLE | '''' - ELSE ''; -SET &SB5 = IF &SB5 NE FOC_NONE - THEN 'BY ' | &SB5 | ' NOPRINT' - ELSE ''; DEFINE FILE DM_CUSTOMER_INVOICE -MRNOEDIT -INCLUDE common_report_export_icons.fex DISPALL/A500 = TRIM('T', '&HD_ALL', &HD_ALL.LENGTH , '; ', 2, DISPALL); DISPCUST/A4100 = '' || '&HD_CUST' || ''; DISPXREF/A4100 = '' || '&HD_XREF' || ''; DISPPO/A200 = '&HD_CUSTPO'; DISPPART/A20 = '&HD_PART'; DISPBOL/A200 = '&HD_BOL'; DISPLOC/A4100 = '&HD_LOC'; D_LOCNUM/A2 = TRIM('L', EDIT(DM_CUSTOMER_INVOICE.DIM_LOCATION.LOCATIONNUMBER, '$$99'), 2, '0', 1, D_LOCNUM); D_LOCNAME/A200 = TRIM('L', DM_CUSTOMER_INVOICE.DIM_LOCATION.LOCATIONNAME, 200, ' ', 1, D_LOCNAME); D_LOCNAMENUM/A500 = D_LOCNAME || '-' || D_LOCNUM; D_CUSTNUM/A7 = TRIM('L', EDIT(DM_CUSTOMER_INVOICE.DIM_CUSTOMER.CUSTOMERNUMBER), 7, '0', 1, D_CUSTNUM); D_PRODNUM/A6 = TRIM('L', EDIT(DM_CUSTOMER_INVOICE.DIM_PRODUCTION.PRODUCTIONNUMBER), 6, '0', 1, D_PRODNUM); D_SHOPNUM/A6 = TRIM('L', EDIT(DM_CUSTOMER_INVOICE.DIM_SHOP.SHOPORDERNUMBER), 6, '0', 1, D_SHOPNUM); D_GAUGERANGE/A15 = PTOA(DM_CUSTOMER_INVOICE.DIM_SHOP.ACTUALGUAGE, '(P7.4)', 'A7') || '/' || PTOA (DM_CUSTOMER_INVOICE.DIM_SHOP.RANGE, '(P7.4)', 'A7'); D_ACTUALWIDTH/A9 = PTOA(DM_CUSTOMER_INVOICE.DIM_SHOP.ACTUALWIDTH, '(P9.4)', 'A9'); D_INVOICENUMBER/A8 = TRIM('L', EDIT(DM_CUSTOMER_INVOICE.FACT_INVOICE.INVOICENUMBER), 8, '0', 1, D_INVOICENUMBER); D_INVOICEAMOUNT/A13 = PTOA(DM_CUSTOMER_INVOICE.FACT_INVOICE.INVOICEAMOUNT_FUNC, '(P13.2)', 'A13'); D_FREIGHT/A9 = PTOA(DM_CUSTOMER_INVOICE.FACT_INVOICE.FREIGHTAMOUNT, '(P9.2)', 'A9'); D_PAIDAMOUNT/A11 = PTOA(DM_CUSTOMER_INVOICE.FACT_INVOICE.PAIDAMOUNT_FUNC, '(P11.2)', 'A11'); D_PRICECWEIGHT/A12 = PTOA(DM_CUSTOMER_INVOICE.FACT_INVOICE.INVOICEAMOUNT_FUNC/DM_CUSTOMER_INVOICE.FACT_INVOICE.SUMFGWGT*100, '(P11.2)', 'A11'); D_SURCHARGE/A13 = PTOA(DM_CUSTOMER_INVOICE.FACT_INVOICE.SURCHARGEAMT, '(P11.2)', 'A13'); D_SALESCLASS/A2 = TRIM('T', PTOA(DM_CUSTOMER_INVOICE.DIM_SALES_CLASS.SALESCLASSIFICATION, '(P2.0)', 'A2'), 2, '.', 1, D_SALESCLASS); D_XREFNUM/A7 = TRIM('T', PTOA(DM_CUSTOMER_INVOICE.DIM_CUSTOMER.XREFNUM, '(P7.0)', 'A7'), 7, '.', 1, D_XREFNUM); D_HST/A13 = PTOA(DM_CUSTOMER_INVOICE.FACT_INVOICE.CADTAX_FUNC, '(P11.2)', 'A13'); D_MATERIALCOST/A13 = PTOA(DM_CUSTOMER_INVOICE.FACT_INVOICE.MATERIALCOST, '(P11.2C)', 'A13'); D_LENGTH/A16 = PTOA(DM_CUSTOMER_INVOICE.FACT_INVOICE.LENGTH, '(P14.2)', 'A16'); BLANK/A200 =' '; END TABLE FILE DM_CUSTOMER_INVOICE HEADING "Steel Technologies " "Customer Invoice &HD_VIEW Sales Report" "&HD_RUNDT.EVAL" "_____________________________________" "For &HD_INVSDT - &HD_INVEDT" &CMNT_ALL "Selected ALL for: <+0><DISPALL" &CMNT_CUST "Customer(s): <+0><DISPCUST" &CMNT_XREF "Master X-Ref(s): <+0><DISPXREF" &CMNT_LOC "Location(s): <+0><DISPLOC" &CMNT_CUSTPO "PO: <+0><DISPPO" &CMNT_PART "Part: <+0><DISPPART" &CMNT_BOL "BOL: <+0><DISPBOL" SUM COMPUTE C_COUNTER/I4 = IF D_INVOICENUMBER NE LAST D_INVOICENUMBER THEN 0 ELSE 1; NOPRINT D_CUSTNUM AS 'Cust#' D_LOCNAMENUM AS 'Loc#' DM_CUSTOMER_INVOICE.DIM_CUSTOMER.SHIPTOCUSTNAME AS 'Ship to Cust Name' DM_CUSTOMER_INVOICE.DIM_CUSTOMER.SOLDTOCUSTNAME AS 'Sold To Name' &NUCORHIDE DM_CUSTOMER_INVOICE.DIM_SHOP.CUSTOMERPONUMBER AS 'CustPO#' D_PRODNUM AS 'Prod#' &SUMMARYSHOW D_SHOPNUM AS 'Shop#' DM_CUSTOMER_INVOICE.DIM_MATERIAL_TYPE.MATERIALTYPE AS 'Matl Type' DM_CUSTOMER_INVOICE.DIM_SHOP.PARTNUMBER AS 'Part#' D_GAUGERANGE AS 'GAUGE/RANGE' &NUCORHIDE D_ACTUALWIDTH AS 'Width' &NUCORHIDE D_LENGTH AS 'Length' &SUMMARYSHOW COMPUTE C_LENGTH/P14.2 = IF C_COUNTER EQ 0 THEN DM_CUSTOMER_INVOICE.FACT_INVOICE.LENGTH ELSE 0; AS 'Length' &DETAILSHOW DM_CUSTOMER_INVOICE.DIM_PRODSHOP.MACHINEPROCESSORDER AS 'Machine Codes' &NUCORSHOW DM_CUSTOMER_INVOICE.DIM_MILLCOIL.MILLCOILNUMBER AS 'Coil#' &SUMMARYHIDE DM_CUSTOMER_INVOICE.DIM_FG_TAG.SERIALNUMBER AS 'Tag#' &SUMMARYHIDE DM_CUSTOMER_INVOICE.FACT_FG.WEIGHT/D15 AS 'Wgt' DM_CUSTOMER_INVOICE.DIM_SHIPPEDDATE.FULL_DATE/MDYY AS 'Shp Date' DM_CUSTOMER_INVOICE.DIM_INVOICEDATE.FULL_DATE/MDYY AS 'Inv Date' DM_CUSTOMER_INVOICE.DIM_BOL.BOLNUMBER AS 'BOL#' D_INVOICENUMBER AS 'Invc#' D_MATERIALCOST AS 'Material Cost' &SUMMARYSHOW COMPUTE C_MATERIALCOST/P11.2C = IF C_COUNTER EQ 0 THEN DM_CUSTOMER_INVOICE.FACT_INVOICE.MATERIALCOST ELSE 0; AS 'Material Cost' &DETAILSHOW COMPUTE C_INVAMT/D15.2 = IF C_COUNTER EQ 0 THEN DM_CUSTOMER_INVOICE.FACT_INVOICE.INVOICEAMOUNT_FUNC ELSE 0; AS 'Inv Amt' &SUMMARYHIDE COMPUTE C_INVAMTSUMMARY/D15.2 = EDIT(D_INVOICEAMOUNT); AS 'Inv Amt' &SUMMARYSHOW D_PRICECWEIGHT AS 'Price (CWT)' &SUMMARYSHOW COMPUTE C_PRICECWEIGHT/D11.2 = IF C_COUNTER EQ 0 THEN DM_CUSTOMER_INVOICE.FACT_INVOICE.INVOICEAMOUNT_FUNC/ DM_CUSTOMER_INVOICE.FACT_INVOICE.SUMFGWGT*100 ELSE 0; AS 'Price (CWT)' &SUMMARYHIDE COMPUTE C_FREIGHT/P9.2 = IF C_COUNTER EQ 0 THEN DM_CUSTOMER_INVOICE.FACT_INVOICE.FREIGHTAMOUNT ELSE 0; AS 'Frght' &SUMMARYHIDE D_FREIGHT AS 'Frght' &SUMMARYSHOW DM_CUSTOMER_INVOICE.FACT_FG.SCRAPWEIGHT AS 'Scrap Weight' &NUCORSHOW COMPUTE C_TOTALSCRAP/P11.2 = IF C_COUNTER EQ 0 THEN DM_CUSTOMER_INVOICE.FACT_INVOICE.TOTALSCRAP ELSE 0; AS 'Scrap Credit Amt' &NUCORSHOW DM_CUSTOMER_INVOICE.FACT_INVOICE.SURCHARGEAMT AS 'Surcharge' &DETAILSHOW D_SURCHARGE AS 'Surcharge' &SUMMARYSHOW DM_CUSTOMER_INVOICE.FACT_FG.PIECES/D9C AS 'Pieces' &NUCORHIDE COMPUTE C_PAIDAMOUNT/P11C = IF C_COUNTER EQ 0 THEN DM_CUSTOMER_INVOICE.FACT_INVOICE.PAIDAMOUNT_FUNC ELSE 0; AS 'Paid Amt' &SUMMARYHIDE COMPUTE C_PAIDAMTSUMMARY/D11.2C = EDIT(D_PAIDAMOUNT); AS 'Paid Amt' &SUMMARYSHOW COMPUTE C_PAYMENTDATE/A8MDYY = DM_CUSTOMER_INVOICE.DIM_PAIDDATE.FULL_DATE; NOPRINT COMPUTE C_PAYMENTDATEALPHA/A10 = EDIT(C_PAYMENTDATE, '99/99/9999'); NOPRINT COMPUTE C_PAYMENTDATEFINAL/A10 = IF C_PAYMENTDATEALPHA EQ ' / / ' THEN '##/##/####' ELSE C_PAYMENTDATEALPHA; AS 'Paid Date'; COMPUTE C_PAYMENTSTATUS/A3 = IF DM_CUSTOMER_INVOICE.FACT_INVOICE.PAYMENTSTATUS EQ MISSING OR DM_CUSTOMER_INVOICE.FACT_INVOICE.PAYMENTSTATUS EQ 'P' OR 'Z' THEN 'No' ELSE 'Yes'; AS 'Opn' COMPUTE C_HST/P11.2 = IF C_COUNTER EQ 0 THEN DM_CUSTOMER_INVOICE.FACT_INVOICE.CADTAX_FUNC ELSE 0; AS 'HST' &DETAILSHOW D_HST AS 'HST' &SUMMARYSHOW D_XREFNUM AS 'XREFNUM' &NUCORHIDE D_SALESCLASS AS 'Sales Class' &NUCORHIDE &SB1 &SB2 &SB3 &SB4 &SB5 BY DM_CUSTOMER_INVOICE.DIM_LOCATION.LOCATIONNUMBER NOPRINT BY DM_CUSTOMER_INVOICE.FACT_INVOICE.INVOICENUMBER NOPRINT &SUMMARYHIDECMT BY DM_CUSTOMER_INVOICE.DIM_FG_TAG.SERIALNUMBER NOPRINT -*END BY COMMANDS -*START ON SUBTOTAL COMMANDS &ST1 &ST2 &ST3 &ST4 &ST5 -*END ON SUBTOTAL COMMANDS -*START WHERE STATEMENTS WHERE DM_CUSTOMER_INVOICE.FACT_INVOICE.INVOICEAMOUNT_TRAN NE 0 &CUST_WHERE &XREF_WHERE &LOC_WHERE -*&CURR_WHERE &CUSTPO_WHERE &PART_WHERE &BOL_WHERE &INVSDT_WHERE &INVEDT_WHERE -*END WHERE STATEMENTS ON TABLE PCHOLD FORMAT &WFFMT -*ON TABLE NOTOTAL ON TABLE COLUMN-TOTAL ON TABLE SET PAGE-NUM OFF ON TABLE SET BYDISPLAY &BYDISPLAY ON TABLE SET LINES &RPTLINES ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * -MRNOEDIT -INCLUDE steeltech.sty PAGESIZE=LEGAL, ORIENTATION=LANDSCAPE, $ TYPE=REPORT,COLUMN=DM_CUSTOMER_INVOICE.DIM_SHOP.PARTNUMBER,WIDTH=4,BACKCOLOR=YELLOW,$ -IF &WFFMT NE 'HTML' THEN GOTO SKIPEXPORTOPTIONS ; TYPE=HEADING, LINE=3, OBJECT=FIELD, ITEM=1, SIZE=8, POSITION=3.0, TARGET='_blank', FOCEXEC=&FEXNAME( \ WFFMT='PDF' \ &LNK_CUSTTYP &LNK_CUST &LNK_XREF &LNK_INVSDT &LNK_INVEDT &LNK_LOC &LNK_CUSTPO &LNK_PART &LNK_BOL &LNK_SORTBY1 &LNK_SORTBY2 &LNK_SORTBY3 &LNK_SORTBY4 &LNK_SORTBY5 &LNK_SUBON1 &LNK_SUBON2 &LNK_SUBON3 &LNK_SUBON4 &LNK_SUBON5 &LNK_VIEW &LNK_HHMMSS ), $ TYPE=HEADING, LINE=3, OBJECT=FIELD, ITEM=2, SIZE=8, POSITION=0.1, TARGET='_blank', FOCEXEC=&FEXNAME( \ WFFMT='EXL2K' &LNK_CUSTTYP &LNK_CUST &LNK_XREF &LNK_INVSDT &LNK_INVEDT &LNK_LOC &LNK_CUSTPO &LNK_PART &LNK_BOL &LNK_SORTBY1 &LNK_SORTBY2 &LNK_SORTBY3 &LNK_SORTBY4 &LNK_SORTBY5 &LNK_SUBON1 &LNK_SUBON2 &LNK_SUBON3 &LNK_SUBON4 &LNK_SUBON5 &LNK_VIEW &LNK_HHMMSS ), $ TYPE=HEADING, LINE=3, OBJECT=FIELD, ITEM=3, SIZE=8, POSITION=0.1, TARGET='_blank', FOCEXEC=&FEXNAME( \ WFFMT='PPT' &LNK_CUSTTYP &LNK_CUST &LNK_XREF &LNK_INVSDT &LNK_INVEDT &LNK_LOC &LNK_CUSTPO &LNK_PART &LNK_BOL &LNK_SORTBY1 &LNK_SORTBY2 &LNK_SORTBY3 &LNK_SORTBY4 &LNK_SORTBY5 &LNK_SUBON1 &LNK_SUBON2 &LNK_SUBON3 &LNK_SUBON4 &LNK_SUBON5 &LNK_VIEW &LNK_HHMMSS ), $ TYPE=HEADING, LINE=3, OBJECT=FIELD, ITEM=4, SIZE=8, POSITION=0.1, TARGET='_blank', FOCEXEC=&FEXNAME( \ WFFMT='AHTML' &LNK_CUSTTYP &LNK_CUST &LNK_XREF &LNK_INVSDT &LNK_INVEDT &LNK_LOC &LNK_CUSTPO &LNK_PART &LNK_BOL &LNK_SORTBY1 &LNK_SORTBY2 &LNK_SORTBY3 &LNK_SORTBY4 &LNK_SORTBY5 &LNK_SUBON1 &LNK_SUBON2 &LNK_SUBON3 &LNK_SUBON4 &LNK_SUBON5 &LNK_VIEW &LNK_HHMMSS ), $ -SKIPEXPORTOPTIONS ENDSTYLE END
quote:Output formats such as HTML and EXCEL, by their very nature, have dynamic cell widths and therefore the output from FOCUS will put the SUBTOTAL values on the SAME line as the "TOTAL" label. This is because HTML and EXCEL can adjust for the width of the "*TOTAL ..." label length.
Output formats such as HTML STYLEMODE FIXED or PDF determine their positioning by character length and do not contain CELLS to place values in, as do HTML and EXCEL. Therefore you experience the splitting of the SUBTOTAL line into two lines.
Try the following code. The length of the field COUNTRY is a maximum of 10 characters and the length of the "*TOTAL " is 7. I created a field called COUNTRYL with a length of A17 and then sorted by it. This created enough space in the COUNTRY sort column so that the "*TOTAL ..." could fit in that column and therefore the SUBTOTAL fits on one line.
TABLE FILE CAR
SUM RCOST DCOST SALES
COMPUTE COUNTRYL/A17=COUNTRY; NOPRINT
BY COUNTRY NOPRINT SUBTOTAL
BY COUNTRYL
ON TABLE SET STYLEMODE FIXED
END
The key here is that the BY fields THAT ARE VISIBLE determine how much space there is to put the "*TOTAL ..." label. Try the following and you will see that even though the output is HTML, the SUBTOTAL is split into two lines. This is because there are no VISIBLE sort field columns on the report.
TABLE FILE CAR
SUM RCOST DCOST SALES
BY COUNTRY NOPRINT SUBTOTAL
END
Add the BY CAR sort and it is back to one line because there is a VISIBLE sort field column to put the "*TOTAL ..." label in.
TABLE FILE CAR
SUM RCOST DCOST SALES
BY COUNTRY NOPRINT SUBTOTAL
BY CAR
END
-SET &SB1 = DECODE &SB1 - ('CUST' 'D_CUSTNUM/A100' - 'BOL' 'DM_CUSTOMER_INVOICE.DIM_BOL.BOLNUMBER/A100' - 'LOC' 'DM_CUSTOMER_INVOICE.DIM_LOCATION.LOCATIONNAME/A100' - 'XREF' 'DM_CUSTOMER_INVOICE.DIM_CUSTOMER.XREFNUM/A100' - 'PART' 'DM_CUSTOMER_INVOICE.DIM_SHOP.PARTNUMBER/A100' - 'CUSTPO' 'DM_CUSTOMER_INVOICE.DIM_SHOP.CUSTOMERPONUMBER/A100' - 'SHOP' 'DM_CUSTOMER_INVOICE.DIM_SHOP.SHOPORDERNUMBER/A100' - ELSE 'FOC_NONE');
-SET &SB1 = IF &SB1 NE FOC_NONE - THEN 'BY ' | &SB1 | ' AS ' | &SB1 | ' NOPRINT'