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] Column Heading with Across
Page 1 2 

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Column Heading with Across
 Login/Join
 
Guru
posted Hide Post
quote:
TABLE FILE CAR
SUM
DCOST AS ''
BY COUNTRY
ACROSS SEATS NOPRINT
ACROSS CAR
ON TABLE HOLD AS ACRSHOLD FORMAT ALPHA
ON TABLE SET HOLDLIST PRINTONLY
END


switch the across statement?

you have to imagine it as a horizontal BY


Developer Studio 7.64
Win XP
Output: mostly HTML, also Excel and PDF

"Never attribute to malice that which can be adequately explained by stupidity." - Heinlein's Razor
 
Posts: 285 | Location: UK | Registered: October 26, 2007Report This Post
Virtuoso
posted Hide Post
If you use ASNAMES, the column names in the hold file will be replaced with the contents of the very first across field. In this case SEATS.
What you want in your live situation is probably something like a month overview, where you do not want the month to be sorted by name but rather by value, but you want to see the name. That can be done, but it is a bit awkward and requires thinking out-of-the-box so to speak.
Consider the follwing example, based on the car file. It shows the dealer cost, sorted by country, across car (which in its turn is sorted by seats - noprint).
 
SET ASNAMES=ON

DEFINE FILE CAR
SORT/A19 = FTOA(SEATS,'(D3)','A3') || CAR;
END

TABLE FILE CAR
SUM
DCOST
BY COUNTRY
ACROSS SORT
ON TABLE HOLD AS ACRSHOLD FORMAT ALPHA
ON TABLE SET HOLDLIST PRINTONLY
END

CHECK FILE ACRSHOLD HOLD

DEFINE FILE HOLD
COLNAME/A50 = '''' | EDIT(FIELDNAME,'9999999999999999999') | ''' AS ''' | EDIT(FIELDNAME,'$$$$$$9999999999999999') | '''';
END

TABLE FILE HOLD
PRINT COLNAME
WHERE FIELDNAME LIKE 'DEA%';
ON TABLE HOLD
END
-RUN

TABLE FILE ACRSHOLD
PRINT 
-INCLUDE hold.ftm
BY COUNTRY
END

-RUN
 

Hope this points you in the desired direction...


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Guru
posted Hide Post
GamP,

You're right on. All I need to do is convert SEATS to alpha, and Susannah's CAR example will work like a charm.

I just don't understand why numeric field behave different from alpha field. Hope someone have the explaination for this.

Thanks again for answering nubi's question.

Hua


Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS
 
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008Report This Post
Guru
posted Hide Post
quote:

i just don't understand why numeric field behave different from alpha field. Hope someone have the explaination for this.



i leave that as an excersize for you to find out, but you may wanna think about how you would alphabetically sort numbers, or more importantly how you would put the letters A-Z in numeric order..


quote:
Originally posted by Hua:
Thanks again for answering nubi's question.
Hua



que? Confused

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


Developer Studio 7.64
Win XP
Output: mostly HTML, also Excel and PDF

"Never attribute to malice that which can be adequately explained by stupidity." - Heinlein's Razor
 
Posts: 285 | Location: UK | Registered: October 26, 2007Report This Post
Guru
posted Hide Post
nubi,

I am very much appreciate your challenge. If someone already gone through burning their brain cells I would be gratefully take their advices. Meanwhile, I'll do my own search too. I am quite satisfied with the workaround here. I am pointing it out so someone else don't have to scratch their head like I did in case they want to sort the numeric value first.

In regards to how and why I sort the data, it is obvious to me that the database is designed to have internal chronicle date sequence along with external representation by conventions. yyyymmdd is internal and mm/dd/yy is external, I have both fields in the file. I obviously want to sort yyyymmdd and display mm/dd/yy(the American standard).

