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,

Prod: WebFOCUS 7.7.03: Win 2008 & AIX hub/Servlet Mode; sub: AS/400 JDE; mostly Self Serve; DBs: Oracle, JDE, SQLServer; various output formats

Here is an example of the output I am getting:

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%.

I hope this helps...

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.

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, 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.

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.

ON RGN_D RECAP
FIELD1/P15 = COLUMN6;
FIELD2/P5% = (((COLUMN6 - COLUMN7) / COLUMN7) * 100);
FIELD3/P15 = COLUMN5;
FIELD4/P5% = (((COLUMN5 - COLUMN6) / COLUMN6) * 100);
FIELD5/P15 = COLUMN4;
FIELD6/P5% = (((COLUMN4 - COLUMN5) / COLUMN5) * 100);
FIELD7/P15 = COLUMN3;
FIELD8/P5% = (((COLUMN3 - COLUMN4) / COLUMN4) * 100);
FIELD9/P15 = COLUMN2;
FIELD10/P5% = (((COLUMN2 - COLUMN3) / COLUMN3) * 100);
FIELD11/P15 = COLUMN1;
FIELD12/P5% = (((COLUMN1 - COLUMN2) / COLUMN2) * 100);

ON RGN_D SUBFOOT
" "
"Sub Total: RGN_D FIELD1 FIELD2 FIELD3 FIELD4 FIELD5 FIELD6 FIELD7 FIELD8 FIELD9 FIELD10 FIELD11 FIELD12"
" "

ON CMDTY_UOM_EXPRT1_C RECAP
FIELDA/P15 = COLUMN6;
FIELDB/P5% = (((COLUMN6 - COLUMN7) / COLUMN7) * 100);
FIELDC/P15 = COLUMN5;
FIELDD/P5% = (((COLUMN5 - COLUMN6) / COLUMN6) * 100);
FIELDE/P15 = COLUMN4;
FIELDF/P5% = (((COLUMN4 - COLUMN5) / COLUMN5) * 100);
FIELDG/P15 = COLUMN3;
FIELDH/P5% = (((COLUMN3 - COLUMN4) / COLUMN4) * 100);
FIELDI/P15 = COLUMN2;
FIELDJ/P5% = (((COLUMN2 - COLUMN3) / COLUMN3) * 100);
FIELDK/P15 = COLUMN1;
FIELDL/P5% = (((COLUMN1 - COLUMN2) / COLUMN2) * 100);

ON CMDTY_UOM_EXPRT1_C SUBFOOT
"Grand Total: CMDTY_UOM_EXPRT1_C FIELDA FIELDB FIELDC FIELDD FIELDE FIELDF FIELDG FIELDH FIELDI FIELDJ FIELDK FIELDL"

Style Sheet:

TYPE=SUBFOOT, OBJECT=TEXT, STYLE=BOLD, $
TYPE=SUBFOOT, BY=2, OBJECT=FIELD, ITEM=2, POSITION=COLUMN6, STYLE=BOLD, $
TYPE=SUBFOOT, BY=2, OBJECT=FIELD, ITEM=3, POSITION=CHG, STYLE=BOLD, $
TYPE=SUBFOOT, BY=2, OBJECT=FIELD, ITEM=4, POSITION=COLUMN5, STYLE=BOLD, $
TYPE=SUBFOOT, BY=2, OBJECT=FIELD, ITEM=5, POSITION=CHG0, STYLE=BOLD,$
TYPE=SUBFOOT, BY=2, OBJECT=FIELD, ITEM=6, POSITION=COLUMN4, STYLE=BOLD,$
TYPE=SUBFOOT, BY=2, OBJECT=FIELD, ITEM=7, POSITION=CHG1, STYLE=BOLD,$
TYPE=SUBFOOT, BY=2, OBJECT=FIELD, ITEM=8, POSITION=COLUMN3, STYLE=BOLD,$
TYPE=SUBFOOT, BY=2, OBJECT=FIELD, ITEM=9, POSITION=CHG2, STYLE=BOLD, $
TYPE=SUBFOOT, BY=2, OBJECT=FIELD, ITEM=10, POSITION=COLUMN2, STYLE=BOLD,$
TYPE=SUBFOOT, BY=2, OBJECT=FIELD, ITEM=11, POSITION=CHG3, STYLE=BOLD, $
TYPE=SUBFOOT, BY=2, OBJECT=FIELD, ITEM=12, POSITION=COLUMN1, STYLE=BOLD,$
TYPE=SUBFOOT, BY=2, OBJECT=FIELD, ITEM=13, POSITION=CHG4, STYLE=BOLD,$

TYPE=SUBFOOT, BY=1, OBJECT=FIELD, ITEM=2, POSITION=COLUMN6, STYLE=BOLD,$
TYPE=SUBFOOT, BY=1, OBJECT=FIELD, ITEM=3, POSITION=CHG, STYLE=BOLD,$
TYPE=SUBFOOT, BY=1, OBJECT=FIELD, ITEM=4, POSITION=COLUMN5, STYLE=BOLD,$
TYPE=SUBFOOT, BY=1, OBJECT=FIELD, ITEM=5, POSITION=CHG0, STYLE=BOLD,$
TYPE=SUBFOOT, BY=1, OBJECT=FIELD, ITEM=6, POSITION=COLUMN4, STYLE=BOLD,$
TYPE=SUBFOOT, BY=1, OBJECT=FIELD, ITEM=7, POSITION=CHG1, STYLE=BOLD,$
TYPE=SUBFOOT, BY=1, OBJECT=FIELD, ITEM=8, POSITION=COLUMN3, STYLE=BOLD,$
TYPE=SUBFOOT, BY=1, OBJECT=FIELD, ITEM=9, POSITION=CHG2, STYLE=BOLD,$
TYPE=SUBFOOT, BY=1, OBJECT=FIELD, ITEM=10, POSITION=COLUMN2, STYLE=BOLD,$
TYPE=SUBFOOT, BY=1, OBJECT=FIELD, ITEM=11, POSITION=CHG3, STYLE=BOLD,$
TYPE=SUBFOOT, BY=1, OBJECT=FIELD, ITEM=12, POSITION=COLUMN1, STYLE=BOLD,$
TYPE=SUBFOOT, BY=1, OBJECT=FIELD, ITEM=13, POSITION=CHG4, STYLE=BOLD,$

Thanks for helping...

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

You have a lot of potential.

WebFOCUS is about technique and method and making it do what you want it to do.

No other BI tool gives that flexibility or control.

Ask, learn, use or ignore, as you require for what you need to do.

And feed back when you feel you can help.