Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Using Subtotal Value in Define

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Using Subtotal Value in Define
 Login/Join
 
Platinum Member
posted
I would like to use a subtotal value in a define to claculate what percent of a hundred the cost is, or how much of the total cost does this expense represent of out 100


EX.

COSTS_____PERCENT
2_________5.4%
5_________13.5%
6_________16.2%
1_________2.7%
3_________8.1%
8_________21.6%
5_________13.5
7_________18.9
TOTAL
37________100

How can this be accomplished?

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


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 111 | Registered: May 12, 2011Report This Post
Platinum Member
posted Hide Post
ahhh yes, the basics. This is exactly what the PCT prefix does for you. Of course, there is always that pesky 'rounding' issue.

TABLE FILE CAR
SUM SEATS
   PCT.SEATS
BY CAR
ON TABLE COLUMN-TOTAL
END


will of course give you a total percentage of '95'. This is because the field SEATS is an integer. Do the following

DEFINE FILE CAR
	SEATS2/D6.2 = SEATS;
END

TABLE FILE CAR
SUM SEATS
    PCT.SEATS2
BY CAR
ON TABLE COLUMN-TOTAL
END


and the total for the percentages will equal 100 and all is well in the world.


Robert F. Bowley Jr.
Owner
TaRa Solutions, LLC

In WebFOCUS since 2001
 
Posts: 132 | Location: Gadsden, Al | Registered: July 22, 2005Report This Post
Platinum Member
posted Hide Post
Now what if i wanted it summed for every subtotoal

TITLE__COSTS_____PERCENT
A______2_________5.4%
_______5_________13.5%
_______6_________16.2%
_______1_________2.7%
_______3_________8.1%
_______8_________21.6%
_______5_________13.5
_______7_________18.9
TOTAL of A
_______37________100

TITLE__COSTS_____PERCENT
B______2_________5.4%
_______5_________13.5%
_______6_________16.2%
_______1_________2.7%
_______3_________8.1%
_______8_________21.6%
_______5_________13.5
_______7_________18.9
TOTAL of B
_______37________100


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 111 | Registered: May 12, 2011Report This Post
Platinum Member
posted Hide Post
I see my subtle sarcasm did not work. Try the same code with a

BY COUNTRY

and add in

ON COUNTRY SUBTOTAL

All of this is covered in DETAIL in:

Creating Reports With WebFOCUS Language > Displaying Report Data > Manipulating Display Fields With Prefix Operators

section of the WebFOCUS Help menu item in the Dev Studio.


Robert F. Bowley Jr.
Owner
TaRa Solutions, LLC

In WebFOCUS since 2001
 
Posts: 132 | Location: Gadsden, Al | Registered: July 22, 2005Report This Post
Virtuoso
posted Hide Post
I would suggest a multiverb solution

DEFINE FILE CAR
TOTDC/D12.2=DEALER_COST;
SUBTOTDC/D12.2=DEALER_COST;
END
TABLE FILE CAR
SUM TOTDC NOPRINT
SUM 
    SUBTOTDC NOPRINT
BY COUNTRY 
SUM 
     DEALER_COST
     COMPUTE CPCT/D15.1 = DEALER_COST / SUBTOTDC*100; AS 'country,pct'
     COMPUTE TOTPCT/D15.1 = DEALER_COST / TOTDC*100; AS 'overall,pct'
BY COUNTRY
BY CAR
     
ON COUNTRY SUBTOTAL   AS 'subtotal'
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF 
ON TABLE COLUMN-TOTAL  
END




The only thing is that you also see a total of the percentage CPCT, that I would love to surpress.
Maybe somebody has a suggestion for this




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Expert
posted Hide Post
ON TABLE RECOMPUTE


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Platinum Member
posted Hide Post
Im trying to do the first to suggestions by using:

SUM
SUBTOTDC NOPRINT
BY COUNTRY

before the SUM section in my code but i get this error:

(FOC020) THE SETS OF SORT PHRASES ARE INCONSISTENT
(FOC009) INCOMPLETE REQUEST STATEMENT
BYPASSING TO END OF COMMAND

Did not try RECOMPUTE I don't know what that is yet

Thanks for all the replys!!

This message has been edited. Last edited by: Charles Richards,


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 111 | Registered: May 12, 2011Report This Post
Virtuoso
posted Hide Post
Charles

Maybe it is time for some basic training

Did you try my whole code from begin to end on your own computer?

