[SOLVED] Two Subtotals calculation

September 13, 2012, 04:39 PM

Hello,

I have report need has two subtotals and Net total is not addition of two subtotal but subtract of one subtotal - other subtotal

For example

Revenue - Expense for each measure fields for final total
Field1 Field2
Rev 20 30
Exp 10 25

Tot 10 5

Can anyone have idea about how can do it.
Using WF 7.7.3, Windows.

THanks.

This message has been edited. Last edited by: Kerry,

In Focus since 1993. WebFOCUS 7.7.03 Win 2003

September 13, 2012, 05:25 PM

Dan Satchell

This example may give you some helpful ideas:

TABLE FILE CAR
 SUM  RETAIL_COST/D8C AS 'Revenue'
 OVER DEALER_COST/D8C AS 'Expense'
 BY CAR
 ON CAR RECAP NET/D8C = RETAIL_COST - DEALER_COST ;
 ON CAR SUBFOOT
  " <+0>Net<+0><NET"
  " "
 ON TABLE SET STYLE *
-INCLUDE ENDEFLT.STY
  TYPE=REPORT, HEADALIGN=BODY, GRID=OFF, $
  TYPE=SUBFOOT, OBJECT=FIELD, JUSTIFY=RIGHT, $
 ENDSTYLE
END

WebFOCUS 7.7.05

September 13, 2012, 05:26 PM

David Briars

In sounds like you have an accounting file, which suggests you have an identifier for what is a revenue item and what is an expense item.

Further, it sounds like you have a requirement to perform inter-row calculations.

If so, you might want to consider Financial Modeling Language (FML):

APP PREPENDPATH IBISAMP
-RUN
TABLE FILE LEDGER
SUM AMOUNT   AS 'Amount'
FOR ACCOUNT
1000 TO 1099 AS 'Revenue' OVER
1100 TO 1199 AS 'Expense' OVER
BAR                       OVER
RECAP NET = R1 - R2; AS 'Net'
END

PAGE 1 
 
        Amount 
Revenue 21,239 
Expense 18,829 
        ------ 
Net      2,410

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

September 13, 2012, 11:09 PM

Vir

Thank you all for your suggestions.

Recap works. Just need formatting now. Thanks again for quick response.