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
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
July 07, 2007, 08:17 AM
FrankDutch
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.
Frank
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7
July 07, 2007, 02:04 PM
GCohen
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.
Release 7.6.9 Windows HTML
July 09, 2007, 10:20 AM
Francis Mariani
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
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
July 09, 2007, 03:39 PM
k.lane
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
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
Prod - WF 7.6.4 Unix/Solaris - Self-Service, BI Dashboard, MRE Dev - WF 7.6.4 Unix/Solaris - Self-Service, BI Dashboard, MRE Databases: Oracle 10g, SQL Server 2000, DB2.
July 09, 2007, 03:48 PM
Francis Mariani
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
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
July 09, 2007, 04:00 PM
k.lane
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
Prod - WF 7.6.4 Unix/Solaris - Self-Service, BI Dashboard, MRE Dev - WF 7.6.4 Unix/Solaris - Self-Service, BI Dashboard, MRE Databases: Oracle 10g, SQL Server 2000, DB2.
July 09, 2007, 04:47 PM
Francis Mariani
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.
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