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

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,

WebFocus 7.69
Windows 2003
Db2 for Windows 9.5

Posts: 9 | Registered: November 16, 2009

Francis Mariani

Expert

posted

Hide Post

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

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

Show Post

Francis Mariani

Expert

posted

Hide Post

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

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

Show Post

Vas

Member

posted

Hide Post

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

WebFocus 7.69
Windows 2003
Db2 for Windows 9.5

Posts: 9 | Registered: November 16, 2009

Ignored post by Vas posted

Show Post

Francis Mariani

Expert

posted

Hide Post

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.

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

Show Post

Vas

Member

posted

Hide Post

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

WebFocus 7.69
Windows 2003
Db2 for Windows 9.5

Posts: 9 | Registered: November 16, 2009

Ignored post by Vas posted

Show Post

Vas

Member

posted

Hide Post

Hi all,

Any ideas somebody?

WebFocus 7.69
Windows 2003
Db2 for Windows 9.5

Posts: 9 | Registered: November 16, 2009

Ignored post by Vas posted

Show Post

Vas

Member

posted

Hide Post

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,

WebFocus 7.69
Windows 2003
Db2 for Windows 9.5

Posts: 9 | Registered: November 16, 2009

Ignored post by Vas posted

Show Post

Vas

Member

posted

Hide Post

Guys any ideas on it?

WebFocus 7.69
Windows 2003
Db2 for Windows 9.5

Posts: 9 | Registered: November 16, 2009

Ignored post by Vas posted

Show Post

Francis Mariani

Expert

posted

Hide Post

If you've tried to code something similar to the working example I posted, and it does not work, perhaps no one here can explain that. How about opening a case with Tech Support?

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

Show Post

Please Wait. Your request is being processed...

Read-Only Topic

Focal Point

Focal Point Forums

WebFOCUS/FOCUS Forum on Focal Point

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

Focal Point | Privacy Statement | Terms of Service