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.



Read-Only Read-Only Topic
Go
Search
Notify
Tools
Totals
 Login/Join
 
kitten
Gold member
posted
Can someone please help me. I'm trying to do what I would call a group by in sql. I have multiple rows of fund codes with amounts for a person. I want to just show a total of the amount for each person (one row for a person). when I got rid of the fund code and kept the amount column and used a sum on the amount column....it didn't sum all fund codes. It only gave me the one amount(i think the first). how can i get a sum for each person. I'm doing a by on the name.


WebFOCUS 7.6.2, MS Windows Server/______, Excel, PDF, HTML
 
Posts: 91 | Registered: May 15, 2008Report This Post
GinnyJakes
Expert
posted Hide Post
Kitten,

First things, first. Please update your profile signature with your product suite, releases, and platform. That way we can better help you.

Secondly, it sounds like you are doing it properly, i.e. SUM AMOUNT BY PERSON_NAME. BY is the WebFOCUS equivalent of GROUP BY and ORDER BY.

You might want to post your code (and your master if it is not too big). Maybe you've got a join going on that is skewing the results.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Prarie
Virtuoso
posted Hide Post
** Update signature
1) Logon to Focal Point and go into your complete profile.
2) Scroll down the page until you see the “Signature” field.
3) Fill in the signature you want to use. Here is a sample to model:

Member Name
Member Title

Prod: WebFOCUS 5.2.3 on Win 2K/IIS 6/ISAPI Mode
Test: WebFOCUS 7.1 on Win 2K/IIS 6/Weblogic 8.1/Servlet Mode

4) Once all changes are made/added, scroll down to the bottom of the page and click on the Submit button.


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Prarie
Virtuoso
posted Hide Post
Oh...Ginny you beat me to it.


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
GinnyJakes
Expert
posted Hide Post
Yes, buy you had better directions.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
kitten
Gold member
posted Hide Post
I have to get more info from the sys admin. Here's my code:

TABLE FILE AS_RESIDENTIAL_LIFE
SUM
Name
BLDG_DESC_ROOM AS 'Building'
ROOM_NUMBER AS 'Room No.'
MSCD_DESC AS 'Meal Plan'
AWARD_TERM_ACCEPT_AMOUNT AS 'Accept Amount'
COMPUTE CNTR/D12 = CNTR + 1; NOPRINT
BY Name NOPRINT
BY TERM_CODE_KEY AS 'Term'
BY ID
HEADING
""
FOOTING
""
ON TABLE SUBFOOT
"WHERE ( MEAL_PLAN_IND EQ 'Y' ) OR ( HOUSING_IND EQ 'Y' );
WHERE TERM_CODE_KEY EQ '&TERM_CODE_KEY.Term Code.';
WHERE AWARD_TERM_PAID_AMOUNT EQ MISSING;
WHERE AWARD_TERM_ACCEPT_AMOUNT NE MISSING;
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT &WFFMT.(,,,,,).Select type of display output.
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
LEFTMARGIN=0.000000,
RIGHTMARGIN=0.000000,
TOPMARGIN=0.000000,
BOTTOMMARGIN=0.000000,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,


WebFOCUS 7.6.2, MS Windows Server/______, Excel, PDF, HTML
 
Posts: 91 | Registered: May 15, 2008Report This Post
kitten
Gold member
posted Hide Post
OOPS!!!! Here's the rest:

"WHERE ( MEAL_PLAN_IND EQ 'Y' ) OR ( HOUSING_IND EQ 'Y' );
WHERE TERM_CODE_KEY EQ '&TERM_CODE_KEY.Term Code.';
WHERE AWARD_TERM_PAID_AMOUNT EQ MISSING;
WHERE AWARD_TERM_ACCEPT_AMOUNT NE MISSING;
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT &WFFMT.(,,,,,).Select type of display output.
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *


WebFOCUS 7.6.2, MS Windows Server/______, Excel, PDF, HTML
 
Posts: 91 | Registered: May 15, 2008Report This Post
GinnyJakes
Expert
posted Hide Post
Kitten, good start on your signature.

Now to the code. If you are going to sort by name and term, you will get more than one line per name. And I'm not sure what the CNTR is for. And summing on alpha fields is not generally a good idea. You only get the last one in a group. Here is an alternative, which I can't test of course.

TABLE FILE AS_RESIDENTIAL_LIFE
SUM AWARD_TERM_ACCEPT_AMOUNT AS 'Accept Amount'
BY Name
PRINT
BLDG_DESC_ROOM AS 'Building'
ROOM_NUMBER AS 'Room No.'
MSCD_DESC AS 'Meal Plan'
BY Name 
BY TERM_CODE_KEY AS 'Term'
BY ID
ON TABLE SET BYDISPLAY ON
ON TABLE SUBFOOT
END


This is called a multi-verb request.

Let us know if that helps you at all.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
kitten
Gold member
posted Hide Post
I took the by off of the term because there is only one term. The CNTR gives a total of the number of people in this query. I took the sum off of the alph fields. I did it in painter...it's running now....takes forever these are very large views.


WebFOCUS 7.6.2, MS Windows Server/______, Excel, PDF, HTML
 
Posts: 91 | Registered: May 15, 2008Report This Post
kitten
Gold member
posted Hide Post
I tried making the changes you suggested in painter and it didn't work. I guess I'll try adding the code. I'm no good at the code don't know what should stay or go.


WebFOCUS 7.6.2, MS Windows Server/______, Excel, PDF, HTML
 
Posts: 91 | Registered: May 15, 2008Report This Post
GinnyJakes
Expert
posted Hide Post
You put a NOPRINT on the CNTR field so it wasn't obvious that you needed it.

You can do a multi-verb request in the Report Painter but it is a bit tricky.

Paste the code I provided in the text editor and run it to see if it is doing what you want. Then if it is, open it in the painter and do your styling.

Good luck!


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders