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     [CLOSED]Total Row percentage with across

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED]Total Row percentage with across
 Login/Join
 
Gold member
posted
I am trying to get a across percentage while using an across report.

Using the below code i get a percentage after every across field, which is called ACTIVITY_DESC. How can i get a percentage on just the total row amount (ACROSS-TOTAL) on the far right side?

Is there anyway i can do a percentage on just the ACROSS-TOTAL field?
TABLE FILE HOLD
SUM
TOTAL1 AS ''
PCT.TOTAL1/D3% WITHIN Name AS '%'
BY STROFFICE NOPRINT
BY OFFICE_ID NOPRINT
BY Name AS ' '
BY STAGE_DESC AS ' '
ACROSS ACTIVITY_DESC AS ' '
ACROSS-TOTAL
ON Name SUBTOTAL
TOTAL1 AS 'TOTAL FOR - '


WF 7.1.7 Windows 2003

This message has been edited. Last edited by: <Emily McAllister>,


WF 7.1.7- Windows XP
 
Posts: 63 | Registered: August 28, 2007Report This Post
Virtuoso
posted Hide Post
Welcome to the Forum
Please take a minute to update your signature (via Profile page) with the following information:

1) What version of WebFOCUS are you using?
2) What platform are you on?

on this Try Question Try adding
ON TABLE RECOMPUTE


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Gold member
posted Hide Post
I have updated my profile, thanks for letting me know. Also the resolution did not work.


WF 7.1.7- Windows XP
 
Posts: 63 | Registered: August 28, 2007Report This Post
Virtuoso
posted Hide Post
Sorry...was just a quick suggestion.


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Gold member
posted Hide Post
thanks a bunch for suggestion, its greatly appreciated im just trying to figure this thing out. You would think it would be simple. I would think i can just take the percentages that are being showed after every value and make them "invisible" but when i do that it also makes my total percentage invisible even though that field was never selected to be made invisble.


WF 7.1.7- Windows XP
 
Posts: 63 | Registered: August 28, 2007Report This Post
Gold member
posted Hide Post
quote:
Originally posted by Erney:
I am trying to get a across percentage while using an across report.

Using the below code i get a percentage after every across field, which is called ACTIVITY_DESC. How can i get a percentage on just the total row amount (ACROSS-TOTAL) on the far right side?

Is there anyway i can do a percentage on just the ACROSS-TOTAL field?
TABLE FILE HOLD
SUM
TOTAL1 AS ''
PCT.TOTAL1/D3% WITHIN Name AS '%'
BY STROFFICE NOPRINT
BY OFFICE_ID NOPRINT
BY Name AS ' '
BY STAGE_DESC AS ' '
ACROSS ACTIVITY_DESC AS ' '
ACROSS-TOTAL
ON Name SUBTOTAL
TOTAL1 AS 'TOTAL FOR - '


WF 7.1.7 Windows 2003

As you can see from this picture, there is a "%" column after every across category. The only thing i want to see is the percent column after the Total on the far right side.


WF 7.1.7- Windows XP
 
Posts: 63 | Registered: August 28, 2007Report This Post
Expert
posted Hide Post
another thing you'll want to do , Erney, is write your examples using the CAR file, so that we can help you more easily.
Here's one way to solve your problem:
TABLE FILE CAR
SUM SALES  NOPRINT
SUM SALES  NOPRINT BY MODEL 
SUM SALES  ACROSS COUNTRY  AND COMPUTE P/P8%=100*C2/C1; 
BY MODEL
ON TABLE COLUMN-TOTAL
END

the terms C1 and C2 are column references, column1 is the total of all sales for your data set, column c2 is the sum of all sales for the BY field.




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Expert
posted Hide Post
She's baaaaack! Smiler


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Platinum Member
posted Hide Post
Here's another idea....

DEFINE FILE CAR
D_SALES/D8.1% = SALES;
END
-*
TABLE FILE CAR
SUM
SALES AS 'TOTAL,SALES'
PCT.D_SALES AS 'PCT.,SALES'
BY MODEL
SUM SALES
BY MODEL
ACROSS COUNTRY
ON TABLE COLUMN-TOTAL
END

This puts the total and percent on the left side and then the across totals follow.

Jim


WF DevStu 5.2.6/WF Srv 5.2.4/Win NT 5.2
 
Posts: 118 | Location: Lincoln Nebraska | Registered: May 04, 2005Report This Post
Platinum Member
posted Hide Post
Have you tried using the RPCT.field option instead of the PCT. option. i.e.

SUM
RPCT.TOTAL1 AS ''
_


Release 7.6.9
Windows
HTML
 
