I am trying to compute percentage for a column value as follows:
> TABLE FILE AGG_ARBYSCHOOL -*ON J0.DIM_SCHOOL.DIVISIONNAME SUBTOTAL AS 'DIVISIONTOTAL' -* -*ON J0.DIM_SCHOOL.DISTRICTNAME SUBTOTAL AS 'TOTAL' -*ON J0.DIM_SCHOOL.DIVISIONNAME RECOMPUTE AS 'Division Total' -*ON TABLE SET BYPANEL ON -*ON TABLE COLUMN TOTAL OFF SUM 'AGG_ARBYSCHOOL.AGG_ARBYSCHOOL.0TO6/I7' AS '' 'AGG_ARBYSCHOOL.AGG_ARBYSCHOOL.7TO13/I7' AS '' 'AGG_ARBYSCHOOL.AGG_ARBYSCHOOL.14TO20/I7' AS '' 'AGG_ARBYSCHOOL.AGG_ARBYSCHOOL.21TO27/I7' SKIP-LINE AS '' OVER COMPUTE 0to6PCT/I7% = ( 0TO6 / TOT.0TO6 ) * 100; AS ''
COMPUTE 7to13PCT/I7% = ( 7TO13 / TOT.7TO13 ) * 100; AS '' COMPUTE 14to20PCT/I7% = ( 14TO20 / TOT.14TO20 ) * 100; AS '' COMPUTE 21to27PCT/I7% = ( 21TO27 / TOT.21TO27 ) * 100; AS '' BY 'J0.DIM_SCHOOL.DIVISIONNAME' AS 'Division' RECOMPUTE BY 'J0.DIM_SCHOOL.DISTRICTNAME' AS 'District' RECOMPUTE
ON J0.DIM_SCHOOL.DIVISIONNAME SUBTOTAL AS 'Total ' <\> > What am I doing wrong? I am trying to calculate percentage to show under the sum value and I would like to give my columns headers. If I add header it repates with every value. How can I accomplish this that I have header appearing once for the SUM column and % value goes under sum value, and all values are alligned?This message has been edited. Last edited by: Kerry,
WebFOCUS 7.6.10 Windows HTML
June 10, 2010, 09:47 AM
GamP
Maybe something like this might be of help?
TABLE FILE CAR
SUM
DEALER_COST AS ''
RETAIL_COST AS ''
OVER
COMPUTE DCPCT/D7.2% = ( DEALER_COST / TOT.DEALER_COST ) * 100; AS ''
COMPUTE RCPCT/D7.2% = ( RETAIL_COST / TOT.RETAIL_COST ) * 100; AS ''
BY COUNTRY AS ''
BY CAR AS ''
ON TABLE SUBHEAD
"COUNTRY<+0>CAR<+0>DCOST<+0>RCOST"
ON TABLE NOTOTAL
ON TABLE SET STYLE *
TYPE=TABHEADING,
HEADALIGN=BODY,
$
ENDSTYLE
END
GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
June 10, 2010, 09:49 AM
Francis Mariani
You need to code the column titles in the page heading and then use HEADALIGN=BODY:
TABLE FILE CAR
SUM
LENGTH AS ''
WIDTH AS ''
WHEELBASE AS ''
WEIGHT AS ''
OVER
COMPUTE 1PCT/I7% = ( LENGTH / TOT.LENGTH ) * 100; AS ''
COMPUTE 2PCT/I7% = ( WIDTH / TOT.WIDTH ) * 100; AS ''
COMPUTE 3PCT/I7% = ( WHEELBASE / TOT.WHEELBASE ) * 100; AS ''
COMPUTE 4PCT/I7% = ( WEIGHT / TOT.WEIGHT ) * 100; AS ''
BY COUNTRY AS ''
BY CAR AS ''
BY MODEL AS ''
HEADING
"CAR REPORT"
"COUNTRY <+0> CAR <+0> MODEL <+0> LENGTH <+0> WIDTH <+0> WHEEL BASE <+0> WEIGHT"
ON TABLE SET STYLE *
TYPE=REPORT, FONT='ARIAL', SIZE=9, $
TYPE=HEADING, HEADALIGN=BODY, STYLE=BOLD, $
TYPE=HEADING, LINE=1, COLSPAN=7, $
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
June 11, 2010, 01:04 PM
Arif
Thank you it was helpful code, but when I run it in my environment, My headings dont align with column data. It seems like this code only works with the grid on. > TABLE FILE AGGRIGATE_ENROLLMENT_VIEW_BV01 SUM 'AGGRIGATE_ENROLLMENT_VIEW_BV01.AGG_ENR.ENROLLEDACTIVE' AS '' 'AGGRIGATE_ENROLLMENT_VIEW_BV01.AGG_ENR.NEWENROLL' AS '' OVER COMPUTE 3PCT/I7% = ( ENROLLEDACTIVE / TOT.ENROLLEDACTIVE) * 100; AS '' COMPUTE 3PCT/I7% = ( NEWENROLL / TOT.NEWENROLL) * 100; AS '' BY 'AGGRIGATE_ENROLLMENT_VIEW_BV01.SCHOOL.DIVISIONNAME' AS '' BY 'AGGRIGATE_ENROLLMENT_VIEW_BV01.SCHOOL.DISTRICTNAME' AS '' HEADING "Enrollment Test" "DIVISION<+5> DISTRICT<+5> ENROLLED<+5> NEWENROLLE"
FOOTING "" ON TABLE SET PAGE-NUM OFF ON TABLE NOTOTAL ON TABLE PCHOLD FORMAT HTML ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * UNITS=IN, SQUEEZE=ON, ORIENTATION=PORTRAIT, $ TYPE=REPORT, GRID=OFF, FONT='ARIAL', SIZE=9, $ TYPE=TITLE, STYLE=BOLD, $ TYPE=TABHEADING, SIZE=12, STYLE=BOLD, LINE=1, COLSPAN=7, HEADALIGN=BODY, $ TYPE=TABFOOTING, SIZE=12, STYLE=BOLD, $ TYPE=HEADING, SIZE=12, STYLE=BOLD, $ 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=RGB(210 210 210), STYLE=BOLD, $ ENDSTYLE END > Is there anything I can do to align my columns heading with the data?
WebFOCUS 7.6.10 Windows HTML
June 15, 2010, 09:32 AM
RSquared
Try adding "IN col"
quote:
TABLE FILE AGGRIGATE_ENROLLMENT_VIEW_BV01 SUM 'AGGRIGATE_ENROLLMENT_VIEW_BV01.AGG_ENR.ENROLLEDACTIVE' AS '' IN 1 'AGGRIGATE_ENROLLMENT_VIEW_BV01.AGG_ENR.NEWENROLL' AS '' IN 20 OVER COMPUTE 3PCT/I7% = ( ENROLLEDACTIVE / TOT.ENROLLEDACTIVE) * 100; AS '' IN 1 COMPUTE 3PCT/I7% = ( NEWENROLL / TOT.NEWENROLL) * 100; AS '' IN 20 BY 'AGGRIGATE_ENROLLMENT_VIEW_BV01.SCHOOL.DIVISIONNAME' AS '' BY 'AGGRIGATE_ENROLLMENT_VIEW_BV01.SCHOOL.DISTRICTNAME' AS ''
WF 7.6.11 Oracle WebSphere Windows NT-5.2 x86 32bit