

Go  New  Search  Notify  Tools  Reply 
Expert 
I'm sure it's easy, but for the life of me, I can't figure this one out. I would like to calculate the percentages by the columns, not the rows. In the results of the sample FEX below, I would like to see 50%, 25%, 25% in the "Count" percent rows for England. I must be missing something small. Thanks in advance, Francis. * Example of PCT and CNT for a BY and ACROSS report DEFINE FILE CAR CTOT_COST/D8 WITH DEALER_COST = 1; TOT_COST/D8 = DEALER_COST; END TABLE FILE CAR SUM CTOT_COST NOPRINT TOT_COST NOPRINT BY SEATS SUM CNT.DEALER_COST AS 'Count' COMPUTE PCTCTOT_COST/D4% = CNT.DEALER_COST / CTOT_COST * 100; AS '' DEALER_COST AS 'Cost' COMPUTE PCTDEALER_COST/D4% = DEALER_COST / TOT_COST * 100; AS '' BY SEATS ACROSS COUNTRY AS '' AND COLUMNTOTAL WHERE COUNTRY IN ('ENGLAND', 'ITALY'); ON TABLE SET STYLE * TYPE=REPORT, FONT='Verdana', SIZE=9, $ TYPE=TITLE, COLOR=GREEN, $ TYPE=DATA, COLOR=GREEN, $ TYPE=SUBTOTAL, COLOR=GREEN, $ TYPE=GRANDTOTAL, COLOR=GREEN, $ TYPE=TITLE, COLUMN=DEALER_COST(*), COLOR=BLUE, $ TYPE=DATA, COLUMN=DEALER_COST(*), COLOR=BLUE, $ TYPE=SUBTOTAL, COLUMN=DEALER_COST(*), COLOR=BLUE, $ TYPE=GRANDTOTAL, COLUMN=DEALER_COST(*), COLOR=BLUE, $ TYPE=TITLE, COLUMN=PCTDEALER_COST(*), COLOR=BLUE, $ TYPE=DATA, COLUMN=PCTDEALER_COST(*), COLOR=BLUE, $ TYPE=SUBTOTAL, COLUMN=PCTDEALER_COST(*), COLOR=BLUE, $ TYPE=GRANDTOTAL, COLUMN=PCTDEALER_COST(*), COLOR=BLUE, $ TYPE=TITLE, COLUMN=SEATS(*), COLOR=BLACK, $ TYPE=DATA, COLUMN=SEATS(*), COLOR=BLACK, $ TYPE=SUBTOTAL, COLUMN=SEATS(*), COLOR=BLACK, $ TYPE=GRANDTOTAL, COLUMN=SEATS(*), COLOR=BLACK, $ ENDThis message has been edited. Last edited by: Francis Mariani,  

Virtuoso 
The short answer is to use the PCT.CNT. and PCT. prefix operators. See the code below: DEFINE FILE CAR CTOT_COST/D8 WITH DEALER_COST = 1; END TABLE FILE CAR SUM CNT.DEALER_COST AS 'Count' PCT.CTOT_COST/D3% AS '' *PCT.CNT.DEALER_COST AS '' DEALER_COST AS 'Cost' PCT.DEALER_COST/D3% AS '' BY SEATS ACROSS COUNTRY AS '' COLUMNTOTAL WHERE COUNTRY IN ('ENGLAND', 'ITALY'); END Just some gotchas in regards to the code above. I added the "/D3%" to change the format of the percent value on the fly so it shows no decimal places and adds the "%" sign. You need to decide if you would like to use "D3%" or "I3%". Each one will round appropriately to the nearest whole number. D3% will guarentee the COLUMNTOTAL shows 100% across the board, whereas using I3% will not. Run it with both to see what I mean. Notice the commented line in the code which used PCT.CNT. as a prefix operator to DEALER_COST. Apparently trying to reformat the field on the fly does not work when using PCT.CNT. so I went with the DEFINE field you originally had in the code.  