Posts: 226 | Registered: June 08, 2003Report This Post
Gold member
posted Hide Post
Apparently you cannot use the across option and expect to get a single percent total on the right hand side. I needed to change it from an across report to a general "by" report without the across.

thanks for the help.

By the way the rpct.total1 as '' does not change the report.


WF 7.1.7- Windows XP
 
Posts: 63 | Registered: August 28, 2007Report This Post
Expert
posted Hide Post
Erney, of course you can. I promise, its as easy as running the code example i gave you...
i tried to spell it out very carefully for you so you could adapt the technique for lots of other situations.




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Gold member
posted Hide Post
Yes susannah i greatly appreciate that. I would like to see you do the above across that you have without using the compute field and doing it with the pct.sales field.

I cannot do the compute field since the percentage is based on the total of the columns. In the above report the percentage is based off of 48 which is the total for all rows. Because of this is cannot use a compute field.


WF 7.1.7- Windows XP
 
Posts: 63 | Registered: August 28, 2007Report This Post
Expert
posted Hide Post
which is why we use double verbs (actually triple verbs), we create the total for the sample as a first column
we create the row totals as the second column
we just dont print them, but they're still available to be used.
The COMPUTE field is the way to do this.
Replicate the code i wrote for you and take the NOPRINTs out. Maybe it will be clearer to you then. ok?




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Virtuoso
posted Hide Post
I think Erney wants something else, I will post a sample report and I hope Erney tells us if this is it.
        ENGLAND FRANCE ITALY  TOTAL    PCT
Hardtop     500   2500   .     3000    20%
Coupe      2000   5000  2000   9000    60%
Break        .    1000  2000   3000    20%
Total      2500   8500  3000  15000   100% 




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
which is exactly what i gave him. Is there a full moon today?
OMG it IS a full moon, today
that explains alot!




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Guru
posted Hide Post
You guys bicker ... well maybe banter is a better word ... back and forth like family. It's one of the reasons I love being here.

Keep it up!


Glenda

In FOCUS Since 1990
Production 8.2 Windows
 
Posts: 301 | Location: Galveston, Texas | Registered: July 07, 2004Report This Post
Virtuoso
posted Hide Post
Susannah, it's full moon here too, but here it is night....
I posted my example to be sure that this is what Erney wants...
The posted questions are sometimes a but confusing....




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
Gold member
posted Hide Post
Frank thats exactly it. Lol sorry im a .net / Crystal guy, wasnt expecting to have to learn a new programming language just to do reports. I thought the GUI would let me accomplish my tasks.


WF 7.1.7- Windows XP
 
Posts: 63 | Registered: August 28, 2007Report This Post
Virtuoso
posted Hide Post
My .net and Crystal colleagues don't understand how Focus works....they will write programs to do the job and keep asking if I can convert a Crystal report into Webfocus.

But "just to do reports" is not the right way to look at it.
The report is in most of the cases the final result where almost the whole computing thing is working for. Put numbers and characters into a database just for putting it in, would be a waste of time if the data is never used to create information from that data.




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
Gold member
posted Hide Post
Excellent way to look at it!


WF 7.1.7- Windows XP
 
Posts: 63 | Registered: August 28, 2007Report This Post
Guru
posted Hide Post
Hi folks,

I am developing a report using PCT, across and row-total. I changed my fex to use Susannah´s logic. I am getting the corret values but now I need to sort data by highest SALES ( my case it´s the highest VENTAS). Any help?

 
TABLE FILE DW_ADV_VENEZUELA
SUM DW_ADV_VENEZUELA.MSRSD83_ADV_REPORTS_VENEZUELA.SRSD83_RETAIL_SALES_Q NOPRINT
SUM DW_ADV_VENEZUELA.MSRSD83_ADV_REPORTS_VENEZUELA.SRSD83_RETAIL_SALES_Q  NOPRINT BY MANUFACTURER 
SUM DW_ADV_VENEZUELA.MSRSD83_ADV_REPORTS_VENEZUELA.SRSD83_RETAIL_SALES_Q AS 'Ventas'
PCT.VENTAS AS '%'
ACROSS ORDEM NOPRINT
ACROSS SEGMENTO COMPUTE P/P8.2%=100*C2/C1;
BY MANUFACTURER
WHERE ( DW_ADV_VENEZUELA.MSRSD83_ADV_REPORTS_VENEZUELA.SRSD83_PERIOD_R EQ '&MES');
END

 


WebFOCUS 8.1.05 / APP Studio
 
Posts: 272 | Location: Brazil | Registered: October 31, 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     [CLOSED]Total Row percentage with across

Copyright © 1996-2020 Information Builders