Hi,

maybe somebody can share a good source code of financial report exaple?

...examples are the fastest way to be familiar with the subject

Your wish etc. etc. etc. An example with annotation (trying to) describing what is happening - from the TA book of samples :lol

Apologies if this exceeds the screen width of your monitor - I will edit later if necessary.

Warning - hand coded version only - will not open in FML painter

T

-* 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
END

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

FYI ... There are a couple of tutorials in the FML doc: Creating Financial Reports Version 7 Release 1 DN4500710.0505