-* File fml_example.fex SET ASNAMES = ON SET HOLDLIST = PRINTONLY SET PAGE-NUM = NOPAGE -SET &WFFMT = 'HTML'; -* Redefining some fields that can be used to provide financial data -* across a varied time frame such as year, quarter and month. DEFINE FILE GGSALES SYEAR/YY = DATE; SMNTH/M = DATE; SQTR/Q = DATE; -* This is not always necessary but it does allow a refined control -* of the data rows without complicated code. EMR_TAG/A3 = EDIT(SQTR) | EDIT(SMNTH); END -* All this is doing is providing a file with summations at two different levels. TABLE FILE GGSALES SUM DOLLARS/D12!L AS MNTH_REV BY SYEAR BY SQTR BY SMNTH SUM EMR_TAG DOLLARS/D12!L AS REVENUE UNITS BY SYEAR BY SQTR BY SMNTH BY REGION BY ST WHERE SYEAR FROM 1990 TO 1999 ON TABLE HOLD AS EMRDATA END -* The theory of the EMR (or FML) code is akin to a spreadsheet. -* WebFOCUS builds an internal matrix, including non displayable columns, -* from which to build the report. -* -* Each cell in the matrix obviously holds a single value and is related to -* other data in that row. -* -* One important thing to remember is that WebFOCUS does not calculate all field values -* as it builds the matrix, rather it includes fields from which the end calculations -* are built. -* Therefore if you have a compute using three other fields to ascertain the value of your compute -* then all three fields plus the new, computed, field will be included in the matrix. -* This knowledge becomes important when you start to build your inner calculations where you -* reference particular cells. -* TABLE FILE EMRDATA SUM REVENUE AS 'Revenue' MAX.MNTH_REV AS 'Total Revenue,All Regions' COMPUTE PCT_CONT/D6.1% = REVENUE / MAX.MNTH_REV * 100; AS '% Contribution to,Monthly Total,Revenue' BY REGION PAGE-BREAK ACROSS HIGHEST SYEAR AS '' -* If you take the code so far you will have in your matrix a set of cells that can be used -* to build the report data. -* The cells in a row will consist of :- -* REGION -* SYEAR.REVENUE -* SYEAR.MAX.MNTH_REV -* SYEAR.PCT_CONT -* You will notice that the second through fourth columns are prefixed with SYEAR which is -* the across field. Because of the ACROSS verb we end up with a virtual array -* (one that cannot be referenced as a normal array!) -* The EMR control verb is FOR and is used to match incoming values to calculate the row in the matrix -* within which the recordset is to be contained (using SUM in this case). FOR EMR_TAG '101' AS 'Jan' LABEL Q1JAN OVER '102' AS 'Feb' LABEL Q1FEB OVER '103' AS 'Mar' LABEL Q1MAR OVER -* This is where the EMR strength lies, the ability to reference individual cells within the -* report matrix, because we LABEL the rows of data and the columns are referenced using -* notation from 0 to max number of cells in a row. RECAP Q1TOT = Q1JAN + Q1FEB + Q1MAR; AS 'Q1 Totals' LABEL Q1TOT OVER -* Here we are saying the contents of column 3 in row Q1TOT are to be recalculated -* once the matrix has been filled with the incoming data, and it is to be given -* the value of the contents of column 1 divided by column 2 multiplied by 100 -* with all references being restricted to the same row. -* This equates to PCT_CONT = REVENUE / MAX.MNTH_REV * 100; on the total line and -* effectively gives us a recalculation on the row as opposed to the summation of the -* values in the column from the rows above. RECAP Q1TOT(3) = Q1TOT(1) / Q1TOT(2) * 100; OVER RECAP Q1TOT(6) = Q1TOT(4) / Q1TOT(5) * 100; OVER RECAP Q1TOT(9) = Q1TOT(7) / Q1TOT(8) * 100; OVER RECAP Q1TOT(12) = Q1TOT(10) / Q1TOT(11) * 100; OVER '204' AS 'Apr' LABEL Q2APR OVER '205' AS 'May' LABEL Q2MAY OVER '206' AS 'Jun' LABEL Q2JUN OVER RECAP Q2TOT = Q2APR + Q2MAY + Q2JUN; AS 'Q2 Totals' LABEL Q2TOT OVER RECAP Q2TOT(3) = Q2TOT(1) / Q2TOT(2) * 100; OVER RECAP Q2TOT(6) = Q2TOT(4) / Q2TOT(5) * 100; OVER RECAP Q2TOT(9) = Q2TOT(7) / Q2TOT(8) * 100; OVER RECAP Q2TOT(12) = Q2TOT(10) / Q2TOT(11) * 100; OVER '307' AS 'Jul' LABEL Q3JUL OVER '308' AS 'Aug' LABEL Q3AUG OVER '309' AS 'Sep' LABEL Q3SEP OVER RECAP Q3TOT = Q3JUL + Q3AUG + Q3SEP; AS 'Q3 Totals' LABEL Q3TOT OVER RECAP Q3TOT(3) = Q3TOT(1) / Q3TOT(2) * 100; OVER RECAP Q3TOT(6) = Q3TOT(4) / Q3TOT(5) * 100; OVER RECAP Q3TOT(9) = Q3TOT(7) / Q3TOT(8) * 100; OVER RECAP Q3TOT(12) = Q3TOT(10) / Q3TOT(11) * 100; OVER '410' AS 'Oct' LABEL Q4OCT OVER '411' AS 'Nov' LABEL Q4NOV OVER '412' AS 'Dec' LABEL Q4DEC OVER RECAP Q4TOT = Q4OCT + Q4NOV + Q4DEC; AS 'Q4 Totals' LABEL Q4TOT OVER RECAP Q4TOT(3) = Q4TOT(1) / Q4TOT(2) * 100; OVER RECAP Q4TOT(6) = Q4TOT(4) / Q4TOT(5) * 100; OVER RECAP Q4TOT(9) = Q4TOT(7) / Q4TOT(8) * 100; OVER RECAP Q4TOT(12) = Q4TOT(10) / Q4TOT(11) * 100; OVER -* This section takes the flexibility of EMR one step further than the usage above -* by using the values from the RECAP calculation rather than the initial values -* used to populate the matrix. RECAP YRTOT = Q1TOT + Q2TOT + Q3TOT + Q4TOT; AS 'Year Totals' LABEL YRTOT OVER RECAP YRTOT(3) = YRTOT(1) / YRTOT(2) * 100; OVER RECAP YRTOT(6) = YRTOT(4) / YRTOT(5) * 100; OVER RECAP YRTOT(9) = YRTOT(7) / YRTOT(8) * 100; OVER RECAP YRTOT(12) = YRTOT(10) / YRTOT(11) * 100; OVER "" ON TABLE SET HTMLCSS ON ON TABLE SET ONLINE-FMT &WFFMT ON TABLE SET STYLE * -* Centralise the Year with bold style and no border TYPE=ACROSSVALUE, ACROSS=1, JUSTIFY=CENTER, STYLE=BOLD, BORDER=OFF, $ -* To maintain the styling across all components of the spreadsheet, including total lines, -* we need to apply styling to each label. -* This seems over the top but some styling disappears if you do not individually specify -* a style for each conditional line. TYPE=DATA, LABEL=Q1JAN, ACROSSCOLUMN=PCT_CONT, COLOR='RED', WHEN=PCT_CONT LT 25, $ TYPE=DATA, LABEL=Q1FEB, ACROSSCOLUMN=PCT_CONT, COLOR='RED', WHEN=PCT_CONT LT 25, $ TYPE=DATA, LABEL=Q1MAR, ACROSSCOLUMN=PCT_CONT, COLOR='RED', WHEN=PCT_CONT LT 25, $ TYPE=DATA, LABEL=Q2APR, ACROSSCOLUMN=PCT_CONT, COLOR='RED', WHEN=PCT_CONT LT 25, $ TYPE=DATA, LABEL=Q2MAY, ACROSSCOLUMN=PCT_CONT, COLOR='RED', WHEN=PCT_CONT LT 25, $ TYPE=DATA, LABEL=Q2JUN, ACROSSCOLUMN=PCT_CONT, COLOR='RED', WHEN=PCT_CONT LT 25, $ TYPE=DATA, LABEL=Q3JUL, ACROSSCOLUMN=PCT_CONT, COLOR='RED', WHEN=PCT_CONT LT 25, $ TYPE=DATA, LABEL=Q3AUG, ACROSSCOLUMN=PCT_CONT, COLOR='RED', WHEN=PCT_CONT LT 25, $ TYPE=DATA, LABEL=Q3SEP, ACROSSCOLUMN=PCT_CONT, COLOR='RED', WHEN=PCT_CONT LT 25, $ TYPE=DATA, LABEL=Q4OCT, ACROSSCOLUMN=PCT_CONT, COLOR='RED', WHEN=PCT_CONT LT 25, $ TYPE=DATA, LABEL=Q4NOV, ACROSSCOLUMN=PCT_CONT, COLOR='RED', WHEN=PCT_CONT LT 25, $ TYPE=DATA, LABEL=Q4DEC, ACROSSCOLUMN=PCT_CONT, COLOR='RED', WHEN=PCT_CONT LT 25, $ -* When if comes to total lines then the same styling must be specified for each row. TYPE=DATA, LABEL=Q1TOT, ACROSSCOLUMN=PCT_CONT, COLOR='RED', BORDER-TOP=LIGHT, BORDER-BOTTOM=LIGHT, BORDER-TOP-COLOR=BLACK, BORDER-BOTTOM-COLOR=BLACK, BACKCOLOR=RGB(210 210 210), WHEN=PCT_CONT LT 25, $ TYPE=DATA, LABEL=Q2TOT, ACROSSCOLUMN=PCT_CONT, COLOR='RED', BORDER-TOP=LIGHT, BORDER-BOTTOM=LIGHT, BORDER-TOP-COLOR=BLACK, BORDER-BOTTOM-COLOR=BLACK, BACKCOLOR=RGB(210 210 210), WHEN=PCT_CONT LT 25, $ TYPE=DATA, LABEL=Q3TOT, ACROSSCOLUMN=PCT_CONT, COLOR='RED', BORDER-TOP=LIGHT, BORDER-BOTTOM=LIGHT, BORDER-TOP-COLOR=BLACK, BORDER-BOTTOM-COLOR=BLACK, BACKCOLOR=RGB(210 210 210), WHEN=PCT_CONT LT 25, $ TYPE=DATA, LABEL=Q4TOT, ACROSSCOLUMN=PCT_CONT, COLOR='RED', BORDER-TOP=LIGHT, BORDER-BOTTOM=LIGHT, BORDER-TOP-COLOR=BLACK, BORDER-BOTTOM-COLOR=BLACK, BACKCOLOR=RGB(210 210 210), WHEN=PCT_CONT LT 25, $ TYPE=DATA, LABEL=YRTOT, ACROSSCOLUMN=PCT_CONT, COLOR='RED', BORDER-TOP=LIGHT, BORDER-BOTTOM=LIGHT, BORDER-TOP-COLOR=BLACK, BORDER-BOTTOM-COLOR=BLACK, BACKCOLOR=RGB(210 210 210), WHEN=PCT_CONT LT 25, $ -* The border lines disappear when a "normal" data value above the total line is styled red -* so we force the styling when the non red styling is to be used TYPE=DATA, LABEL=Q1TOT, ACROSSCOLUMN=PCT_CONT, BORDER-TOP=LIGHT, BORDER-BOTTOM=LIGHT, BORDER-TOP-COLOR=BLACK, BORDER-BOTTOM-COLOR=BLACK, BACKCOLOR=RGB(210 210 210), WHEN=PCT_CONT GE 25, $ TYPE=DATA, LABEL=Q2TOT, ACROSSCOLUMN=PCT_CONT, BORDER-TOP=LIGHT, BORDER-BOTTOM=LIGHT, BORDER-TOP-COLOR=BLACK, BORDER-BOTTOM-COLOR=BLACK, BACKCOLOR=RGB(210 210 210), WHEN=PCT_CONT GE 25, $ TYPE=DATA, LABEL=Q3TOT, ACROSSCOLUMN=PCT_CONT, BORDER-TOP=LIGHT, BORDER-BOTTOM=LIGHT, BORDER-TOP-COLOR=BLACK, BORDER-BOTTOM-COLOR=BLACK, BACKCOLOR=RGB(210 210 210), WHEN=PCT_CONT GE 25, $ TYPE=DATA, LABEL=Q4TOT, ACROSSCOLUMN=PCT_CONT, BORDER-TOP=LIGHT, BORDER-BOTTOM=LIGHT, BORDER-TOP-COLOR=BLACK, BORDER-BOTTOM-COLOR=BLACK, BACKCOLOR=RGB(210 210 210), WHEN=PCT_CONT GE 25, $ TYPE=DATA, LABEL=YRTOT, ACROSSCOLUMN=PCT_CONT, BORDER-TOP=LIGHT, BORDER-BOTTOM=LIGHT, BORDER-TOP-COLOR=BLACK, BORDER-BOTTOM-COLOR=BLACK, BACKCOLOR=RGB(210 210 210), WHEN=PCT_CONT GE 25, $ -* The general style for the total lines, black borders top and bottom with a grey background. TYPE=DATA, LABEL=Q1TOT, BORDER-TOP=LIGHT, BORDER-BOTTOM=LIGHT, BORDER-TOP-COLOR=BLACK, BORDER-BOTTOM-COLOR=BLACK, BACKCOLOR=RGB(210 210 210), $ TYPE=DATA, LABEL=Q2TOT, BORDER-TOP=LIGHT, BORDER-BOTTOM=LIGHT, BORDER-TOP-COLOR=BLACK, BORDER-BOTTOM-COLOR=BLACK, BACKCOLOR=RGB(210 210 210), $ TYPE=DATA, LABEL=Q3TOT, BORDER-TOP=LIGHT, BORDER-BOTTOM=LIGHT, BORDER-TOP-COLOR=BLACK, BORDER-BOTTOM-COLOR=BLACK, BACKCOLOR=RGB(210 210 210), $ TYPE=DATA, LABEL=Q4TOT, BORDER-TOP=LIGHT, BORDER-BOTTOM=LIGHT, BORDER-TOP-COLOR=BLACK, BORDER-BOTTOM-COLOR=BLACK, BACKCOLOR=RGB(210 210 210), $ TYPE=DATA, LABEL=YRTOT, BORDER-TOP=LIGHT, BORDER-BOTTOM=LIGHT, BORDER-TOP-COLOR=BLACK, BORDER-BOTTOM-COLOR=BLACK, BACKCOLOR=RGB(210 210 210), $ ENDSTYLE ENDThis message has been edited. Last edited by: Tony A,
In FOCUS since 1986 | WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2 | ||
WebFOCUS App Studio 8.2.06 standalone on Windows 10 |