Virtuoso 
Here are the results: ENGLAND ITALY SEATS Count Cost Count Cost  2 2 50% 11,719 31% 3 75% 36,320 88% 4 1 25% 14,940 39% 1 25% 4,915 12% 5 1 25% 11,194 30% 0 . . . TOTAL 4 100% 37,853 100% 4 100% 41,235 100% Notice the three % values for COST add up to 100% and the TOTAL line does say 100%. Using I3% would yield different individual values due to truncation (not rounding) and a total of 98%. I was wrong in my previous post when I said both D3% and I3% would round appropriately. Play with both and see what the results are.  

Expert 
Mickey, Thanks for your response. I avoided using the PCT prefix because I could not get it to work when there's more than one BY statement. Unfortunately the example I posted did not make that clear. The code below has more than one BY statement. The PCT. creates a column that adds up to 100 for the TOTAL of the report, I'd like to calculate the percentage of each line within the highest BY statement (BODYTYPE). Cheers, Francis TABLE FILE CAR SUM CNT.DEALER_COST AS 'Count' PCT.CNT.DEALER_COST/D4% AS '' DEALER_COST AS 'Cost' PCT.DEALER_COST/D4% AS '' BY BODYTYPE NOPRINT SUBHEAD " " "Body Type: <BODYTYPE" " " BY SEATS ACROSS COUNTRY AS '' AND ACROSSTOTAL AND COLUMNTOTAL ENDThis message has been edited. Last edited by: Francis Mariani,  

Virtuoso 
Look WITHIN yourself, or at least your post, and ye shall find the answer. Use the key word WITHIN to get what you are looking for. Incidentally, ACROSSTOTAL and COLUMNTOTAL become useless at this point. Run the code below to see what I mean. DEFINE FILE CAR CTOT_COST/D8 WITH DEALER_COST = 1; END TABLE FILE CAR SUM CNT.DEALER_COST AS 'Count' PCT.CTOT_COST/D3% WITHIN BODYTYPE WITHIN COUNTRY AS '' DEALER_COST AS 'Cost' PCT.DEALER_COST/D3% WITHIN BODYTYPE WITHIN COUNTRY AS '' BY BODYTYPE NOPRINT SUBHEAD " " "Body Type: <BODYTYPE" " " BY SEATS ACROSS COUNTRY AS '' ACROSSTOTAL COLUMNTOTAL END  

Expert 
Mickey, Thank you! I've never used WITHIN in this way. Cheers, Francis.  

Expert 
Mickey, I'm back at the PCT. WITHIN game! Would you know why the percentages do not add up to 100% when using decimal places, i.e D8.4% rather than D3%? Thanks. 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  

Platinum Member 
Here's another idea without using across DEFINE FILE CAR T_CNT/I5SC WITH DEALER_COST = 1; T_CNT_PCT/D5.1S WITH DEALER_COST = 1; T_CST/D8SC = DEALER_COST; T_CST_PCT/D5.1S = DEALER_COST; END * TABLE FILE CAR SUM T_CNT AS 'Count' T_CST AS 'Cost' SUM T_CNT AS 'Count' PCT.T_CNT_PCT AS 'Pct.' T_CST AS 'Cost' PCT.T_CST_PCT AS 'Pct.' BY BODYTYPE SUM T_CNT AS 'Count' PCT.T_CNT_PCT WITHIN BODYTYPE AS 'Pct.' T_CST AS 'Cost' PCT.T_CST_PCT WITHIN BODYTYPE AS 'Pct.' BY BODYTYPE BY SEATS SUM T_CNT AS 'Count' PCT.T_CNT_PCT WITHIN SEATS AS 'Pct.' T_CST AS 'Cost' PCT.T_CST_PCT WITHIN SEATS AS 'Pct.' BY BODYTYPE BY SEATS BY COUNTRY END Jim WF DevStu 5.2.6/WF Srv 5.2.4/Win NT 5.2  

Expert 
I now realize that the column that the PCT is used on must be formatted as D99.99 for the percentages to add up to 100. 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  

