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     Can I arrange the order of the Across Statement????

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Can I arrange the order of the Across Statement????
 Login/Join
 
Learner
Gold member
posted
I have fex program that looks something like the below;

TABLE FILE KPIDATA
SUM
FIGURE
ACROSS COUNTRY
FOR
FIELD
"KPI" LABEL R1 OVER
..........

Is there a way to arrange the order of the ACROSS COUNTRY the way I want, rather than using the standard HIGHEST or other predefined sort?


WebFOCUS 7.1.6 on Win 2K/IIS 6/ISAPI HTML
 
Posts: 77 | Location: San Ramon, CA | Registered: May 17, 2007Report This Post
Tom Flynn
Expert
posted Hide Post
Yes,

Search on COLUMNS...

Additionally, it's obvious you "didn't" update your profile


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
susannah
Expert
posted Hide Post
Learner
this is the easy way
DEFINE FILE CAR
sortorder/I1=IF COUNTRY IS 'W GERMANY' THEN 1 ELSE
IF COUNTRY IS 'ENGLAND' THEN 2 ELSE
IF COUNTRY IS 'FRANCE' THEN 3 ELSE
IF COUNTRY IS 'CHINA' THEN 4 ELSE 5 ;
END
-RUN
TABLE FILE CAR
SUM SALES
ACROSS sortorder NOPRINT
ACROSS COUNTRY
...whatever else
END
..
the idea here is the NOPRINT concept
It means 'do it, just don't tell me about it'
and we've DEFINEd a variable to put your elements in order.

Tom's way works for GRAPH FILE, where NOPRINT doesnt seem to work.
TABLE FILE CAR
SUM SALES
ACROSS COUNTRY
COLUMNS 'W GERMANY' AND 'ENGLAND' AND 'FRANCE' AND ... you get the idea



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
Tony A
Expert
posted Hide Post
The alternative to hardcoding your values is to retrieve the values to be used in the COLUMNS or ROWS syntax and then repetatively read the values in to build your code. Have a look at this article for the idea.

T


In FOCUS
since 1986		WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Learner
Gold member
posted Hide Post
Thank you Tom and Susannah and Tony,

After viewing your suggestions and putting into my code, it is working exactly the way i want it. I have used Susannah method because it works better in my program.. Thanks Susannah for the solution. Very much appreciate.

Simon


WebFOCUS 7.1.6 on Win 2K/IIS 6/ISAPI HTML
 
Posts: 77 | Location: San Ramon, CA | Registered: May 17, 2007Report This Post
susannah
Expert
posted Hide Post
you're most welcome, California,
now please go to your 'Personal Zone' , 'Profile', and edit the signature block in that profile, so that we know what your system/version is , or are.
-S.



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
GCohen
Platinum Member
posted Hide Post
Just for the record I would like to provide a simple example so you see easy it is to use the 'columns' feature.
TABLE FILE CAR
SUM SALES
ACROSS BODYTYPE COLUMNS SEDAN AND COUPE AND WAGON AND WAGON
BY COUNTRY
END


Release 7.6.9
Windows
HTML
 
Posts: 226 | Registered: June 08, 2003Report This Post
Learner
Gold member
posted Hide Post
Thanks GeraldCohen for the additional solutions. I gain so much after i joined this forum. It is very useful!


WebFOCUS 7.1.6 on Win 2K/IIS 6/ISAPI HTML
 
Posts: 77 | Location: San Ramon, CA | Registered: May 17, 2007Report This Post
FrankDutch
Virtuoso
posted Hide Post
Learner

The "columns" function gives you also the possibility to add a column even if there is no data available.

Just add an "non existing" item to see the result.




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
susannah
Expert
posted Hide Post
wow. that is so handy to know...
It would be amusing to know how exactly you tripped over that little factoid Wink



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
Tony A
Expert
posted Hide Post
Susanahh,

That "little factoid" is mentioned in the link above.

T


In FOCUS
since 1986		WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
j.gross
Virtuoso
posted Hide Post
Yeah, COLUMNS creates a sort of Procrustean bed: lop off any uninvited values, and insert any no-shows.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
susannah
Expert
posted Hide Post
aha! i knew i had seen that somewhere before.
thanks T.
this is getting good , jg
1 : of, relating to, or typical of Procrustes
2 : marked by arbitrary often ruthless disregard of individual differences or special circumstances

i apologize to Learner for hijacking his thread..we get carried away sometimes.

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



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
Doug
Expert
posted Hide Post
Did I miss something? Without the "SET HOLDLIST = PRINTONLY" (Or, is that the default setting at this installation?) I get the following:

ERROR AT OR NEAR LINE 22 IN PROCEDURE rowscommand
(FOC015) THE TEST VALUE IS LONGER THAN THE FIELD FORMAT LENGTH: CONVERTIBLE
CONVERTIBLE
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT

Adding the -SET &ECHO = ALL; reveals the following:

TABLE FILE CAR
SUM CAR
BY COUNTRY
BY BODYTYPE
ROWS 'CONVERTIBLE CONVERTIBLE' OVER 'COUPE COUPE' OVER 'HARDTOP HARDTOP' OVER 'ROADSTER ROADSTER' OVER 'SEDAN SEDAN'
BY CAR NOPRINT
END




   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
j.gross
Virtuoso
posted Hide Post
Susannah -

Actually more extreme than Procrustes -- he would either stretch or shorten his visitors; COLUMNS may do both Wink


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report 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     Can I arrange the order of the Across Statement????

Copyright © 1996-2020 Information Builders