Hua


Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS
 
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008Report This Post
Virtuoso
posted Hide Post
If you have (or define) a "smart date" field for the period (e.g., format Q.YY or MtYY) you can use it for Hold Across -- the periods will appear in natural order, and the field suffix in the Hold file will be the unadorned numeric form of the data. Try this out:
SET ASNAMES=ON,ONLINE-FMT=PDF,COMPOUND=OPEN
DEFINE FILE CAR
 PERIOD/Q.YY WITH COUNTRY= IF LAST PERIOD EQ 0 THEN '2008 Q4' ELSE LAST PERIOD + 1;
END
TABLE FILE CAR  SUM SEATS  ACROSS PERIOD
END
TABLE FILE CAR  SUM SEATS  ACROSS PERIOD   ON TABLE HOLD AS STATS
END
SET COMPOUND=CLOSE
TABLEF FILE STATS PRINT *
END


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Guru
posted Hide Post
it wasn't a trick question or even a particularly coding related question- you cant sort letters numerically because they are not numbers, you can sort numbers alphabetically but they wont be sorted in numerical order so will come out as 1,10,2,3,4,5,6,7,8,9

also how would you carry out arithemtic on alphanumerica characters?


Developer Studio 7.64
Win XP
Output: mostly HTML, also Excel and PDF

"Never attribute to malice that which can be adequately explained by stupidity." - Heinlein's Razor
 
Posts: 285 | Location: UK | Registered: October 26, 2007Report This Post
Guru
posted Hide Post
Jack, I see where you want to steel me to(and I thought maybe that was what nubi have been rolling his eyes convincing me to). I should be able to move on now. And you are ahead of me when I wondered how can I format the output if I can only do PRINT *. Thanks.

nubi, neither English nor WebFOCUS are my first languages and my books had returned to the professors long time ago. I really feel dumber than I thought. So I beg you! give me an illustration, like the report elements, huh?

Hua


Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS
 
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008Report This Post
Guru
posted Hide Post
sorry Hua, i for some reason assumed you were American, and i wasn't rolling my eyes- look again Smiler

here is a rough example, im using the DEFINE to create a new field which is an ALPHA version of SALES.

i then run the CAR file twice, the first time summarising the Alpha version of SALES, the second time summarising the Number version of SALES.

notice that when SALES is converted to alpha and the summarised it comes out as 0 but when the number version of the field is summarised it comes out correctly as 208420?

DEFINE FILE CAR
SALES_ALPHA/A5 =SUBSTR(7, EDIT(SALES), 2, 7, 5, 'A5');
END

TABLE FILE CAR
SUM SALES_ALPHA 
ON TABLE SET STYLE *
END

TABLE FILE CAR
SUM SALES 
ON TABLE SET STYLE *
END 


Developer Studio 7.64
Win XP
Output: mostly HTML, also Excel and PDF

"Never attribute to malice that which can be adequately explained by stupidity." - Heinlein's Razor
 
Posts: 285 | Location: UK | Registered: October 26, 2007Report This Post
Guru
posted Hide Post
nubi, I prefer to say 'huh' more than 'eh' doesn't make me an American.
Your example is 100% tricky to me, I haven't have the imagination to apply your technique to my code. But I got enough info for me to do my own homework. Thanks.
(you may Confused Roll Eyes now)


Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS
 
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008Report This Post
Guru
posted Hide Post
i suppose now that i have no idea what your talking about that a Confused is appropriate Smiler


Developer Studio 7.64
Win XP
Output: mostly HTML, also Excel and PDF

"Never attribute to malice that which can be adequately explained by stupidity." - Heinlein's Razor
 
Posts: 285 | Location: UK | Registered: October 26, 2007Report This Post
Guru
posted Hide Post
It is OK. Let's leave it like that. Maybe some day someone or something hit me on the head will open up my mind.

Cheers.


Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS
 
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008Report This Post
Guru
posted Hide Post
dont be too hard on yourself, Rome wasn't built in a day....


Developer Studio 7.64
Win XP
Output: mostly HTML, also Excel and PDF

"Never attribute to malice that which can be adequately explained by stupidity." - Heinlein's Razor
 
Posts: 285 | Location: UK | Registered: October 26, 2007Report This Post
  Powered by Social Strata Page 1 2  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Column Heading with Across

Copyright © 1996-2020 Information Builders