I am developing an income statement report. It has a revenue section and an expense section. Revenue numbers are generally positive. Expense numbers are generally negative but there are exceptions. The requirement is that expense numbers need to be displayed with their opposite signs. For example, -100 should be displayed as 100, and 100 should be displayed as -100.

Here is an example:

Revenue 200
    Sales 150
    Other Revenue 50
Expense 150
    Admin cost 100
    Other cost 60
    One Time Credit -10

Profit 50

In the example, all numbers in expense are actually negative except for "One time credit".

I know I can use a define field to achieve this. However, I also need to be able to sum both revenue and expense to get to profit. Using a define field to flip the sign will not give me the correct profit.

I tried using FML, but my actual requirements are a lot more complex and I realized that I cannot use it.

My current workaround is to use hold files for different sections of the report: one for revenue, one for expense, and another for profit. It is very tedious. Is there an easier way to achieve this?

This message has been edited. Last edited by: JC Zhu,

The following code running against the IB sample database LEDGER...

-* File FRLExample.fex
APP PREPENDPATH IBISAMP
-RUN
DEFINE FILE LEDGER
-* Credits to be treated as a negative number.  
 NEWAMOUNT/I6C = IF ACCOUNT EQ '1200' THEN AMOUNT * (-1) ELSE AMOUNT; 
END
TABLE FILE LEDGER
"Income Statement"
SUM     NEWAMOUNT         AS 'Amount'
COMPUTE COLUMN2/I6CS = 0; AS ''
-*
FOR ACCOUNT
101$ AS 'Sales'         LABEL SALES      OVER
102$ AS 'Other Revenue' LABEL OTHERREV   OVER
-* 
RECAP UNLINE1(1)/A6 = '------';             AS ''              OVER
RECAP TOTALREV(2) = SALES(1) + OTHERREV(1); AS 'Total Revenue' OVER
-*
103$ AS 'Admin Expense'   LABEL ADMINEXP  OVER
11$$ AS 'Other Expenses'  LABEL OTHEREXP  OVER
12$$ AS 'One Time Credit' LABEL CREDITS   OVER
-*
RECAP UNLINE2(1)/A6 = '------';                             AS ''               OVER
RECAP TOTALEXP(2) = ADMINEXP(1) + OTHEREXP(1) + CREDITS(1); AS 'Total Expenses' OVER
-*
RECAP UNLINE3(2)/A6 = '------';                             AS ''               OVER
RECAP NETINC(2) = TOTALREV - TOTALEXP;                      AS 'Net Income'     OVER
RECAP UNLINE4(2)/A6 = '======';                             AS '' 
-*           
ON TABLE SET ONLINE-FMT STANDARD
END  

..returns...

1 PAGE     1
  
  Income Statement
                    Amount                                                      
                    ------                                                      
  Sales              8,784         
  Other Revenue      4,494         
                    ------         
  Total Revenue              13,278
  Admin Expense      7,961         
  Other Expenses    18,829         
  One Time Credit  -27,307         
                    ------         
  Total Expenses               -517
                             ------
  Net Income                 13,795
                             ======

Thanks for the example. I have added an ACROSS to better reflect my requirements. See following modified code.

This gets me a lot closer to what I am looking for. However, I still have 2 more requirements:

1. I need to add 2 more columns to the right. The first column should have the average of all the years. The second column should have the percentage difference between the most recent year (1987) and the average.
2. I need to have the 2 sub total lines above detail lines. For instance, "Total Revenue" needs to be above "Sales" and "Other Revenue".

Can those be done?


TABLE FILE LEDGER
SUM
NEWAMOUNT AS ''
ACROSS LOWEST YEAR AS ''
FOR
ACCOUNT
'101$' AS ' Sales' LABEL SALES OVER
'102$' AS ' Other Revenue' LABEL OTHERREV OVER
RECAP TOTALREV=SALES + OTHERREV;
AS 'Total Revenue' OVER
RECAP UNLINE1/A6='------';
AS '' OVER
'103$' AS ' Admin Expense' LABEL ADMINEXP OVER
'11$$' AS ' Other Expenses' LABEL OTHEREXP OVER
'12$$' AS ' One Time Credit' LABEL CREDITS OVER
RECAP TOTALEXP=ADMINEXP + OTHEREXP + CREDITS;
AS 'Total Expenses' OVER
RECAP UNLINE3/A6='------';
AS '' OVER
RECAP NETINC=TOTALREV - TOTALEXP;
AS 'Net Income' OVER
RECAP UNLINE4/A6='======';
AS ''
HEADING
"Income Statement"
ON TABLE NOTOTAL

