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] Combine some columns to produce two rows?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Combine some columns to produce two rows?
 Login/Join
 
ChannyS
Guru
posted
Supposed I have data that gets returned to me as a records as following:

  
CAR 		SALES1		SALES2		SALES_DATE_1		SALES_DATE_2
Jaguar		100000		150000		01/01/2008		01/01/2009
BMW		200000		200000		01/01/2008		01/01/2009


Would there be a way for me to take this data to produce a report such as:

 

CAR: JAGUAR

			SALES
01/01/2009		150000
01/01/2008		100000


CAR: BMW
			SALES
01/01/2009		200000
01/01/2008		200000


I'd prefer not to do this with all the data in a subhead or subfoot (which I know I can do). I'm looking to try to get real columns out of this if possible.

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


Release: WebFOCUS 7.6.8
OS: Windows
Output formats: HTML, PDF, Excel, csv
 
Posts: 256 | Registered: July 21, 2008Report This Post
GinnyJakes
Expert
posted Hide Post
Print the columns as CAR, SALES1, SALES_DATE_1, SALES2, SALES_DATE_2 and hold format alpha.

Create an alternate master for the hold file using the OCCURS clause and then filedef this master to your hold file.

Your master would look something like this:

FILENAME=CARSALES, SUFFIX=FIX
SEGNAME=CARS,SEGTYPE=S0
FIELD=CAR, ALIAS=, FORMAT=A10, ACTUAL=A10,$
SEGNAME=SALES,OCCURS=n,PARENT=CARS,$
FIELD=SALES, ALIAS=, FORMAT=I8, ACTUAL=A8,$
FIELD=SLSDT, ALIAS=, FORMAT=YYMD, ACTUAL=A8,$

Note: You will have to adjust the formats for your own data.
Note 2: n will be the max number of occurs or can be VARIABLE.

Then you can say:
TABLE FILE CAR SALES
SUM SALES
BY CAR
BY SLSDT
END


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
ChannyS
Guru
posted Hide Post
Thanks!


Release: WebFOCUS 7.6.8
OS: Windows
Output formats: HTML, PDF, Excel, csv
 
Posts: 256 | Registered: July 21, 2008Report This Post
Francis Mariani
Expert
posted Hide Post
The easiest way would be to read the data twice, creating HOLD files with renamed columns, something like this:

SET ASNAMES=ON

TABLE FILE CAR
PRINT
LENGTH AS 'DIM'
COMPUTE DIMX/A10 = 'LENGTH';
BY MODEL
ON TABLE HOLD AS H001
END

TABLE FILE CAR
PRINT
WIDTH AS 'DIM'
COMPUTE DIMX/A10 = 'WIDTH';
BY MODEL
ON TABLE HOLD AS H002
END

TABLE FILE H001
PRINT 
DIM
DIMX
BY MODEL

MORE
FILE H002
END


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
ChannyS
Guru
posted Hide Post
Francis,

That works really nicely.

Thanks so much.


Release: WebFOCUS 7.6.8
OS: Windows
Output formats: HTML, PDF, Excel, csv
 
Posts: 256 | Registered: July 21, 2008Report 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] Combine some columns to produce two rows?

Copyright © 1996-2020 Information Builders