Expert 
slick! i'd never used WITHIN in that way before, either. thanks mickey.
 

Virtuoso 
Francis, You should not need to include decimal positions in your field reformat. The key is to make sure the field on which the PCT. prefix is applied has a D (or F) format. This is because D and F format fields maintain significant digits internally even though it will not show them all. The following is an expanded version of my original example which illustrates what I mean. Hopefully this helps clarify the behavior. DEFINE FILE CAR CTOT_COST/D8 WITH DEALER_COST = 1; END TABLE FILE CAR SUM CNT.DEALER_COST AS 'Count' PCT.CTOT_COST/D6.2% AS 'D6.2%' PCT.CTOT_COST/I6% AS 'I6%' DEALER_COST AS 'Cost' PCT.DEALER_COST/D6.2% AS 'D6.2%' PCT.DEALER_COST/I6% AS 'I6%' BY SEATS ACROSS COUNTRY AS '' COLUMNTOTAL WHERE COUNTRY IN ('ENGLAND', 'ITALY'); END Thanks! Mickey
 

Expert 
Mickey, thanks for the clarification. My note regarding use of D99.99 was unclear  I meant zero or more decimal places in D format field. Regards. 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  

Gold member 
Hello, i'm having a similar problem. here is what my code looks like: SUM CNTR NOPRINT PCT.CNTR/D12.2% WITHIN TABLE AS '%' WITHIN TIER BY CATEGORY SUM CNT.ITEM AS 'HD count' BY CATEGORY BY CTYPE BY ITEM ACROSS SOMETHING AS '' END it works fine without the across and the second within, but as soon as i add it, i keep getting zeros... please help. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ PROD: WebFOCUS 7.1.3 on Win 2K/IIS 6/ISAPI Mode/SelfServe Apps Only (No App Server) TEST: WebFOCUS 7.1.3 on Win 2K/IIS 6/Weblogic 8.1/Servlet Mode  

Expert 
Zu, "TABLE" is a reserved word i'ld avoid it.
 

Gold member 
i was using the example in the book, i thought i was using TABLE as a reserved word... i dont have a field like that. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ PROD: WebFOCUS 7.1.3 on Win 2K/IIS 6/ISAPI Mode/SelfServe Apps Only (No App Server) TEST: WebFOCUS 7.1.3 on Win 2K/IIS 6/Weblogic 8.1/Servlet Mode  

Expert 
I am back to the PCT ACROSS BY problem FIVE years later!!! SET PAGE=NOPAGE DEFINE FILE CAR REGION/A10 = IF COUNTRY IN ('ENGLAND', 'FRANCE') THEN 'REGION 1' ELSE 'REGION 2'; DSALES/D6 = SALES; END TABLE FILE CAR SUM PCT.DSALES AS '' BY REGION PAGEBREAK BY COUNTRY ACROSS SEATS AS '' END Results: 2 4 5 REGION COUNTRY      REGION 1 ENGLAND 0 0 13 FRANCE . . 0 ==================================== 2 4 5 REGION COUNTRY      REGION 2 ITALY 100 5 . JAPAN . 85 . W GERMANY . 10 87 I would like: 2 4 5 REGION COUNTRY      REGION 1 ENGLAND 0 0 100 FRANCE . . 0 ==================================== 2 4 5 REGION COUNTRY      REGION 2 ITALY 100 5 . JAPAN . 85 . W GERMANY . 10 100 I thought I could control that by WITHIN, but the WITHIN field is not REGION, nor is it COUNTRY. I get an error when I use SEATS: FOC168) THE 'WITHIN' FIELD IS NOT ALSO A 'BY' OR 'ACROSS' FIELD:, but SEATS is an ACROSS field! HELP!This message has been edited. Last edited by: Francis Mariani, 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  

Virtuoso 
If SUM PCT.DSALES AS '' WITHIN SEATSdoes not work, you might try SUM PCT.DSALES AS '' WITHIN TABLE WITHIN SEATS WebFOCUS 7.7.05  

