[SOLVED -DON'T KNOW HOW THOUGH] How to Recompute Across Total Columns Correctly

November 17, 2009, 01:11 PM

Hello everybody!

After searching the forums and reading webfocus tutorials i still haven't been able to recompute my across total column correctly.
I didn't know how to reproduce the problem with the focus sample databases provided so i just insert hereby the whole code for the report i want to produce.

aLL I want is to get M% recomputed correctly on row level.

  
SET FOCTRANSFORM = ON
SET EMPTYREPORT = ON
SET CDN = ON
TABLE FILE BV1
SUM
     'BV1.TRANSACTIONS.amount/P17.2C'
 AS 'WITHDRAWALS'
     'CNT.BV1.TRANSACTIONS.ACCOUNT/I7C' AS 'No_Of_Loans'
     COMPUTE M1/D6.2% = BV1.INTEREST.INTEREST / CNT.BV1.TRANSACTIONS.ACCOUNT;
 AS 'M%'
BY 'BV1.BRANCH.CODE'
BY 'BV1.BRANCH.BRANCH'
ACROSS 'BV1.PRODUCTS.S_PRODUCT_GROUP_NM' AS '' ACROSS-TOTAL
ON TABLE SUBHEAD
"My report header"
HEADING
"My page heading"
WHERE BV1.TRANSACTIONS.MONTHLY_DATA AND ( BV1.PRODUCTS.ΟΝΟΜΑ EQ 'ΕΚ' OR 'ΕΠ' OR 'ΚΚΕ' OR 'Ε1-2' );
ON TABLE SET PAGE-NUM OFF
ON TABLE SUMMARIZE BV1.TRANSACTIONS.amount BV1.TRANSACTIONS.ACCOUNT M1 AS 'TOTAL'
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = 'tt_dw',
$
     UNITS=IN,
     PAGESIZE='A4',
     LEFTMARGIN=0.250000,
     RIGHTMARGIN=0.250000,
     TOPMARGIN=0.250000,
     BOTTOMMARGIN=0.250000,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     FONT='ARIAL',
     SIZE=9,
     RIGHTGAP=0.125000,
$
TYPE=DATA,
     COLUMN=N2,
     BORDER-RIGHT=LIGHT,
     BORDER-RIGHT-STYLE=DOUBLE,
     BORDER-RIGHT-COLOR=RGB(51 51 153),
$
TYPE=DATA,
     ACROSSCOLUMN=N3,
     BORDER-RIGHT=LIGHT,
     BORDER-RIGHT-STYLE=DOUBLE,
     BORDER-RIGHT-COLOR=RGB(51 51 153),
$
TYPE=TITLE,
     STYLE=BOLD,
$
TYPE=TITLE,
     COLUMN=N2,
     BORDER-RIGHT=LIGHT,
     BORDER-BOTTOM-STYLE=RIDGE,
     BORDER-RIGHT-STYLE=DOUBLE,
$
TYPE=TITLE,
     ACROSSCOLUMN=N3,
     BORDER-BOTTOM=LIGHT,
     BORDER-LEFT=LIGHT,
     BORDER-RIGHT-STYLE=DOUBLE,
$
TYPE=TABHEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=TABHEADING,
     LINE=1,
     OBJECT=TEXT,
     ITEM=1,
     SIZE=10,
$
TYPE=TABFOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=HEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=HEADING,
     LINE=1,
     JUSTIFY=LEFT,
$
TYPE=FOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=SUBHEAD,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBFOOT,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBTOTAL,
     BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
     SIZE=9,
$
TYPE=ACROSSTITLE,
     STYLE=BOLD,
$
TYPE=GRANDTOTAL,
     BACKCOLOR='NONE',
     STYLE=BOLD,
$
ENDSTYLE
END

By the way ------------ ACROSS RECOMPUTE 'BV1.PRODUCTS.S_PRODUCT_GROUP_NM' AS '' ACROSS-TOTAL--------- IS NOT ACCEPTED. wHY?

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

November 17, 2009, 01:50 PM

Francis Mariani

TABLE FILE CAR
SUM
SALES
RETAIL_COST AS 'RETAIL'
DEALER_COST AS 'DEALER'
COMPUTE M1/D6.2% = RETAIL_COST / DEALER_COST;

ACROSS COUNTRY RECOMPUTE
BY SEATS
END

November 17, 2009, 02:03 PM

Francis Mariani

Creating Reports With WebFOCUS Language > Including Totals and Subtotals > Producing Summary Columns for Horizontal Sort Fields

November 18, 2009, 04:10 AM

Vas

Francis thank you very much for reply!
I appreciate it a lot.

I have read the specific tutorial many times and i have tried to apply the techniques. So in my code that would be:

 
ACROSS 'BV1.PRODUCTS.S_PRODUCT_GROUP_NM' RECOMPUTE AS 'CORRECT ACROSS TOTAL'

However if i try this what i get is the following Error:

FOC32498) FOCEXEC PARSER ERROR. INVALID SYMBOL: RECOMPUTE
--------------------------------------------------------vvvvvvvvv
ACROSS 'BV1.PRODUCT.S_PRODUCT_GROUP_NM' RECOMPUTE AS 'CORRECT
AC
(FOC32498) FOCEXEC PARSER ERROR. INVALID SYMBOL: AS
------------------------------------------------------------------vv
ACROSS 'BV1.PRODUCT.S_PRODUCT_GROUP_NM' RECOMPUTE AS 'CORRECT
AC
(FOC32498) FOCEXEC PARSER ERROR. INVALID SYMBOL: 'CORRECT ACROSS TOTAL'
---------------------------------------------------------------------vvvvvvvvvv
ACROSS 'BV1.PRODUCT.S_PRODUCT_GROUP_NM' RECOMPUTE AS 'CORRECT
AC
0 ERROR AT OR NEAR LINE 8 IN PROCEDURE test FOCEXEC *
(FOC003) THE FIELDNAME IS NOT RECOGNIZED:
BV1.TRANSACTIONS.TRANSACTIONS.amount/P17.2C
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT

