I have a multiverb request which I've used custom field placement to reposition the fields in webfocus 713.

Some of the subtotals are under the wrong columns. How do I fix this?

Thanks,

John

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

John,

Can you duplicate the issue with the CAR file? Post the code if you are able to do this. This iwll help a lot to understand the issue and help us to work out a possible solution.

If you are using subfoots to accomplish your custom placement, is it possible you are using a variable width font and it's causing issues. I remember the pain it was to align custom subtotals in the old days on the mainframe.

Here's the code guys. Thanks for your help.


-DEFAULT &ORDSTATUSTYPEDESC = 'OR';
-SET &FLAG = IF &ORDSTATUSTYPEDESC CONTAINS 'OR' THEN 'YES' ELSE 'NO';
-*
-DEFAULT &BEGIN_DATE_1 = 20070301;
-DEFAULT &END_DATE_1 = 20070326;
-*
-SET &BEGIN_DATE_2 = DATETRAN (DATECVT (&BEGIN_DATE_1, 'I8YYMD', 'MDYY'), '(MDYY)', '(trdD)', 'EN', 18, 'A18') ;
-SET &END_DATE_2 = DATETRAN (DATECVT (&END_DATE_1, 'I8YYMD', 'MDYY'), '(MDYY)', '(trdD)', 'EN', 18, 'A18') ;
-*
-*
-*
DEFINE FILE ABC
QUOTE_CNT/I7C=IF AIQUOTENUMBER EQ ' ' OR '0' THEN 0 ELSE 1;
PO_CNT/I7C=IF PONUMBER EQ ' ' OR '0' THEN 0 ELSE 1;
CREATEDDATE_2/MDYY = CREATEDDATE;
-*
DATEOUT_1/A16 = HCNVRT(DATEOUT, '(HYYMDI)', 16, DATEOUT_1);
DATEOUT_2/A10 =
IF EDIT (DATEOUT_1,'9999$$$$$$$$$$$$') EQ '1900'
THEN ' '
ELSE EDIT (DATEOUT_1,'$$$$$99$$$$$$$$$') | '/' |
EDIT (DATEOUT_1,'$$$$$$$$99$$$$$$') | '/' |
EDIT (DATEOUT_1,'9999$$$$$$$$$$$$');
-*
FOLLOWUPDATE_1/A16 = HCNVRT(FOLLOWUPDATE, '(HYYMDI)', 16, FOLLOWUPDATE_1);
FOLLOWUPDATE_2/A10 =
IF EDIT (FOLLOWUPDATE_1,'9999$$$$$$$$$$$$') EQ '1900'
THEN ' '
ELSE EDIT (FOLLOWUPDATE_1,'$$$$$99$$$$$$$$$') | '/' |
EDIT (FOLLOWUPDATE_1,'$$$$$$$$99$$$$$$') | '/' |
EDIT (FOLLOWUPDATE_1,'9999$$$$$$$$$$$$');
-*
AIQUOTENUMBER_2/A50 = IF AIQUOTENUMBER EQ '0' THEN ' ' ELSE AIQUOTENUMBER;
AS400AIQUOTENUMBER_2/A10 = SUBSTR(50, AS400AIQUOTENUMBER, 1, 10, 10, 'A10');
AIQUOTENUMBER_2/A50=IF AIQUOTENUMBER EQ '0' THEN ' ' ELSE AIQUOTENUMBER;
-*
COORDINATOR_LAST_NAME_1/A47=GETTOK(COORDINATORNAME, 50, 1, ',', 47, 'A47');
COORDINATOR_LAST_NAME_2/A48=TRIM('B', COORDINATOR_LAST_NAME_1, 50, ' ', 1, 'A47');
-*
COORDINATOR_FIRST_NAME_1/A47=GETTOK(COORDINATORNAME, 50, 2, ',', 47, 'A47');
COORDINATOR_FIRST_NAME_2/A48=TRIM('B', COORDINATOR_FIRST_NAME_1, 50, ' ', 1, 'A47') ;
-*
COORDINATOR_NAME_2/A196=COORDINATOR_FIRST_NAME_2 || (' ' | COORDINATOR_LAST_NAME_2);
-*
-* DOLLARVALUE_2/P14.2M = DOLLARVALUE;
-* AS400LINEITEMSUBTOTAL_2/P14.2M = AS400LINEITEMSUBTOTAL;
END
-*
-*
-*
TABLE FILE ABC
SUM
QUOTE_CNT NOPRINT AS 'Quote,Count'
PO_CNT NOPRINT AS 'PO,Count'
BY COORDINATOR_NAME_2 AS 'Coordinator,Name'
SUM
COMPUTE RECORD_COUNTER/I6 = IF CREATEDDATE_2 NE LAST CREATEDDATE_2 THEN 1 ELSE 0; NOPRINT
COMPUTE CANCELLED_RECORD_COUNTER/I6 = IF ( ORDSTATUSTYPEDESC EQ 'Cancelled' ) AND ( CREATEDDATE_2 NE LAST CREATEDDATE_2 ) THEN 1 ELSE 0; NOPRINT
AS 'CANCELLED,RECORD,COUNTER'
COMPUTE COMPLETED_RECORD_COUNTER/I6 = IF ( ORDSTATUSTYPEDESC EQ 'Complete' ) AND ( CREATEDDATE_2 NE LAST CREATEDDATE_2 ) THEN 1 ELSE 0; NOPRINT
AS 'COMPLETED,RECORD,COUNTER'
COMPUTE PENDING_RECORD_COUNTER/I6 = IF ( ORDSTATUSTYPEDESC EQ 'Pending' ) AND ( CREATEDDATE_2 NE LAST CREATEDDATE_2 ) THEN 1 ELSE 0; NOPRINT
AS 'PENDING,RECORD,COUNTER'
DATEOUT_2 NOPRINT AS 'Date,Out'
FOLLOWUPDATE_2 NOPRINT AS 'Followup,Date'
SALESREP_NAME NOPRINT AS 'Sales Rep Name'
AIQUOTENUMBER_2 NOPRINT AS 'AI,Quote,Number'
MAJORDESC NOPRINT AS 'Major,Code'
CUSTOMERNAME NOPRINT AS 'Customer Name'
CUSTOMERCONTACT NOPRINT AS 'Customer Contact'
TITLE NOPRINT AS 'Title'
COMMENTS NOPRINT AS ' Comments'
COMPUTE COUNTER/I11 = COUNTER + 1;
CNT.SPECIALORDERQUOTELINEID AS 'Actual,Item,Count'
ITEMDESCRIPTION AS 'Item,Description'
BY COORDINATOR_NAME_2 AS 'Coordinator,Name'
BY ORDSTATUSTYPEDESC AS 'Status'
BY CREATEDDATE_2 AS 'Created,Date'
BY SALESREP_NAME AS 'Sales Rep Name'
BY AIQUOTENUMBER_2 AS 'AI,Quote,Number'
BY CUSTOMERNAME AS 'Customer Name'
BY MAJORDESC AS 'Major,Code'
BY CUSTOMERCONTACT AS 'Customer,Contact'
BY TITLE AS 'Title'
BY COMMENTS AS 'Comments'
BY ITEMCOUNT AS 'Quote,Item,Count'
BY PONUMBER AS 'PO #'
PRINT
DOLLARVALUE/P14.2M AS 'Dollar,Value'
QUANTITY/I7 AS 'Quantity'
MARGIN/P7.2M NOPRINT AS 'Margin'
ISPURCHASED AS 'Items,Purchased'
AS400AIQUOTENUMBER AS 'AS400,AI,QUOTE,NUMBER'
AS400ITEMNAME AS 'AS400,ITEM,NAME'
AS400ORDERNUMBER AS 'AS400,ORDER,NUMBER'
AS400PONUMBER AS 'AS400,PO,NUMBER'
AS400LINEITEMSUBTOTAL/P14.2M AS 'AS400,DOLLAR,VALUE'
QUOTE_CNT AS 'Quote,Count'
PO_CNT AS 'PO,Count'
ITEMCOUNT NOPRINT AS 'Quote,Item,Count'
BY COORDINATOR_NAME_2 AS 'Coordinator,Name'
BY ORDSTATUSTYPEDESC AS 'Status'
BY CREATEDDATE_2 AS 'Created,Date'
BY SALESREP_NAME AS 'Sales Rep Name'
BY AIQUOTENUMBER_2 AS 'AI,Quote,Number'
BY CUSTOMERNAME AS 'Customer Name'
BY MAJORDESC AS 'Major,Code'
BY CUSTOMERCONTACT AS 'Customer,Contact'
BY TITLE AS 'Title'
BY COMMENTS AS 'Comments'
BY ITEMCOUNT AS 'Quote,Item,Count'
BY PONUMBER AS 'PO #'
ON COORDINATOR_NAME_2 SUBFOOT
"Totals for <COORDINATOR_NAME_2 "
"Pending count: <ST.PENDING_RECORD_COUNTER "
"Cancelled count: <ST.CANCELLED_RECORD_COUNTER "
"Completed count: <ST.COMPLETED_RECORD_COUNTER "
" "
WHEN '&FLAG' EQ 'YES';
ON ORDSTATUSTYPEDESC SUBTOTAL
ITEMCOUNT
SPECIALORDERQUOTELINEID
DOLLARVALUE
QUANTITY
ISPURCHASED
AS400ORDERNUMBER
AS400LINEITEMSUBTOTAL AS 'Total '
ON ORDSTATUSTYPEDESC SUBFOOT
"Total count of <ORDSTATUSTYPEDESC records for <COORDINATOR_NAME_2 <ST.RECORD_COUNTER "
" "
" "
HEADING
"Special Order Quote Log"
"From &BEGIN_DATE_2 to &END_DATE_2"
"For <COORDINATOR_NAME_2 "
"Run Date: <+0>&DATEtMDYY <+0> "
" "
WHERE CREATEDDATE FROM &BEGIN_DATE_1 TO &END_DATE_1;
WHERE COORDINATORNAME EQ &COORDINATORNAME.(OR(FIND COORDINATORNAME IN SPECIALORDER_DATA)).COORDINATORNAME.;
WHERE ORDSTATUSTYPEDESC EQ &ORDSTATUSTYPEDESC.(OR(<Cancelled,Cancelled>,<Complete,Complete>,<Pending,Pending>)).ORDSTATUSTYPEDESC.;
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K FORMULA
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='TIMES NEW ROMAN',
SIZE=10,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
LINES-PER-PAGE=20,
$
TYPE=DATA,
COLUMN=N30,
COLOR='BLACK',
STYLE=NORMAL,
$
TYPE=DATA,
COLUMN=N43,
STYLE=BOLD,
$
TYPE=TITLE,
COLUMN=N30,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N43,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N14,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N13,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N12,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N8,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N9,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N10,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N11,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N6,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N7,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N28,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N32,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N3,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N4,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N5,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N21,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N23,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N25,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N26,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N27,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N29,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N31,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N15,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N16,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N17,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N18,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N19,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N20,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N34,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N35,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N36,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N38,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N37,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N39,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N1,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N2,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N22,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N24,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N44,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N33,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N45,
STYLE=BOLD,
$
TYPE=TITLE,
COLUMN=N42,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N40,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N41,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=SUBHEAD,
BY=2,
LINE=2,
OBJECT=TEXT,
ITEM=1,
SIZE=12,
STYLE=BOLD,
$
TYPE=SUBHEAD,
BY=2,
LINE=2,
OBJECT=FIELD,
ITEM=1,
SIZE=12,
STYLE=BOLD,
$
TYPE=REPORT,
COLUMN=N28,
SEQUENCE=25,
$
TYPE=REPORT,
COLUMN=N30,
WRAP=1.500000,
SEQUENCE=15,
$
TYPE=REPORT,
COLUMN=N43,
WRAP=0.750000,
$
TYPE=REPORT,
COLUMN=N14,
SQUEEZE=1.430556,
SEQUENCE=17,
$
TYPE=REPORT,
COLUMN=N13,
SQUEEZE=1.180556,
SEQUENCE=13,
$
TYPE=REPORT,
COLUMN=N12,
WRAP=1.777778,
SEQUENCE=12,
$
TYPE=REPORT,
COLUMN=N8,
WRAP=2.777778,
SEQUENCE=9,
$
TYPE=REPORT,
COLUMN=N9,
SEQUENCE=8,
$
TYPE=REPORT,
COLUMN=N10,
WRAP=1.666667,
SEQUENCE=10,
$
TYPE=REPORT,
COLUMN=N11,
WRAP=1.402778,
SEQUENCE=11,
$
TYPE=REPORT,
COLUMN=N6,
SEQUENCE=6,
$
TYPE=REPORT,
COLUMN=N7,
SQUEEZE=1.402778,
SEQUENCE=7,
$
TYPE=REPORT,
COLUMN=N32,
SEQUENCE=16,
$
TYPE=REPORT,
COLUMN=N3,
WRAP=0.680556,
$
TYPE=REPORT,
COLUMN=N4,
WRAP=0.680556,
SEQUENCE=4,
$
TYPE=REPORT,
COLUMN=N5,
WRAP=0.680556,
SEQUENCE=5,
$
TYPE=REPORT,
COLUMN=N21,
WRAP=1.500000,
$
TYPE=REPORT,
COLUMN=N25,
WRAP=1.500000,
$
TYPE=REPORT,
COLUMN=N26,
WRAP=1.500000,
$
TYPE=REPORT,
COLUMN=N27,
WRAP=3.500000,
$
TYPE=REPORT,
COLUMN=N29,
SEQUENCE=14,
$
TYPE=REPORT,
COLUMN=N31,
WRAP=1.500000,
SEQUENCE=19,
$
TYPE=REPORT,
COLUMN=N15,
WRAP=1.500000,
$
TYPE=REPORT,
COLUMN=N16,
WRAP=0.680556,
$
TYPE=REPORT,
COLUMN=N17,
WRAP=1.500000,
$
TYPE=REPORT,
COLUMN=N18,
WRAP=1.500000,
$
TYPE=REPORT,
COLUMN=N19,
WRAP=0.680556,
$
TYPE=REPORT,
COLUMN=N20,
WRAP=0.680556,
$
TYPE=REPORT,
COLUMN=N34,
WRAP=0.750000,
SEQUENCE=18,
$
TYPE=REPORT,
COLUMN=N35,
WRAP=1.000000,
SEQUENCE=20,
$
TYPE=REPORT,
COLUMN=N36,
WRAP=1.000000,
SEQUENCE=21,
$
TYPE=REPORT,
COLUMN=N38,
WRAP=1.000000,
SEQUENCE=23,
$
TYPE=REPORT,
COLUMN=N37,
WRAP=1.000000,
SEQUENCE=22,
$
TYPE=REPORT,
COLUMN=N39,
WRAP=1.000000,
SEQUENCE=24,
$
TYPE=REPORT,
COLUMN=N1,
WRAP=1.250000,
SEQUENCE=1,
$
TYPE=REPORT,
COLUMN=N2,
WRAP=0.750000,
$
TYPE=REPORT,
COLUMN=N22,
WRAP=1.000000,
$
TYPE=REPORT,
COLUMN=N24,
WRAP=1.500000,
$
TYPE=REPORT,
COLUMN=N44,
WRAP=0.750000,
$
TYPE=REPORT,
COLUMN=N40,
SEQUENCE=2,
$
TYPE=REPORT,
COLUMN=N41,
SEQUENCE=3,
$
ENDSTYLE
END

