The following bit of code only recalculates the percentage columns correctly for the first ACROSS value. How do I get it to work for all ACROSS values?

TABLE FILE R018H010
SUM
CNT_BAL_AMT                   NOPRINT
CNT_BAL_AMT_30                AS 'Volume'
COMPUTE PCT_CNT_BAL_AMT_30/D4.2 = CNT_BAL_AMT_30 / CNT_BAL_AMT * 100;
                              AS '%'

BAL_AMT                       NOPRINT
BAL_AMT_30                    AS 'Outstanding,Balance'
COMPUTE PCT_BAL_AMT_30/D4.2     = BAL_AMT_30     / BAL_AMT * 100;
                              AS '%'

BY SEGMENT                    NOPRINT
ON SEGMENT                    PAGE-BREAK

FOR DIVISION_NODE_NMY
'Atlantic'                    LABEL D01           OVER
'Quebec'                      LABEL D02           OVER
'Greater Toronto'             LABEL D03           OVER
'Ontario Regional'            LABEL D04           OVER
RECAP ONT_TOTAL = D03 + D04;  AS 'Ontario Total'  OVER
RECAP ONT_TOTAL(3)  = ONT_TOTAL(2) / ONT_TOTAL(1) * 100; OVER
RECAP ONT_TOTAL(6)  = ONT_TOTAL(5) / ONT_TOTAL(4) * 100; OVER
'Prairies'                    LABEL D05           OVER
'British Columbia'            LABEL D06           OVER
RECAP SEG_TOTAL = D01 + D02 + D03 + D04 + D05 + D06;  AS 'Total' OVER
RECAP SEG_TOTAL(3)  = SEG_TOTAL(2) / SEG_TOTAL(1) * 100; OVER
RECAP SEG_TOTAL(6)  = SEG_TOTAL(5) / SEG_TOTAL(4) * 100;

ACROSS TIME_DIM_KEY           NOPRINT
ACROSS MONTH                  AS ''

Thanks.

Francis

I do not know why it is not working but how about first putting the summed data in a hold file, do the pct calculation in the define and then build the FML report.

I tried a stripped down version and it works. But I am using release 7.6.
I would retry this in a more up-to-date release.

With the CAR file:

-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) = (TOTE(2) - TOTE(1)) / TOTE(2) * 100; OVER

'JAPAN'             LABEL CNA1 OVER
'TAIWAN'            LABEL CNA2 OVER
RECAP TOTA    = CNA1 + CNA2; AS 'TOTAL ASIA' OVER
RECAP TOTA(3) = (TOTA(2) - TOTA(1)) / TOTA(2) * 100; OVER

RECAP TOTT    = TOTE + TOTA; AS 'TOTAL' OVER
RECAP TOTT(3) = (TOTT(2) - TOTT(1)) / TOTT(2) * 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

Result:



The Total % for Europe Seats 4 is wrong, as is the Report Total % Seats 4.

This message has been edited. Last edited by: Francis Mariani,

Francis,

The example using carfile is doing exactly what you're telling it to do. It's a coincidence that the % for seats 2 works - simply by default I guess since there's only one row with data.

However, if you use the following for the carfile example, the %'s for all three will work:

-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) = (TOTE(2) - TOTE(1)) / TOTE(2) * 100; OVER
RECAP TOTE(6) = (TOTE(5) - TOTE(4)) / TOTE(5) * 100; OVER
RECAP TOTE(9) = (TOTE(8) - TOTE(7)) / TOTE(8) * 100; OVER

'JAPAN' LABEL CNA1 OVER
'TAIWAN' LABEL CNA2 OVER
RECAP TOTA = CNA1 + CNA2; AS 'TOTAL ASIA' OVER
RECAP TOTA(3) = (TOTA(2) - TOTA(1)) / TOTA(2) * 100; OVER
RECAP TOTA(6) = (TOTA(5) - TOTA(4)) / TOTA(5) * 100; OVER
RECAP TOTA(9) = (TOTA(8) - TOTA(7)) / TOTA(8) * 100; OVER

RECAP TOTT = TOTE + TOTA; AS 'TOTAL' OVER
RECAP TOTT(3) = (TOTT(2) - TOTT(1)) / TOTT(2) * 100; OVER
RECAP TOTT(6) = (TOTT(5) - TOTT(4)) / TOTT(5) * 100; OVER
RECAP TOTT(9) = (TOTT(8) - TOTT(7)) / TOTT(8) * 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

That being said, your first posting actually looks pretty good. I see you're using ONT_TOTAL(6) and SEG_TOTAL(6).

Ken

Ken, I was waiting for the FRL guru to show up!

In your solution, am I correct in assuming you have to know how many ACROSS values there are and code one line per ACROSS value?

In my first posting, the ONT_TOTAL(6) and SEG_TOTAL(6) were there for the second percentage (column 6 of the set of columns in each ACROSS value.

I've seen code using *, something like ONT_TOTAL(6,3,*) to designate the sixth column in the set of columns for ACROSS value, incrementing every three columns, for all the columns - but I haven't been able to make this work either.

Francis,

Why don't you do this:

-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

When using RECAP TOTE(3,*,3) = (TOTE(*-1) - TOTE(*-2)) / TOTE(*-1) * 100; OVER, remember that:

TOTE(3,*,3) means to start at column 3 and go for all columns in increments of 3. The *-1 means to use the column value to the immediate left. *-2 would then mean to use the value two columns to the left. The above should now make it dynamic for you.

Speaking of dynamic, I was playing around some time ago and created an FML report that is totally dynamic in working from a hierarchy. Very little logic and save TONS of coding.

I hope this helps.

It's been a while since I posted. I've been doing mainly ETL's for the past year (and silently going insane might I add). I just happened to look in here today for a problem one of the guys was having.

Ken

Ken, thank you! That works. I just couldn't get the dynamic notation to work.

I guess I was really lucky today that you stopped by.

Thanks again and good luck with all that ETL.

Francis.