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,

Hua

This message has been edited. Last edited by: Kerry,

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,

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

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.

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,

Appreciated very much for your help.

Hua

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

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:

                       Com-1    Com-2   Com-3   
            Account#   sales    sales   sales    Total
Cash         111         100      290     250      640    
Inventory    125         500      660     680     1840 
...

 

Hua