This looks like a bug to me. I created the following example using the CAR file and it looks like when you use SEQUENCE and then hold an EXL2K FORMULA file format, the formulas do not adjust to the new sequence of the columns. If you take off the word FORMULA and hold just a normal EXL2K file, it seems to work.

I would report this to IBI.

TABLE FILE CAR
SUM RCOST DCOST
BY COUNTRY
SUM SALES
BY COUNTRY SUBTOTAL SALES
BY CAR
PRINT SEATS
BY COUNTRY SUBTOTAL SEATS
BY CAR
BY MODEL
BY BODYTYPE
ON TABLE PCHOLD FORMAT EXL2K FORMULA
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF,$
TYPE=REPORT, COLUMN=SALES, SEQUENCE=1,$
ENDSTYLE
END

The problem turned out to be with the output format. Changing the format from EXL2K FORMULA
to EXL2K solved the problem. The subtotals also lined up correctly with HTML and PDF. Fortunately, this report did not need recompute or summarize.

So, does anyone know why EXL2K FORMULA misaligned the subtotals?

Thanks,

John

John,

SEQUENCE seems to be a command that affects the visual output of the report and is probably done near the end of creating the report output. When HOLDing in FORMAT EXL2K FORMULA, it looks like the formulas for the spreadsheet are created before the SEQUENCE setting is applied. Therefore the formulas are pointing to the wrong cells in the spreadsheet when processing is complete.

Open a tech support case with IBI to report this issue.

Has anyone found that even using just EXL2K (no formula) if you change the SALES column to a sequence like 2 instead of 1 that the subtotal is not placed in sequence 2 accordingly?