[SOLVED] FML - displaying the FOR-field along with the CAPTION
Hi everyone,
I am building a consolidated financial report with the following code. What I really want is add a column after the CAPTION/TITLE column to display the FOR-field value and I have trouble with this simple task once I use the keyword ACROSS. Thanks for your help.
TABLE FILE YAAJREP
SUM
BSAMT
ACROSS GLFDIV
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 '-' ELSE A111;
AS 'Cash ' OVER
'133' ADD ALL AS CAPTION LABEL A133 NOPRINT OVER
RECAP AR133=IF A133 LE 0 THEN '-' ELSE A133;
AS 'Related Party Loans' OVER
'115.1' ADD ALL AS CAPTION LABEL AR1151 OVER
'115.3' ADD ALL AS CAPTION LABEL AR1153 OVER
'115.4' ADD ALL AS CAPTION LABEL AR1154 OVER
'115.5' ADD ALL AS CAPTION LABEL AR1155 OVER
'115.6' ADD ALL AS CAPTION LABEL AR1156 OVER
'228' ADD ALL AS CAPTION LABEL A228 NOPRINT OVER
RECAP AR228=IF A228 LE 0 THEN '-' ELSE A228;
AS 'Income Taxes' OVER
'115.7' ADD ALL AS CAPTION LABEL AR1157 OVER
'123' ADD ALL AS CAPTION LABEL A123 NOPRINT OVER
RECAP AR123=IF A123 LE 0 THEN '-' ELSE A123;
AS 'Intercompany Loans' OVER
BAR AS '-' OVER
RECAP ARtotal/D12.0B=AR111 + AR133 + AR1151 + AR1153 + AR1154 + AR1156 + AR228 + AR1157;
AS 'Accounts Receivables'
END
Thanks,
HuaThis message has been edited. Last edited by: Kerry,
Developer Studio 7.6.11 AS400 - V5R4 HTML,PDF,XLS
October 06, 2009, 12:08 PM
Francis Mariani
How about adding a BY statement after the last RECAP?
-* File fml01.fex
TABLE FILE CAR
SUM
RETAIL_COST AS 'Retail'
DEALER_COST AS 'Dealer'
SEATS NOPRINT
SALES NOPRINT
BY COUNTRY PAGE-BREAK
ACROSS SEATS AS ' '
FOR BODYTYPE
'CONVERTIBLE' AS 'CONVERTIBLE' LABEL R1 OVER
'COUPE' AS 'COUPE' LABEL R2 OVER
'HARDTOP' AS 'HARDTOP' LABEL R3 OVER
'ROADSTER' AS 'ROADSTER' LABEL R4 OVER
'SEDAN' AS 'SEDAN' LABEL R5 OVER
'SUV' AS 'SUV' LABEL R6 OVER
'VAN' AS 'VAN' LABEL R7 OVER
RECAP R_TOTAL = R1 + R2 + R3 + R4+ R5 + R6 + R7; AS 'Total' OVER
" " LABEL S02 OVER
RECAP R98 = R1 + R2; AS 'CONV+COUPE' OVER
RECAP R_30DAYS = R_TOTAL - R1 - R2; AS '30 days or more' OVER
RECAP R_90DAYS = R_TOTAL - R1 - R2 - R3; AS '90 days or more' OVER
" " LABEL S02
BY BODYTYPE
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
October 06, 2009, 04:08 PM
Hua
Francis,
Thank you very much for the reply.
I am trying to apply your solution, and managed to display the additional column. But it seems to display all-lowest-level children(AJIFCD) with top-level CAPTION. Like in your example, all body types for SEDAN are displayed instead of only one line for SEDAN Also when I switch to GUI, the BY AJIFCD moved up above the ACROSS fields. Does this has to do with my release 7.6.4?
TABLE FILE YAAJREP
SUM
BSAMT
BY AJIFCD <==== this line move up here by GUI interface
ACROSS IFCSEQ NOPRINT 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 '-' ELSE A111; AS 'CASH' OVER
...
(BY AJIFCD <==== this was added here with text editor)
HEADING
Thanks,
Hua
Developer Studio 7.6.11 AS400 - V5R4 HTML,PDF,XLS
October 06, 2009, 04:16 PM
Hua
I also try using BY AJAHCD(parent of AJAHCD), it showed 2nd-lowest-level AJIFCD, and the CAPTION not quite match the value of AJIFCD, can not explain to myself how they are related yet.
Developer Studio 7.6.11 AS400 - V5R4 HTML,PDF,XLS
October 06, 2009, 05:23 PM
Francis Mariani
I don't think it has anything to do with the version - the GUI was just "correcting" the underlying code.
Yes, my hasty example is cr@p!
Your requirement may not be possible - sorry, I don't think I can help.
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
October 06, 2009, 05:38 PM
Hua
Francis,
Appreciated very much for your help.
Hua
Developer Studio 7.6.11 AS400 - V5R4 HTML,PDF,XLS
October 07, 2009, 08:15 AM
Efrem
I'm not sure if I totally understand your problem but I thought the following documentation might be helpful:
Syntax: How to Retain FOR Values in a Request Note: these commands are allowable only as a compute and do not appear in the drop down list of functions
FMLFOR(‘Ann’) Returns the FOR value associated with each row in an report. If the row was generated as a sum of data records using the OR phrase, FMLFOR will only return the first FOR value specified in the list of values. If the OR phrase was generated by an Hierarchy ADD command, FMLFOR returns the FOR value associated with the parent specified in the ADD command the FOR value associated with each row in an report
FMLCAP (‘Ann’) Returns the CAPTION value associated with each row in an report
FMLLIST (‘Ann’) Returns the FOR statement associated with each row in an report. Appropriate If the row was generated as a sum of data records using the OR phrase.
Ann Alphanumeric Is the length of the field that contains the result, or the format of the output value enclosed in single quotation marks. FMLLIST may be up to 4095 bytes.
Let me know if this works for you.
Efrem
October 07, 2009, 10:18 AM
Hua
Thank you, Efrem.
Very helpful information, and FMLFOR is what I need for my for-value.
My biggest problem is how to place the for-value onto the report. Like Francis suggested, I have attempted to make a sub-group that is parallel to the Across group within the For-value. The examples I have found so far are all in a single group, ie the for-value, caption, actual amount, budget amount...
My ideal outcome would look like the following, which I couldn't hard code each company as individual column value because these companies change from time to time. Unlike the 12-month period budget report that I know for sure there are 12 columns only: