We're trying to develop a financial report using the FML painter and are running into an issue.

We would like to print out a report that shows us an actual $ amount, then the % of Sales, like so:

                                 Actual $       % of Sales
                               -----------------------------
Gross Sales                    10,116,639.53      100.00
  Sales Returns & Deductions     (104,628.75)      (1.00)
  Sales Freight                    (1,327.06)       0.00
  Standard                      5,901,405.93       58.30

We're trying this way, but apparently we can't do a RECAP on a COMPUTEd field.

JOIN
 LEFT_OUTER HOLDHIER.SEG01.CHILD IN HOLDHIER TO MULTIPLE HOLDGLBAL.SEG01.ACCOUNT
 IN HOLDGLBAL AS J0
END

DEFINE FILE HOLDHIER
CUR_BALANCE/D20.2C!D=IF PERIOD EQ '1/2008' THEN CURRENT_PERIOD_BALANCE * (-1) ELSE 0;
PREV_YR_BAL/D20.2C!D=IF PERIOD EQ '1/2007' THEN CURRENT_PERIOD_BALANCE * (-1) ELSE 0;
TOT_SALES/D12.2=IF PERIOD EQ '1/2008' THEN CURRENT_PERIOD_BALANCE * (-1) ELSE 0;
END

TABLE FILE HOLDHIER
SUM
     CUR_BALANCE/D20.2CB AS 'Actual $'
     COMPUTE CUR_PCT_SALES/D20.2B% = CUR_BALANCE / TOT_SALES * 100;
 AS '% of Sales'
FOR
     CHILD
'170GRSSLS' ADD ALL AS CAPTION LABEL R1 OVER
'175SLSRTN' ADD ALL AS CAPTION LABEL R2 OVER
RECAP R3(1)=R2(1);
R3(2)=R3(1) / R1(1) * 100;
 AS ' '
WHERE ( YEAR EQ 2008 ) AND ( MONTH EQ 1 );
WHERE LOCATION EQ 'Paris Plant';

Does anyone know of a way to do this?

Shane

Shane,

Of course you can RECAP on a computed field as this example shows. I have only included a RECAP for one LABEL.

TABLE FILE GGSALES
SUM DOLLARS
    BUDDOLLARS
    COMPUTE PCT_BUDGET/D8.2B = 0;
FOR REGION
 'West'       LABEL L1 AS 'West'       OVER
 'Southeast'  LABEL L2 AS 'South East' OVER
 'Northeast'  LABEL L3 AS 'North East' OVER
 'Midwest'    LABEL L4 AS 'Midwest'    OVER
RECAP L1(3) = L1(1) / L1(2) * 100;
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE NOLEAD
END

However, that is not your real problem, which is that you have missed out an OVER and RECAP syntax from your code -

TABLE FILE HOLDHIER
SUM CUR_BALANCE/D20.2CB                                            AS 'Actual $'
    COMPUTE CUR_PCT_SALES/D20.2B% = CUR_BALANCE / TOT_SALES * 100; AS '% of Sales'
FOR CHILD
  '170GRSSLS' ADD ALL AS CAPTION LABEL R1 OVER
  '175SLSRTN' ADD ALL AS CAPTION LABEL R2 OVER
  RECAP     R3(1)=R2(1);                **OVER**
**RECAP**   R3(2)=R3(1) / R1(1) * 100; AS ' '
WHERE ( YEAR EQ 2008 ) AND ( MONTH EQ 1 );
WHERE LOCATION EQ 'Paris Plant';
etc. ....

T

Tony,

Thanks for the reply.

When I apply this to the code the results, I get some weird results. Here's what it looks like in the Matrix tab...

                             Actual $ % of Sales 
Gross Sales             10,116,639.53    100.00% 
Sales Returns and Deductions   (1.03) 

It appears to completely ignore my 3rd row R3(1)=R2(1).

What I thought I could do is display my Gross Sales from the hierarchy in row 1. Then, display some other accounts in later rows and make them invisible. Then, make a RECAP row and reference the previous row's value in column 1 and do a calculation of that row, column 1 divided by row 1 column 1 * 100 to get my percent of sales for that row.

Am I going about this completely wrong?

Shane

Shane,

Did you put the missing OVER and RECAP? Just checking

It is always difficult to assist without physical data (even test data) or knowing the actual synonym. The best option is to use one of the IB supplied sample files. In your case I would be inclined to use GGSALES.

It is easier if you can mimic the problem you have in a fex against one of the sample databases like GGSALES. Because each installationshould have access to these we can see exactly what is going on and can advise more readily.

T

post 2000

This message has been edited. Last edited by: Tony A,

Tony,

Yeah, I put the missing OVER and RECAP...

I'll give it a shot with a sample file...

Shane

All:

I got this to work...The worst part about this is that for every 1 row I want to display on the report, I have to do a TAG and a RECAP...Which is rather time consuming and a hastle, but you work with what you got...Here's the code:

JOIN
 LEFT_OUTER HOLDHIER2.SEG01.CHILD IN HOLDHIER2 TO MULTIPLE HOLDGLBAL.SEG01.ACCOUNT
 IN HOLDGLBAL AS J0
END

SET CNOTATION = EXPLICIT

DEFINE FILE HOLDHIER2
CUR_BALANCE/D20.2CB=IF PERIOD EQ '1/2008' THEN CURRENT_PERIOD_BALANCE * (-1) ELSE 0;
PREV_YR_BAL/D20.2CB=IF PERIOD EQ '1/2007' THEN CURRENT_PERIOD_BALANCE * (-1) ELSE 0;
END

TABLE FILE HOLDHIER2
-*&PLTCOMM.EVAL WHERE LOCATION EQ '&PLANT';
SUM
     CUR_BALANCE AS 'Actual $'
     COMPUTE TOT_CUR_SALES/D20.2CB = IF FMLFOR('A45V') EQ '170GRSSLS' THEN CUR_BALANCE ELSE 0; NOPRINT
     COMPUTE PCT_SALES/D20.2% = CUR_BALANCE / C02 * 100; AS '% of Sales'
     PREV_YR_BAL AS 'Last Year $'
     COMPUTE TOT_PRV_SALES/D20.2CB = IF FMLFOR('A45V') EQ '170GRSSLS' THEN PREV_YR_BAL ELSE 0; NOPRINT
     COMPUTE PCT_SALES2/D20.2% = PREV_YR_BAL / C05 * 100; AS '% of Sales'
     COMPUTE DIFF/D20.2CB = C01 - C04; AS 'Difference $'
FOR
     CHILD
'170GRSSLS' ADD ALL AS CAPTION LABEL R1 WHEN EXISTS OVER
'175SLSRTN' ADD ALL AS CAPTION LABEL R2 NOPRINT OVER
RECAP R3(1)=R2;
R3(2)=R1;
R3(3)/D12.2%=R3(1) / R3(2) * 100;
R3(4)=R2;
R3(5)=R1;
R3(6)=R3(4) / R3(5) * 100;
R3(7)=R3(1) - R3(4);
 AS 'Sales Returns and Deductions' OVER
'180SLSFRT' ADD ALL AS CAPTION LABEL R4 NOPRINT OVER
RECAP R5(1)=R4;
R5(2)=R1;
R5(3)/D20.2%=R5(1) / R5(2) * 100;
R5(4)=R4;
R5(5)=R1;
R5(6)/D20.2%=R5(4) / R5(5) * 100;
R5(7)=R5(1) - R5(4);
 AS 'Sales Freight' OVER
...

Hope this is useful to someone else.

Shane