[SOLVED] Display difference of 2 subtotals in 3rd subtotal
Hello,
My report has 3 subtotals - I am trying to show difference of 1st two subtotal amounts as 3rd subtotal.. I have a computed field to do that but I can't get the difference in the 3rd subtotal.. I just get the sum ..
Can anyone help me out here?
Thanks in advance!This message has been edited. Last edited by: FP Mod Chuck,
WF 7.7.03, Win 7
July 28, 2017, 10:24 AM
BabakNYC
I'm not sure if I understand where the issue is. Doesn't this give you the right results?
TABLE FILE CAR
SUM RETAIL DEALER
COMPUTE DIFF/D12.2=RETAIL-DEALER;
BY COUNTRY
BY CAR
ON COUNTRY SUB-TOTAL
END
WebFOCUS 8206, Unix, Windows
July 28, 2017, 10:31 AM
Nova27
This doesn't work for me since I do not want to display my computed field. Also, I have 2 defined fields (which I do not display) and my computed fields is difference between these 2 defined fields.
For example:
DEFINE FILE SQLOUT
CR_AMOUNT/D20.2 = IF T_TYPE EQ 'CR' THEN AMOUNT;
DB_AMOUNT/D20.2 = IF T_TYPE EQ 'DB' THEN AMOUNT;
END
TABLE FILE SQLOUT
PRINT
...
.....
COMPUTE DIFF/D20.2 = CR_AMOUNT - DB_AMOUNT; NOPRINT
.....
BY SORTFIELD1 NOPRINT
BY SORTFIELD2 NOPRINT
BY SORTFIELD3 NOPRINT
ON SORTFIELD1 SUBTOTAL DIFF AS 'Total: '
.........
........
So I can't get the subtotal to display the difference.
WF 7.7.03, Win 7
July 28, 2017, 10:34 AM
BabakNYC
TABLE FILE CAR
SUM RETAIL DEALER
COMPUTE DIFF/D12.2=RETAIL-DEALER; NOPRINT
BY COUNTRY
BY CAR
ON COUNTRY SUBHEAD
"SUBTOTAL FOR THE DIFFERENCE FOR <COUNTRY IS: <ST.DIFF"
END
WebFOCUS 8206, Unix, Windows
July 28, 2017, 10:50 AM
Nova27
Anyway I don't have to use SUBHEAD? I would really like it to be as a SUBTOTAL line.. Using SUBHEAD changes the report formatting and display and unfortunately that won't be accepted by the report users...
WF 7.7.03, Win 7
July 28, 2017, 11:06 AM
BabakNYC
TABLE FILE CAR
SUM
DEALER_COST
RETAIL_COST
COMPUTE DIFF/D12.2 = RETAIL_COST- DEALER_COST;
BY COUNTRY
BY CAR
ON COUNTRY SUBTOTAL AS 'Subtotal and the difference'
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
TYPE=DATA,
COLUMN=N5,
COLOR='WHITE',
$
TYPE=TITLE,
COLUMN=N5,
COLOR='WHITE',
$
ENDSTYLE
END
WebFOCUS 8206, Unix, Windows
July 28, 2017, 12:53 PM
Doug
I don't see the "ON SORTFIELD2 SUBTOTAL" or 'ON SORTFIELD3 SUBTOTAL" lines in your code...
TABLE FILE SQLOUT
PRINT
...
.....
COMPUTE DIFF/D20.2 = CR_AMOUNT - DB_AMOUNT; NOPRINT
.....
BY SORTFIELD1 NOPRINT
BY SORTFIELD2 NOPRINT
BY SORTFIELD3 NOPRINT
ON SORTFIELD1 SUBTOTAL DIFF AS 'Total: '
.........
........
In FOCUS Since 1983 ~ from FOCUS to WebFOCUS. Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
July 30, 2017, 07:00 AM
Danny-SRL
Nova, If I understand correctly, you want to show the difference only at the SUBTOTAL level. The problem is that if you NOPRINT the difference, the SUBTOTAL does not appear, which is logical. Here is a workaround...
-* File Nova27_02.fex
TABLE FILE CAR
SUM RCOST DCOST
COMPUTE DIFF/D6=RCOST - DCOST;
BY COUNTRY
BY CAR
ON COUNTRY SUBTOTAL
ON TABLE SET STYLE *
INCLUDE=ENDEFLT, $
TYPE=DATA, COLUMN=DIFF, SIZE=1,$
ENDSTYLE
END
Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF
July 30, 2017, 12:05 PM
BabakNYC
Same concept as my suggestion except I make the COMPUTED field data and title white effectively making it invisible on a white background.
WebFOCUS 8206, Unix, Windows
July 31, 2017, 07:26 AM
Tony A
The only downside of both the above solutions is that the data is still displayed.
If your output type is HTML then you can always use CSS styling to control the display. See this post from 2008.
T
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