Expert 
Dan, Thanks for the suggestion, it was not completely correct but did point me to the right answer: PCT.DSALES WITHIN REGION WITHIN SEATS AS ''This message has been edited. Last edited by: Francis Mariani, 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  

Expert 
Unfortunately, this doesn't seem to work in the real world: TABLE FILE R010H200 SUM PCT.CNT_UBN_ID/D6.2S WITHIN RRS_OSFI_REF WITHIN P2_OSFI_BIRR_CD WITHIN P2_MS_RR_NAME AS '' BY RRS_OSFI_REF NOPRINT BY P2_OSFI_BIRR_CD NOPRINT BY P2_MS_RR_NAME AS 'Metric' ACROSS P1_OSFI_BIRR_CD NOPRINT ACROSS P1_MS_RR_NAME_SHORT AS 'Final BIRR' ON RRS_OSFI_REF PAGEBREAK I get the error (FOC168) THE 'WITHIN' FIELD IS NOT ALSO A 'BY' OR 'ACROSS' FIELD This message has been edited. Last edited by: Francis Mariani, 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  

Expert 
Below is a fex close to the real world problem I was having. The answer seems to be that the WITHIN must include the highest BY statement and only the first ACROSS statement. I found this out through trial and error. Convoluted fex: * pct1.fex SET &ECHO=ALL; * Generate dummy data  FILEDEF DATAMAST DISK R010H101.mas FILEDEF R010H101 DISK R010H101.ftm RUN * Create the master for R010H101  WRITE DATAMAST FILENAME=R010H101, SUFFIX=FIX, $ WRITE DATAMAST SEGMENT=R010H101, SEGTYPE=S0, $ WRITE DATAMAST FIELDNAME=BOR_RRS_CD , ALIAS=E03, USAGE=A03 , ACTUAL=A03, $ WRITE DATAMAST FIELDNAME=UBN_ID , ALIAS=E01, USAGE=A20 , ACTUAL=A20, $ WRITE DATAMAST FIELDNAME=P2_PERIOD_END_DT, ALIAS=E05, USAGE=YYMD, ACTUAL=A08, $ WRITE DATAMAST FIELDNAME=P1_BOR_RR_CD , ALIAS=E02, USAGE=A02 , ACTUAL=A02, $ WRITE DATAMAST FIELDNAME=P2_BOR_RR_CD , ALIAS=E04, USAGE=A02 , ACTUAL=A02, $ * Create the data file for R010H101  * Set up RRS  SET &NBR_RRS = 2; SET &BOR_RRS_CD1 = 'COM'; SET &BOR_RRS_CD2 = 'COR'; * Set up all possible Risk Ratings DEFAULTS &RRCD1 = '05', &RRCD2 = '10', &RRCD3 = '15', &RRCD4 = '20'; DEFAULTS &RRCD5 = '25', &RRCD6 = '30', &RRCD7 = '35', &RRCD8 = '40'; DEFAULTS &RRCD9 = '45', &RRCD10= '46', &RRCD11= '47', &RRCD12= '50'; DEFAULTS &RRCD13= '51', &RRCD14= '52', &RRCD15= '60', &RRCD16= '71'; DEFAULTS &RRCD17= '72', &RRCD18= '80', &RRCD19= '90'; * Repeat loop for Risk Ratings, Period 1 REPEAT :LOOP_B2 FOR &N FROM 1 TO 19 STEP 1; SET &P1_RRCD = &RRCD.&N; * Repeat loop for Risk Ratings, Period 2 REPEAT :LOOP_B3 FOR &X FROM 1 TO 19 STEP 1; SET &P2_RRCD = &RRCD.&X; * Repeat loop for Risk Rating Systems REPEAT :LOOP_B4 FOR &Y FROM 1 TO &NBR_RRS STEP 1; WRITE R010H101 &BOR_RRS_CD.&Y0000000000000000000099991231&P1_RRCD&P2_RRCD :LOOP_B4 :LOOP_B3 :LOOP_B2 * Create report DEFINE FILE R010H101 CNT_UBN_ID/D10 = 1; RRS_NAME/A12 = DECODE BOR_RRS_CD ('COR' 'Corporate', 'COM' 'Commercial' 'CM1' 'Commercial 1' ELSE '???'); * Set up Risk Rating System sort field  RRS_OSFI_REF/A17 = DECODE BOR_RRS_CD ('COR' '0201 Corporate', 'COM' '0202 Commercial' 'CM1' '0207 Commercial 1' ELSE '???'); * Set up Master Scale Ratings for report  * Initial Period  P1_MS_RR_NAME/A28 = IF P1_BOR_RR_CD EQ '05' THEN 'I1 (Investment Grade) ' ELSE IF P1_BOR_RR_CD EQ '10' THEN 'I2 (Investment Grade) ' ELSE IF P1_BOR_RR_CD EQ '15' THEN 'I3 (Investment Grade) ' ELSE IF P1_BOR_RR_CD EQ '20' THEN 'I4 (Investment Grade) ' ELSE IF P1_BOR_RR_CD EQ '25' THEN 'I5 (Investment Grade) ' ELSE IF P1_BOR_RR_CD EQ '30' THEN 'I6 (Investment Grade) ' ELSE IF P1_BOR_RR_CD EQ '35' THEN 'I7 (Investment Grade) ' ELSE IF P1_BOR_RR_CD EQ '40' THEN 'S1 (Sub Investment Grade)' ELSE IF P1_BOR_RR_CD EQ '45' THEN 'S2 (Sub Investment Grade)' ELSE IF P1_BOR_RR_CD EQ '46' THEN 'S3 (Sub Investment Grade)' ELSE IF P1_BOR_RR_CD EQ '47' THEN 'S4 (Sub Investment Grade)' ELSE IF P1_BOR_RR_CD EQ '50' THEN 'P1 (Watch List) ' ELSE IF P1_BOR_RR_CD EQ '51' THEN 'P2 (Watch List) ' ELSE IF P1_BOR_RR_CD EQ '52' THEN 'P3 (Watch List) ' ELSE IF P1_BOR_RR_CD EQ '60' THEN 'D1 (Watch List) ' ELSE IF P1_BOR_RR_CD EQ '71' THEN 'D2 (Impaired) ' ELSE IF P1_BOR_RR_CD EQ '72' THEN 'D2 (Impaired) ' ELSE IF P1_BOR_RR_CD EQ '80' THEN 'D2 (Impaired) ' ELSE IF P1_BOR_RR_CD EQ '90' THEN 'D2 (Impaired) ' ELSE '???'; P1_MS_RR_NAME_SHORT/A3 = IF P1_BOR_RR_CD EQ '05' THEN 'I1' ELSE IF P1_BOR_RR_CD EQ '10' THEN 'I2' ELSE IF P1_BOR_RR_CD EQ '15' THEN 'I3' ELSE IF P1_BOR_RR_CD EQ '20' THEN 'I4' ELSE IF P1_BOR_RR_CD EQ '25' THEN 'I5' ELSE IF P1_BOR_RR_CD EQ '30' THEN 'I6' ELSE IF P1_BOR_RR_CD EQ '35' THEN 'I7' ELSE IF P1_BOR_RR_CD EQ '40' THEN 'S1' ELSE IF P1_BOR_RR_CD EQ '45' THEN 'S2' ELSE IF P1_BOR_RR_CD EQ '46' THEN 'S3' ELSE IF P1_BOR_RR_CD EQ '47' THEN 'S4' ELSE IF P1_BOR_RR_CD EQ '50' THEN 'P1' ELSE IF P1_BOR_RR_CD EQ '51' THEN 'P2' ELSE IF P1_BOR_RR_CD EQ '52' THEN 'P3' ELSE IF P1_BOR_RR_CD EQ '60' THEN 'D1' ELSE IF P1_BOR_RR_CD EQ '71' THEN 'D2' ELSE IF P1_BOR_RR_CD EQ '72' THEN 'D2' ELSE IF P1_BOR_RR_CD EQ '80' THEN 'D2' ELSE IF P1_BOR_RR_CD EQ '90' THEN 'D2' ELSE '???'; P1_OSFI_BIRR_CD/A04 = IF P1_BOR_RR_CD EQ '05' THEN '0101' ELSE IF P1_BOR_RR_CD EQ '10' THEN '0102' ELSE IF P1_BOR_RR_CD EQ '15' THEN '0103' ELSE IF P1_BOR_RR_CD EQ '20' THEN '0104' ELSE IF P1_BOR_RR_CD EQ '25' THEN '0105' ELSE IF P1_BOR_RR_CD EQ '30' THEN '0106' ELSE IF P1_BOR_RR_CD EQ '35' THEN '0107' ELSE IF P1_BOR_RR_CD EQ '40' THEN '0108' ELSE IF P1_BOR_RR_CD EQ '45' THEN '0109' ELSE IF P1_BOR_RR_CD EQ '46' THEN '0110' ELSE IF P1_BOR_RR_CD EQ '47' THEN '0111' ELSE IF P1_BOR_RR_CD EQ '50' THEN '0112' ELSE IF P1_BOR_RR_CD EQ '51' THEN '0113' ELSE IF P1_BOR_RR_CD EQ '52' THEN '0114' ELSE IF P1_BOR_RR_CD EQ '60' THEN '0115' ELSE IF P1_BOR_RR_CD EQ '71' THEN '0116' ELSE IF P1_BOR_RR_CD EQ '72' THEN '0116' ELSE IF P1_BOR_RR_CD EQ '80' THEN '0116' ELSE IF P1_BOR_RR_CD EQ '90' THEN '0116' ELSE '???'; * Final Period  P2_MS_RR_NAME/A28 = IF (P2_PERIOD_END_DT LT '20090101' AND P2_BOR_RR_CD LT '60') OR P2_BOR_RR_CD IN ('','48') THEN 'No longer rated' ELSE IF P2_BOR_RR_CD EQ '05' THEN 'I1 (Investment Grade) ' ELSE IF P2_BOR_RR_CD EQ '10' THEN 'I2 (Investment Grade) ' ELSE IF P2_BOR_RR_CD EQ '15' THEN 'I3 (Investment Grade) ' ELSE IF P2_BOR_RR_CD EQ '20' THEN 'I4 (Investment Grade) ' ELSE IF P2_BOR_RR_CD EQ '25' THEN 'I5 (Investment Grade) ' ELSE IF P2_BOR_RR_CD EQ '30' THEN 'I6 (Investment Grade) ' ELSE IF P2_BOR_RR_CD EQ '35' THEN 'I7 (Investment Grade) ' ELSE IF P2_BOR_RR_CD EQ '40' THEN 'S1 (Sub Investment Grade)' ELSE IF P2_BOR_RR_CD EQ '45' THEN 'S2 (Sub Investment Grade)' ELSE IF P2_BOR_RR_CD EQ '46' THEN 'S3 (Sub Investment Grade)' ELSE IF P2_BOR_RR_CD EQ '47' THEN 'S4 (Sub Investment Grade)' ELSE IF P2_BOR_RR_CD EQ '50' THEN 'P1 (Watch List) ' ELSE IF P2_BOR_RR_CD EQ '51' THEN 'P2 (Watch List) ' ELSE IF P2_BOR_RR_CD EQ '52' THEN 'P3 (Watch List) ' ELSE IF P2_BOR_RR_CD EQ '60' THEN 'D1 (Watch List) ' ELSE IF P2_BOR_RR_CD EQ '71' THEN 'D2 (Impaired) ' ELSE IF P2_BOR_RR_CD EQ '72' THEN 'D2 (Impaired) ' ELSE IF P2_BOR_RR_CD EQ '80' THEN 'D2 (Impaired) ' ELSE IF P2_BOR_RR_CD EQ '90' THEN 'D2 (Impaired) ' ELSE '???'; P2_MS_RR_NAME_SHORT/A15 = IF (P2_PERIOD_END_DT LT '20090101' AND P2_BOR_RR_CD LT '60') OR P2_BOR_RR_CD IN ('','48') THEN 'No longer rated' ELSE IF P2_BOR_RR_CD EQ '05' THEN 'I1' ELSE IF P2_BOR_RR_CD EQ '10' THEN 'I2' ELSE IF P2_BOR_RR_CD EQ '15' THEN 'I3' ELSE IF P2_BOR_RR_CD EQ '20' THEN 'I4' ELSE IF P2_BOR_RR_CD EQ '25' THEN 'I5' ELSE IF P2_BOR_RR_CD EQ '30' THEN 'I6' ELSE IF P2_BOR_RR_CD EQ '35' THEN 'I7' ELSE IF P2_BOR_RR_CD EQ '40' THEN 'S1' ELSE IF P2_BOR_RR_CD EQ '45' THEN 'S2' ELSE IF P2_BOR_RR_CD EQ '46' THEN 'S3' ELSE IF P2_BOR_RR_CD EQ '47' THEN 'S4' ELSE IF P2_BOR_RR_CD EQ '50' THEN 'P1' ELSE IF P2_BOR_RR_CD EQ '51' THEN 'P2' ELSE IF P2_BOR_RR_CD EQ '52' THEN 'P3' ELSE IF P2_BOR_RR_CD EQ '60' THEN 'D1' ELSE IF P2_BOR_RR_CD EQ '71' THEN 'D2' ELSE IF P2_BOR_RR_CD EQ '72' THEN 'D2' ELSE IF P2_BOR_RR_CD EQ '80' THEN 'D2' ELSE IF P2_BOR_RR_CD EQ '90' THEN 'D2' ELSE '???'; P2_OSFI_BIRR_CD/A04 = IF (P2_PERIOD_END_DT LT '20090101' AND P2_BOR_RR_CD LT '60') OR P2_BOR_RR_CD IN ('','48') THEN '2000' ELSE IF P2_BOR_RR_CD EQ '05' THEN '0101' ELSE IF P2_BOR_RR_CD EQ '10' THEN '0102' ELSE IF P2_BOR_RR_CD EQ '15' THEN '0103' ELSE IF P2_BOR_RR_CD EQ '20' THEN '0104' ELSE IF P2_BOR_RR_CD EQ '25' THEN '0105' ELSE IF P2_BOR_RR_CD EQ '30' THEN '0106' ELSE IF P2_BOR_RR_CD EQ '35' THEN '0107' ELSE IF P2_BOR_RR_CD EQ '40' THEN '0108' ELSE IF P2_BOR_RR_CD EQ '45' THEN '0109' ELSE IF P2_BOR_RR_CD EQ '46' THEN '0110' ELSE IF P2_BOR_RR_CD EQ '47' THEN '0111' ELSE IF P2_BOR_RR_CD EQ '50' THEN '0112' ELSE IF P2_BOR_RR_CD EQ '51' THEN '0113' ELSE IF P2_BOR_RR_CD EQ '52' THEN '0114' ELSE IF P2_BOR_RR_CD EQ '60' THEN '0115' ELSE IF P2_BOR_RR_CD EQ '71' THEN '0116' ELSE IF P2_BOR_RR_CD EQ '72' THEN '0116' ELSE IF P2_BOR_RR_CD EQ '80' THEN '0116' ELSE IF P2_BOR_RR_CD EQ '90' THEN '0116' ELSE '9000'; END RUN TABLE FILE R010H101 SUM * This gives a percentage that makes no sense * PCT.CNT_UBN_ID/D6.2 AS '' * This gives a percentage that makes no sense * PCT.CNT_UBN_ID/D6.2 WITHIN RRS_OSFI_REF AS '' * This works when calculating the percentage across * PCT.CNT_UBN_ID/D6.2 WITHIN P2_OSFI_BIRR_CD AS '' * This works when calculating the percentage across * PCT.CNT_UBN_ID/D6.2 WITHIN P2_MS_RR_NAME_SHORT AS '' * This works when calculating the percentage down a column PCT.CNT_UBN_ID/D6.2 WITHIN RRS_OSFI_REF WITHIN P1_OSFI_BIRR_CD AS '' BY RRS_OSFI_REF NOPRINT * Final Period  BY P2_OSFI_BIRR_CD NOPRINT BY P2_MS_RR_NAME_SHORT AS 'Metric' * Initial Period  ACROSS P1_OSFI_BIRR_CD NOPRINT ACROSS P1_MS_RR_NAME_SHORT AS 'Initial BIRR' ON RRS_OSFI_REF PAGEBREAK HEADING "<RRS_NAME" END RUN 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  