END

It can be done and it is all in the documentation.
You can set different calculations for each field, just as you can do in excel, it needs however a bit of study and trial .
We have made a highly dynamic reporting based on the FML system and with drill downs and fancy make-up.

Updates:
1. Subtotals move to beginning of report section.
2. Total and average of years columns added.

-* File FRLExample2.fex
APP PREPENDPATH IBISAMP
-SET &TWOYEARSAGO = '1985';
-SET &LASTYEAR    = '1986';
-SET &CURRYEAR    = '1987';
-*
DEFINE FILE LEDGER
-* Credits to be treated as a negative number.
 NEWAMOUNT/I6C = IF ACCOUNT EQ '1200' THEN AMOUNT * (-1) ELSE AMOUNT;
-* Create data columns.
 TWOYEARSAGO/I6C = IF YEAR EQ '&TWOYEARSAGO' THEN NEWAMOUNT ELSE 0;
 LASTYEAR/I6C    = IF YEAR EQ '&LASTYEAR'    THEN NEWAMOUNT ELSE 0;
 CURRYEAR/I6C    = IF YEAR EQ '&CURRYEAR'    THEN NEWAMOUNT ELSE 0;
END
-*
TABLE FILE LEDGER
"---- Income Statement ----"
SUM     TWOYEARSAGO AS 'Two Years Ago'
        LASTYEAR    AS 'Last Year'
        CURRYEAR    AS 'Current Year'
COMPUTE TOTALYEARS/I6CS = (TWOYEARSAGO + LASTYEAR + CURRYEAR); AS 'Year Total'
COMPUTE COLUMN2/I6CS = TOTALYEARS / 3; AS 'Year Average'
-*
FOR ACCOUNT
"---- Revenue ----" OVER
RECAP TOTALREV = SALES + OTHERREV; AS 'Total Revenue' OVER
BAR AS '=' OVER
-*
101$ AS 'Sales'         LABEL SALES      OVER
102$ AS 'Other Revenue' LABEL OTHERREV   OVER
-*
" " OVER
-*
"---- Expenses ----" OVER
RECAP TOTALEXP = ADMINEXP + OTHEREXP + CREDITS; AS 'Total Expenses' OVER
BAR AS '=' OVER
103$ AS 'Admin Expense'   LABEL ADMINEXP  OVER
11$$ AS 'Other Expenses'  LABEL OTHEREXP  OVER
1200 AS 'One Time Credit' LABEL CREDITS   OVER
-*
" " OVER
-*
"---- Net ----" OVER
RECAP NETINC = TOTALREV - TOTALEXP; AS 'Net Income'   
-*
ON TABLE SET ONLINE-FMT STANDARD
END

1 PAGE     1
  
  ---- Income Statement ----
                  Two Years Ago Last Year Current Year Year Total Year Average  
                  ------------- --------- ------------ ---------- ------------  
  ---- Revenue ----
  Total Revenue           2,303     2,975        8,000     13,278        4,426
                        =======   =======      =======    =======      =======
  Sales                   1,684     2,100        5,000      8,784        2,928
  Other Revenue             619       875        3,000      4,494        1,498
   
  ---- Expenses ----
  Total Expenses            901     1,200       -2,618       -517         -173
                        =======   =======      =======    =======      =======
  Admin Expense           3,360     4,026          575      7,961        2,653
  Other Expenses          5,295     6,250        7,284     18,829        6,276
  One Time Credit        -7,754    -9,076      -10,477    -27,307       -9,102
   
  ---- Net ----
  Net Income              1,402     1,775       10,618     13,795        4,599

This message has been edited. Last edited by: David Briars,

Thank you all for the information. The FML examples you posted have been very helpful.

Thanks for the follow up.

Thanks also for marking the entry 'Solved', as that will assist others researching FML.