I am having trouble recomputing a percentage calculation in my report. I was wondering if anyone can assist me in what I need to do or point me in the right direction.
What I am trying to do is recompute the define percent calculations in both subtotal lines (subtotal by region and subtotal by commodity unit of measure code (grandtotal) ). Right now my percentages in my subtotal like are just summing the value. I figured out if you do computes in your Table File without missing on then I can recompute the calculation but I need the missing on. I also know you cant do a missing on in a compute so thats why I did it in a define.
I looked for examples in these forums but did not have any luck. If there are examples please send me the link.
Please Help!!
Below is my code for my final output of the report:
DEFINE FILE EXPORT_6YR CHG/P15% MISSING ON = IF COLUMN7 EQ 0 THEN MISSING ELSE (((COLUMN6 - COLUMN7) / COLUMN7) * 100); CHG0/P15% MISSING ON = IF COLUMN6 EQ 0 THEN MISSING ELSE (((COLUMN5 - COLUMN6) / COLUMN6) * 100); CHG1/P15% MISSING ON = IF COLUMN5 EQ 0 THEN MISSING ELSE (((COLUMN4 - COLUMN5) / COLUMN5) * 100); CHG2/P15% MISSING ON = IF COLUMN4 EQ 0 THEN MISSING ELSE (((COLUMN3 - COLUMN4) / COLUMN4) * 100); CHG3/P15% MISSING ON = IF COLUMN3 EQ 0 THEN MISSING ELSE (((COLUMN2 - COLUMN3) / COLUMN3) * 100); CHG4/P15% MISSING ON = IF COLUMN2 EQ 0 THEN MISSING ELSE (((COLUMN1 - COLUMN2) / COLUMN2) * 100); END
TABLE FILE EXPORT_6YR SUM COLUMN6 AS '&NEW_DATE4,Volume' CHG AS '% Chg' COLUMN5 AS '&NEW_DATE3,Volume' CHG0 AS '% Chg' COLUMN4 AS '&NEW_DATE2,Volume' CHG1 AS '% Chg' COLUMN3 AS '&NEW_DATE1,Volume' CHG2 AS '% Chg' COLUMN2 AS '&NEW_DATE,Volume' CHG3 AS '% Chg' COLUMN1 AS '&NEWDATE,Volume' CHG4 AS '% Chg'
BY CMDTY_UOM_EXPRT1_C NOPRINT BY RGN_D AS 'Region' BY CTRY_GRP_D AS 'Country (Export To)'
ON CMDTY_UOM_EXPRT1_C SUBTOTAL AS 'Grand Total:' ON CMDTY_UOM_EXPRT1_C PAGE-BREAK
ON RGN_D SUBTOTAL AS 'Sub Total:'
HEADING "6 Year Trend - Export" " <87 Date Run : <+0>&DATEMDYY " "Year Ending Date: &YM_LABEL <87 Run Time : &TIME" " <87 Page : "Volume: &Volume " "Domestic/Import: &DomImpTitle" &HEAD &HEAD1 &HEAD2 &HEAD3 &HEAD4 &HEAD5 &HEAD6 &HEAD7 &HEAD8 "Conversion Measure: &ConvMeas" " " "Measure : " " ON TABLE SET EMPTYREPORT ON ON TABLE SET PAGE-NUM OFF ON TABLE SET BYDISPLAY ON ON TABLE NOTOTAL ON TABLE PCHOLD FORMAT &OUTPUT ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * UNITS=IN, SQUEEZE=ON, ORIENTATION=LANDSCAPE, $ TYPE=REPORT, GRID=OFF, FONT='TIMES NEW ROMAN', SIZE=8, COLOR='BLACK', BACKCOLOR='NONE', STYLE=NORMAL, $
I did not post all of the styling...This message has been edited. Last edited by: prodrigu,
Measure : KG 12/2003 12/2004 12/2005 12/2006 12/2007 12/2008 Region Country (Export To) Volume %Chg Volume %Chg Volume %Chg Volume %Chg Volume %Chg Volume %Chg CENT. AMERICA BELIZE 0 ++++ 0 ++++ 0 ++++ 0 ++++ 0 ++++ 0 ++++ CENT. AMERICA COSTA RICA 121609 ++++ 0 -100% 0 ++++ 0 ++++ 0 ++++ 0 ++++ CENT. AMERICA EL SALVADOR 33237 ++++ 0 -100% 0 ++++ 0 ++++ 0 ++++ 0 ++++ CENT. AMERICA GUATEMALA 31466 ++++ 0 -100% 0 ++++ 0 ++++ 0 ++++ 0 ++++ CENT. AMERICA HONDURAS 110658 ++++ 0 -100% 0 ++++ 0 ++++ 0 ++++ 0 ++++ CENT. AMERICA NICARAGUA 22248 ++++ 0 -100% 0 ++++ 0 ++++ 0 ++++ 0 ++++ CENT. AMERICA PANAMA 136630 ++++ 0 -100% 0 ++++ 0 ++++ 0 ++++ 0 ++++
Sub Total: CENT. AMERICA 455848 0% 0 -600% 0 0% 0 0% 0 0% 0 0%
As you can see the columns with the dates vol are correct by getting summed up. But the %Chg need to be recomputed or recalculated. So if you look at the second % Chg with the all the -100%, the Sub Total for that column needs to be recompute or recalculated with (((COLUMN5 - COLUMN6) / COLUMN6) * 100). So the real Sub Total should be -100% not -600%.
prodrigu when posting output put it between code tags then it keeps it's alignment.
However it's not a SUBTOTAL you need it's a RECOMPUTE
And that is not the sort of thing that IBI support is for. It's a very basic WebFOCUS 101 training item or in your case a very obvious lack of having done it.
Get at least some basic training please.
April 15, 2009, 08:55 AM
PBrightwell
Actually, a RECOMPUTE won't help when your calculations are in a define. If you are totalling your numbers in a previous step changing your defined fields to computed fields will allow you to use RECOMPUTE instead of SUBTOTAL.
Pat WF 7.6.8, AIX, AS400, NT AS400 FOCUS, AIX FOCUS, Oracle, DB2, JDE, Lotus Notes
April 15, 2009, 09:12 AM
<JG>
Pat, you are absolutely correct.
He has to be doing his totaling in a previous step or the data is already summed otherwise his data %'s would be incorrect.
I assumed that prodrigu would know that he needed to switch to compute, but thinking about it I doubt that he would.
April 15, 2009, 02:50 PM
prodrigu
First, I tried doing a compute with missing on and it doesnt work. To be able to do missing on it only works in a DEFINE. This is the only way I can do it with getting the correct data I need for the report.
Second, I did figure out how to figure the precent calculation in my report. Instead of Recomputing or Subtotal I did a Recap with the calculations then put the new fields in the subfoot and in the style sheet I just positioned the field to the column. Below is the code on how to fixed my issue.