Hello there,


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.

Hua

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

The RECAP command creates a new value based on values already identified in the report with LABEL.

Efrem

Efrem,

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

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,

Thanks Francis.

I did modify your car example to simulte the result I wanted in the beginning of this post. Hope it is good enough to show my intension.


Thanks,

Hua

Hua,

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

Thanks Francis.

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

Kerry,

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

Hi Hua,

Thanks a lot for the update and glad to know you have a case opened. Please keep us posted on what you heard from the case and the solution.

Cheers,

Kerry

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

quote:

®.&I.A30

It is supposed a variable amperREG to hold the REGIONs in GGSALES.