As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.
Join the TIBCO Community TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.
From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
Request access to the private WebFOCUS User Group (login required) to network with fellow members.
Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.
I noticed my codes below that RECAP generated undesirable row-total for me, and I don't know how to get around with this: The REPCAP of row-total is not giving me the current row's total, but to apply the same condition on the original row-total(if amount le 0 then 0 else amount).
TABLE FILE YAAJREP
SUM
BSAMT/D10B AS ''
ACROSS IFSHNM AS ''
FOR
AJIFCD
"Asset" LABEL R1 OVER
" " LABEL R2 OVER
"Current Asset" LABEL R3 OVER
'111' ADD ALL AS CAPTION LABEL A111 NOPRINT OVER
RECAP AR111=IF A111 LE 0 THEN 0 ELSE A111;
AS 'Cash' OVER
'133' ADD ALL AS CAPTION LABEL A133 NOPRINT OVER
RECAP AR133=IF A133 LE 0 THEN 0 ELSE A133;
AS 'Related Party Loans' OVER
....
ON TABLE ROW-TOTAL AS 'Total'
...
Outcome from the above code:
Com-1 Com-2 Com-3
Account# sales sales sales Total
Cash 111 100 290 -250 140
Recap(cash) 111 100 290 0 140
Loan 123 -100 290 -250 -60
Recap(loan) 123 0 290 0 0
Ideal outcome:
Com-1 Com-2 Com-3
Account# sales sales sales Total
Cash 111 100 290 -250 140
Recap(cash) 111 100 290 0 390 <== I want current row's total
Loan 133 -100 290 -250 -60
Recap(loan) 133 0 290 0 290 <== I want current row's total
Is there a way I can choose how I perform row-total? I was thinking about RECOMPUTE the total, but can not figure out what is my last column reference(the "Total" column) {there are more that 3 companies in real life}.
Let me borrow Francis' example:
-* File fml01.fex
APP APPENDPATH IBISAMP
TABLE FILE CAR
SUM
RETAIL_COST AS 'Retail'
DEALER_COST AS 'Dealer'
SEATS NOPRINT
SALES NOPRINT
ACROSS SEATS AS ' '
FOR COUNTRY
'W GERMANY' AS 'W GERMANY' LABEL R2 OVER
RECAP WGERMANY = IF R2 GT 50000 THEN R2 ELSE 0; AS 'RECAP W GERMANY'
ON TABLE ROW-TOTAL AS 'TTL'
END
and the TTL should be 58,377 & 0
Thanks for all the help.
HuaThis message has been edited. Last edited by: Hua, November 26, 2009 12:06 PM
Developer Studio 7.6.11 AS400 - V5R4 HTML,PDF,XLS
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008
Thank you for stating the behavior of the RECAP function. I am still having trouble with the row totals(and the account# from anther post, which is not as crusal as the totals). Am I heading to the wrong path? Do you or anyone have any ideal how to approach this?
Thanks. Hua
Developer Studio 7.6.11 AS400 - V5R4 HTML,PDF,XLS
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008
A quick answer would be that the ON TABLE ROW-TOTAL will behave independently of the RECAP commands, so try getting the row total there by some other means.
Perhaps posting an example using the CAR file will help us figure out a solution.
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
You're more into FML than I am - I have to re-learn it every time I use it.
The most complicated part of it is the row/column notation - they sure didn't make it intuitive - they should have used the same sort of syntax as Excel.
This hopefully points you in the right direction - after your RECAP command that calculates the incorrect row-total, add another RECAP command of the same name as the previous, but one that only addresses a specific cell.
This is not correct, but something like this:
OVER
RECAP WGERMANY = IF R2 GT 50000 THEN R2 ELSE 0; AS 'RECAP W GERMANY'
OVER
RECAP WGERMANY(9) = WGERMANY(5);
In this case, and I don't know why, WGERMANY(9) is the 6th column of the 2nd row (the first column of "TTL") - who knows why "9" addresses this cell.
A couple of examples that don't have a row-total...
From Ken Lane:
-*-- FRL / FML demo from Ken Lane
-SET &ECHO=ALL
SET NODATA=0
-RUN
DEFINE FILE CAR
RETAIL_SALES/D10 = SALES * RETAIL_COST;
COST/D10 = SALES * DEALER_COST;
END
-RUN
TABLE FILE CAR
SUM
COST AS 'Cost'
RETAIL_SALES AS 'Sales'
COMPUTE PROFITPCT/D6.2 = (RETAIL_SALES - COST) / RETAIL_SALES * 100; AS '%'
ACROSS HIGHEST SEATS
FOR
COUNTRY
'W GERMANY' LABEL CNE1 OVER
'ITALY' LABEL CNE2 OVER
'ENGLAND' LABEL CNE3 OVER
RECAP TOTE = CNE1 + CNE2 + CNE3; AS 'TOTAL EUROPE' OVER
RECAP TOTE(3,*,3) = (TOTE(*-1) - TOTE(*-2)) / TOTE(*-1) * 100; OVER
'JAPAN' LABEL CNA1 OVER
'TAIWAN' LABEL CNA2 OVER
RECAP TOTA = CNA1 + CNA2; AS 'TOTAL ASIA' OVER
RECAP TOTA(3,*,3) = (TOTA(*-1) - TOTA(*-2)) / TOTA(*-1) * 100; OVER
RECAP TOTT = TOTE + TOTA; AS 'TOTAL' OVER
RECAP TOTT(3,*,3) = (TOTT(*-1) - TOTT(*-2)) / TOTT(*-1) * 100;
ON TABLE SET PAGE NOPAGE
ON TABLE SET STYLE *
TYPE=REPORT, FONT='Arial', SIZE=8, $
TYPE=REPORT, LABEL=TOTA, STYLE=BOLD, COLOR=RED, $
TYPE=REPORT, LABEL=TOTE, STYLE=BOLD, COLOR=BLUE, $
TYPE=REPORT, LABEL=TOTT, STYLE=BOLD, COLOR=GREEN, $
ENDSTYLE
END
-RUN
-* File fml1.fex
TABLE FILE GGSALES
SUM
DOLLARS
BY CATEGORY
BY ST
BY CITY
ACROSS DATE
FOR
PRODUCT
'Capuccino' AS 'Capuccino' LABEL R1 OVER
'Espresso' AS 'Espresso' LABEL R2 OVER
'Latte' AS 'Latte' LABEL R3 OVER
'Scone' AS 'Scone' LABEL R4 OVER
RECAP R5(1,*,1)=(R1*(-1))+(R2*(-1))+R3+R4+R5(*-1);
AS 'Total' OVER
RECAP R6(1,*,1)/P7.4=R5/R1;
AS ' ' NOPRINT
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=DATA,
LABEL=R5,
ACROSSCOLUMN=N1,
BACKCOLOR='SILVER',
$
TYPE=TITLE,
STYLE=BOLD,
$
TYPE=ACROSSVALUE,
SIZE=9,
$
TYPE=ACROSSTITLE,
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
BACKCOLOR=RGB(210 210 210),
STYLE=BOLD,
$
ENDSTYLE
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
A brief look at these codes that should gives me some help to address the column positions. And you are probably right about the 2nd RECAP.
quote:
In this case, and I don't know why, WGERMANY(9) is the 6th column of the 2nd row (the first column of "TTL") - who knows why "9" addresses this cell.
My wild guess is that there are more than just the 4&5 seated cars in the CAR file and columns may be reserved for those non-displayed seats. Our companies in the report will be exactly like the seats. Therefore I need to figure out the "column" numbers for my report. So I hope someone have the logical formula for calculate the column numbers.
Meanwhile, I'll have a closer look at the examples you provided.
Thanks a lot.
Hua
Developer Studio 7.6.11 AS400 - V5R4 HTML,PDF,XLS
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008
I haven't have a solution yet. In fact, I open a case for help on this. So allow me to change the heading to case-opened.
I have been researching on "Referring/Applying Relative Column Addressing in a RECAP Expression" & CNATATION Cell notations and found myself heading to a wall. I am turning to POST/PICKUP to see if that helps. I like to keep this post open that if someone gone down the simular path would give me some insight.
Thanks.
Hua
Developer Studio 7.6.11 AS400 - V5R4 HTML,PDF,XLS
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008
I am given a work-around solution by our rep, which enables me to show the for-value & row-totals correctly. THANK YOU!
In FML, ACROSS limits the columns that are within the ACROSS-columns. You can not have columns outside that range; ROW-TOTAL not working for conditional RECAP, this has nothing to do with the ACROSS.
The idea of the work-around is to abandon the ACROSS & ROW-TOTAL, use the DM to contruct the string of columns required and a string to add all the columns. I came to realize how powerful DM can be, but it came with a price - prepare the give up GUI and do all the grunt work yourself!
Here is the solution:
-* File z_hua_example_2.fex
APP APPENDPATH IBISAMP
-RUN
TABLE FILE GGSALES
SUM MAX.REGION
COMPUTE BLANK/A100 = ' ';
BY HIGHEST REGION NOPRINT
ON TABLE HOLD AS TREG FORMAT ALPHA
END
-RUN
-** Read the values for regions into variables.
-SET &LN_COUNT = &LINES;
-SET &LAST_COL = &LINES + 1;
-SET &TCOL = &LINES + 2;
-REPEAT :RD_LOOP FOR &I FROM 1 TO &LN_COUNT;
-READ TREG ®.&I.A30.
-TYPE ®.&I
-:RD_LOOP
-* COMPUTE LINE/A200 = 'DEF' || EDIT(CTR) || '/D12 = IF REGION EQ ' | ('''' || MAX.REGION || '''' || ' THEN DOLLARS ELSE 0; AS ' | '''' || MAX.REGION || '''');
-SET &ECHO=ON;
DEFINE FILE GGSALES
-** define the columns for all of the regions.
-REPEAT :DFR_LOOP FOR &I FROM 1 TO &LN_COUNT;
-SET &LN = 'DEF' || &I || '/D12 = IF REGION EQ ' | ('''' || ®.&I || '''' || ' THEN DOLLARS ELSE 0;');
&LN.EVAL
-:DFR_LOOP
-** define the column total now.
DEFTOTAL/D12 =
-REPEAT :DFT_LOOP FOR &I FROM 1 TO &LN_COUNT;
-SET &LN = 'DEF' || &I || ' + ';
&LN.EVAL
-:DFT_LOOP
0;
END
TABLE FILE GGSALES
SUM
COMPUTE CAT/A8 = FMLFOR('A8');
-** show the verb objects.
-REPEAT :VO_LOOP FOR &I FROM 1 TO &LN_COUNT;
-SET &VO = 'DEF' || &I || ' AS ' | ( '''' || ®.&I || '''');
&VO.EVAL
-:VO_LOOP
-** show the column total now.
DEFTOTAL AS 'Total'
FOR
'GGSALES.SALES01.CATEGORY'
'Coffee' AS 'Original Coffee line' LABEL R1 OVER
RECAP
CR1 = IF R1 GT 4200000 THEN R1 ELSE 0;
CR1(1) = 'Coffee';
CR1(&TCOL) =
-REPEAT :CR1 FOR &I FROM 2 TO &LAST_COL;
CR1(&I) +
-:CR1
0;
AS 'Recap coffee line1' OVER
RECAP
CR2 = IF R1 LE 4200000 THEN R1 ELSE 0;
CR2(&TCOL) =
-REPEAT :CR2 FOR &I FROM 2 TO &LAST_COL;
CR2(&I) +
-:CR2
0;
AS 'Recap coffee line2' OVER
'Food' AS 'Food' LABEL R2 OVER
'Gifts' AS 'Gifts' LABEL R3
ON TABLE NOTOTAL
END
-EXIT
Thank you everyone for helping!
Hua
Developer Studio 7.6.11 AS400 - V5R4 HTML,PDF,XLS
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008