Does it work?

Then try to understand what the code does

I created a multiverb report with some hidden fields (NOPRINT)
Remove the noprint code and see what happens

What I did was creating two extra fields that in fact give you the same result.
To calc the percentage I created two computed fields.

I hope this helps.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Platinum Member
posted Hide Post
I have been messing around with your code and get it to work, tried using it with and without the NOPRINT...
However,

When i try to use the same concept on my current report it still gives me the error, so then I take it out and can get it to run showing all the data i want just doesnt have the last column with the percentages

As soon as I add another SUM it doesn't work

EX.

---------------
Running VERSION
----------------

DEFINE FILE DETAIL
CustNo/I6=IN_CUSTNO;
totSRP/D12.2=IN_PACK* IN_SRP;
UnitMove/I5=IN_QTY * IN_PACK;
totCost/D12.2=IN_UNITCOST * IN_PACK;
PercProf/D12.2=((IN_SRP - IN_UNITCOST) / IN_SRP)*100;
TotalProfit/D12.2=(totSRP - totCost);
subtotcost/D12.2=totCost;
END
TABLE FILE DETAIL

SUM
IN_ITEMCODE/I7 AS 'ItemCode'
IN_COMCLASS AS 'CC'
IN_DESC AS 'Item Description'
IN_PACK AS 'Pack'
IN_SIZE AS 'Size'
IN_QTY AS 'Cases Del.'
IN_SELL_UNITS AS 'Time Del.'
UnitMove AS 'Unit Move'
totCost AS 'Total Amount Paid'
totSRP AS 'Total Sell Value'
AVE.PercProf AS '% Profit'
TotalProfit
-----------------------------
would like to add percentages here for "totCost/ subtotoal.totCost" BY IN_COMCLASS
-----------------------------
BY DEPARTMENTCODE NOPRINT
BY IN_COMCLASS NOPRINT
BY IN_ITEMCODE NOPRINT
ON IN_COMCLASS SUBTOTAL
IN_QTY
totCost
totSRP AS '*TOTAL'
HEADING
""
FOOTING
""

========================================================

I may be getting the error becuase of the current way I have this Fex set up

Thanks for all the help, I am very new to WebFocus, thanks for the patience!


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 111 | Registered: May 12, 2011Report This Post
Virtuoso
posted Hide Post
DEFINE FILE DETAIL
CustNo/I6=IN_CUSTNO;
totSRP/D12.2=IN_PACK* IN_SRP;
UnitMove/I5=IN_QTY * IN_PACK;
totCost/D12.2=IN_UNITCOST * IN_PACK;
PercProf/D12.2=((IN_SRP - IN_UNITCOST) / IN_SRP)*100;
TotalProfit/D12.2=(totSRP - totCost);
subtotcost/D12.2=totCost;
END
TABLE FILE DETAIL
SUM SUBTOTCOST NOPRINT
BY DEPARTMENTCODE NOPRINT
BY IN_COMCLASS NOPRINT
SUM
IN_ITEMCODE/I7 AS 'ItemCode'
IN_COMCLASS AS 'CC'
IN_DESC AS 'Item Description'
IN_PACK AS 'Pack'
IN_SIZE AS 'Size'
IN_QTY AS 'Cases Del.'
IN_SELL_UNITS AS 'Time Del.'
UnitMove AS 'Unit Move'
totCost AS 'Total Amount Paid'
totSRP AS 'Total Sell Value'
AVE.PercProf AS '% Profit'
TotalProfit
-*----------------------------
COMPUTE PCTTOT/D9.2%=totCost/SUBTOTCOST;

-*-----------------------------
BY DEPARTMENTCODE NOPRINT
BY IN_COMCLASS NOPRINT
BY IN_ITEMCODE NOPRINT
ON IN_COMCLASS SUBTOTAL
IN_QTY
totCost
totSRP AS '*TOTAL'
HEADING
""
FOOTING
""
END


this should work well




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Platinum Member
posted Hide Post
Thanks So much I was able to get it working!

Now is there anyway to arrange it

BY PCTTOT NOPRINT

after

BY IN_COMCLASS NOPRINT ?


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 111 | Registered: May 12, 2011Report This Post
Virtuoso
posted Hide Post
if you do a BY HIGHEST TOTCOST NOPRINT whitin the comclass it will work that way




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Using Subtotal Value in Define

Copyright © 1996-2020 Information Builders