My report has a grand total line. For the most part it works EXCEPT there is a define that does division: column A / column C, the value in the grand total is not doing the math based on the grand total column A / grand total column C.
How do I fix this?This message has been edited. Last edited by: FP Mod Chuck,
WebFOCUS 8206.08 Windows, All Outputs
September 25, 2018, 08:14 AM
BabakNYC
Please show us your syntax. Are you seeing wacky numbers or is it a rounding problem? What's the format of the DEFINEd field and the two columns?
WebFOCUS 8206, Unix, Windows
September 25, 2018, 08:41 AM
RobertF
Here is the define; it works fine on every detail line. I simply clicked the grand totals icon to get totals...I checked, instead of doing the division on the grand total line, it is simply addng all the percenatges.
You may also need to use COMPUTE. RECOMPUTE is applied to a COMPUTEd field not a DEFINEd field. A DEFINEd field will only be summarized (total).
See below sample's result
DEFINE FILE CAR
DEF_RET /P10.2 = (RETAIL_COST - DEALER_COST) / DEALER_COST * 100;
END
TABLE FILE CAR
SUM RETAIL_COST
DEALER_COST
DEF_RET
COMPUTE CMP_RET /P10.2 = (RETAIL_COST - DEALER_COST) / DEALER_COST * 100;
BY COUNTRY
ON COUNTRY RECOMPUTE AS 'SUB-TOTAL'
BY CAR
ON TABLE RECOMPUTE AS 'GRAND-TOTAL'
END
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007
September 26, 2018, 09:21 AM
RobertF
Ok, still just adding everything up. Perhaps you can clarify. The link BabaNYC provided seems to indicate the Recompute on the grand total will ONLY work if there is one at the subtotal level. Well first, I do not want a subtotal...just detail and a grand total. Nevertheless I added one...but my Grand Total is still adding everything.
I added this as my lowest sort (it essentially produces a row similar to the detail): ON HOLDACTBNCHFTEFINAL.SEG01.BENCHMARKVISITS RECOMPUTE AS 'SUB-TOTAL'
I added this: ON TABLE RECOMPUTE COLUMN-TOTAL AS 'TOTAL'
I am still doing something wrong.
My code now looks similar to the CAR example except my subtotal would be after the BY CAR level...and I really would not want to print it; ie
BY CAR ON CAR RECOMPUTE AS 'SUB TTL' HEADING "My Report Title" ON TABLE SET PAGE-NUM NOLEAD ON TABLE SET BYDISPLAY ON ON TABLE RECOMPUTE COLUMN-TOTAL AS 'TOTAL'
ON TABLE RECOMPUTE AS 'GRAND TTL'
WebFOCUS 8206.08 Windows, All Outputs
September 26, 2018, 09:42 AM
BabakNYC
Get rid of COLUMN-TOTAL in the bottom of the page.
TABLE FILE CAR
SUM DEALER_COST
RETAIL_COST
COMPUTE PROFIT/D12.2%=(RETAIL_COST - DEALER_COST ) *100 / DEALER_COST ;
BY COUNTRY
BY CAR
BY MODEL
ON TABLE PCHOLD FORMAT HTML
ON TABLE RECOMPUTE AS 'TOTAL'
END
-RUN
WebFOCUS 8206, Unix, Windows
September 26, 2018, 11:07 AM
RobertF
I think I have it working ---thanks!
WebFOCUS 8206.08 Windows, All Outputs
September 26, 2018, 11:24 AM
BabakNYC
Good. By the way a subtotal isn't required for this to work. The main thing to remember is that calculations like Averages, Percents et cetera, apply to the answer set not each individual record because depending on how many sort fields you have the values will change. Consequently, like MartinY said, you should use a COMPUTE instead of a DEFINE and use RECOMPUTE for subtotals and grand totals. That way the calculations adjust depending on the number of sorts you add to your report instead of just adding a bunch of numbers in a column.