November 18, 2009, 10:55 AM

Francis Mariani

Vas, please try running this code:

TABLE FILE CAR
SUM 
     'CAR.BODY.SALES/D12' AS 'Sales'
     'CAR.BODY.RETAIL_COST' AS 'Retail,Cost'
     'CAR.BODY.DEALER_COST' AS 'Dealer,Cost'
     COMPUTE M1/D6.2% = CAR.BODY.RETAIL_COST / CAR.BODY.DEALER_COST; AS '%'
BY 'CAR.BODY.SEATS' AS 'Seats'
ACROSS 'CAR.ORIGIN.COUNTRY' AS '' RECOMPUTE AS 'TOTAL COUNTRIES'
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE NOTOTAL
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=9,
     COLOR='BLACK',
     STYLE=NORMAL,
$
TYPE=TITLE,
     STYLE=BOLD,
$
TYPE=ACROSSVALUE,
     ACROSS=1,
     COLOR='BLUE',
     JUSTIFY=CENTER,
$
TYPE=ACROSSTITLE,
     ACROSS=1,
     COLOR='BLUE',
     JUSTIFY=CENTER,
$
ENDSTYLE
END

This code was generated by Dev Studio Report Painter, EXCEPT for RECOMPUTE AS 'TOTAL COUNTRIES' which the v7.6.5 GUI removes when you switch from the Source tab to the Report tab and back to the Source tab. It appears that this feature is only documented in the "Creating Reports With WebFOCUS Language" manual, therefore it's not yet compatible with Report Painter, though this does not explain why you get the error, I can't believe they removed this option in v7.6.9.

November 19, 2009, 10:07 AM

Vas

Francis,

Your code runs just fine! The v7.6.5.9 GUI also removes the RECOMPUTE AS 'TOTAL COUNTRIES' code bit.

So i really don't get what is wrong with my code.

I would also like to ask you, if there is any other way to recompute the across total column correctly in a way that the GUI is synchronized with the code. ??

Thank you so much for your help

November 23, 2009, 05:24 AM

Vas

Hi all,

Any ideas somebody?

November 23, 2009, 07:38 AM

Vas

As i have said earlier the following piece of code does not work:

 
ACROSS 'BV1.PRODUCTS.S_PRODUCT_GROUP_NM' RECOMPUTE AS 'CORRECT ACROSS TOTAL'

But the following does work as i want it to work:

 
ACROSS 'BV1.PRODUCTS.S_PRODUCT_GROUP_NM' ON 'BV1.PRODUCTS.S_PRODUCT_GROUP_NM' RECOMPUTE AS 'CORRECT ACROSS TOTAL'

Can somebody explain that to me? Why does it work this way?

Thanks

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