Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     SOLVED: Display numbers in opposite signs for financial reports

Read-Only Read-Only Topic
Go
Search
Notify
Tools
SOLVED: Display numbers in opposite signs for financial reports
 Login/Join
 
Member
posted
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,


WebFOCUS 7.7.3
Windows, All Outputs
 
Posts: 28 | Registered: January 19, 2012Report This Post
Master
posted Hide Post
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
                             ======




Pilot: WebFOCUS 8.2.06 Test: WebFOCUS 8.1.05M Prod: WebFOCUS 8.1.05M Server: Windows Server 2016/Tomcat Standalone Workstation: Windows 10/IE11+Edge Database: Oracle 12c, Netezza, & MS SQL Server 2019 Output: AHTML/XLSX/HTML/PDF/JSCHART Tools: WFDS, Repository Content, BI Portal Designer & ReportCaster
 
Posts: 822 | Registered: April 23, 2003Report This Post
Member
posted Hide Post
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


WebFOCUS 7.7.3
Windows, All Outputs
 
Posts: 28 | Registered: January 19, 2012Report This Post
Virtuoso
posted Hide Post
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.




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

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Master
posted Hide Post
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,
 
Posts: 822 | Registered: April 23, 2003Report This Post
Member
posted Hide Post
Thank you all for the information. The FML examples you posted have been very helpful.


WebFOCUS 7.7.3
Windows, All Outputs
 
Posts: 28 | Registered: January 19, 2012Report This Post
Master
posted Hide Post
Thanks for the follow up.

Thanks also for marking the entry 'Solved', as that will assist others researching FML.
 
Posts: 822 | Registered: April 23, 2003Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     SOLVED: Display numbers in opposite signs for financial reports

Copyright © 1996-2020 Information Builders