Guru 
Hi Francis. Have you cooled yourself down yet? I want to thank you for this post that I learned what the PCT actually does. Here is what I observed by changing your matrix from 19 X 19 to 3 X 4: TABLE FILE R010H101 SUM * This gives a percentage that makes no sense * % over the final column total (by default) PCT.CNT_UBN_ID/D6.2 AS '' * This gives a percentage that makes no sense * % over the byfield total PCT.CNT_UBN_ID/D6.2 WITHIN RRS_OSFI_REF AS '' * This works when calculating the percentage across * % over byfield total(WITHIN higher byfield is assumed) PCT.CNT_UBN_ID/D6.2 WITHIN P2_OSFI_BIRR_CD AS '' * This works when calculating the percentage across * (same as the third) PCT.CNT_UBN_ID/D6.2 WITHIN P2_MS_RR_NAME_SHORT AS '' * This works when calculating the percentage down a column * % over the acrosstotal(WITHIN the higher byfield) PCT.CNT_UBN_ID/D6.2 WITHIN RRS_OSFI_REF WITHIN P1_OSFI_BIRR_CD AS '' * %over itself( the cell defined by P2_OSFI_BIRR_CD & P1_OSFI_BIRR_CD) PCT.CNT_UBN_ID/D6.2 WITHIN P2_OSFI_BIRR_CD WITHIN P1_OSFI_BIRR_CD AS '' BY RRS_OSFI_REF NOPRINT * Final Period  BY P2_OSFI_BIRR_CD NOPRINT BY P2_MS_RR_NAME_SHORT AS 'Metric' * Initial Period  ACROSS P1_OSFI_BIRR_CD NOPRINT ACROSS P1_MS_RR_NAME_SHORT AS 'Initial BIRR' ON RRS_OSFI_REF PAGEBREAK HEADING "<RRS_NAME" END RUN Hua Developer Studio 7.6.11 AS400  V5R4 HTML,PDF,XLS  

Expert 
Hua, This PCT thing really got me worked up! After finding the solution by adding an additional WITHIN of the higherlevel BY PAGEBREAK column, I modified my program to not require the higherlevel BY PAGEBREAK column  the PCT WITHIN failed when I removed this nonrequired column! I had to keep it in there to make it work. The manual touches very briefly on WITHIN and it doesn't provide an example for ACROSS. Cheers, 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  

Expert 
Back at it! In 2005, Mickey said
That's what I'm doing and I don't get the percentages to add up to 100, even when I have four decimal places (D8.4). PCT with WITHIN and ACROSS and BY just does not add up! 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  

Expert 
A strange discovery: PCT.CNT_PIN/D15.3 WITHIN GROUP_CD AS 'PCT_CNT' If the number of decimal places is even, the percentage calculation rounds up  giving me a total greater than 100%; If the number of decimal places is odd, the percentage calculation rounds down  giving me a total less than 100%. 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  

Powered by Social Strata 
Please Wait. Your request is being processed... 