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
Excel spreadsheets
 Login/Join
 
Member
posted
I'd like to run two different reports but have the output from each one written to a different sheet in one spreadsheet. I can't find a description on how to do this. Any ideas?

We are on WebFOCUS 7.6.1
 
Posts: 8 | Registered: May 01, 2007Report This Post
Virtuoso
posted Hide Post
Don't have system up but try this USING CAR FILE

SET COMPOUND = OPEN
TABLE FILE CAR
PRINT MODEL 
BY COUNTRY
ON TABLE PCHOLD FORMAT EXL2K
END
TABLE FILE CAR
COUNT MODEL
BY COUNTRY
ON TABLE SET COMPOUND CLOSE
ON TABLE PC HOLD FORMAT EXL2K
END  


Basic the same as for compound PDF

The 'ON TABLE SET COMPOUND CLOSE' is how I do it in my environment. If done outside the table request.

SET COMPOUND = CLOSE


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Platinum Member
posted Hide Post
TYPE=REPORT, SIZE=9, TITLETEXT='Sheet1', GRID=ON, BORDER=OFF,BACKCOLOR=WHITE, $

if you interested in giving customised names to Sheets...

tks/sam


WF Server: 7.1.4 on Z/OS and Linux, ReportCaster
Data: DB2, DB2/UDB, Adabas, SQL Server, Oracle Output: HTML,PDF,Excel2K
WF Client: Servlet, CGI
 
Posts: 133 | Registered: May 12, 2007Report This Post
Expert
posted Hide Post
Since you are on 76, why not try using the PDF Layout Painter and set the output format to Excel. The PDF Layout Painter is not just for pdf output but can prepare compound reports for html and excel as well.


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
Guru
posted Hide Post
Here's a simplified example of a multi-tabbed Excel spreadsheet with customized tab names as Sam suggested.

TABLE FILE CAR
PRINT MODEL 
BY COUNTRY
ON TABLE SET STYLE *
TYPE=REPORT,
SIZE=9,
TITLETEXT='Car Detail',
GRID=ON,
BORDER=OFF,
BACKCOLOR=WHITE,
$
ENDSTYLE
ON TABLE PCHOLD FORMAT EXL2K OPEN
END
TABLE FILE CAR
COUNT MODEL
BY COUNTRY
ON TABLE SET STYLE *
TYPE=REPORT,
SIZE=9,
TITLETEXT='Car Summary',
GRID=ON,
BORDER=OFF,
BACKCOLOR=WHITE,
$
ENDSTYLE
ON TABLE PCHOLD FORMAT EXL2K CLOSE
END  


For additional information, try looking up "Creating Excel Compound Reports" in the manual
[U]Creating Reports with WebFOCUS Language[/U]
.


Glenda

In FOCUS Since 1990
Production 8.2 Windows
 
Posts: 301 | Location: Galveston, Texas | Registered: July 07, 2004Report This Post
Guru
posted Hide Post
Hi Glenda

This is very interesting but I am getting an error running it.
The output is HTML code inside the spreadsheets.

Regards.


WebFocus 7.6.5
AND WebLogic server as web server
sql2005 as database server
 
Posts: 273 | Location: Europe | Registered: May 31, 2007Report This Post
Virtuoso
posted Hide Post
Majid, it may help if you tell us what error message it is that you're getting.
And how do you notice that the code in excel is html? Are the tags and such preent in the cells or what? Please be a bit more specific, that way we can try to help you better.
For what it is worth, I ran Glenda's report and all just came out fine in excel 2003.


GamP

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

  
SET COMPOUND = OPEN
EX smohanty_fex1
SET COMPOUND = CLOSE
EX smohanty_fex2


-*smohanty_fex1 starts here

TABLE FILE CAR
PRINT
     COUNTRY
     CAR
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
     TITLETEXT='Sambit',
$
TYPE=REPORT,
     GRID=OFF,
     FONT='TIMES NEW ROMAN',
     SIZE=10,
     COLOR='BLACK',
     BACKCOLOR='NONE',
     STYLE=NORMAL,
$
ENDSTYLE
END


-*smohanty_fex2 starts here
TABLE FILE CAR
PRINT
     COUNTRY
     CAR
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
     TITLETEXT='Mohanty',
$
TYPE=REPORT,
     GRID=OFF,
     FONT='TIMES NEW ROMAN',
     SIZE=10,
     COLOR='BLACK',
     BACKCOLOR='NONE',
     STYLE=NORMAL,
$
ENDSTYLE
END




Note :
3 fex files are there
1st one is the calling fex file and smohanty_fex1/2 are the called fex files.
try this way and u will get the output.

tks/sam


WF Server: 7.1.4 on Z/OS and Linux, ReportCaster
Data: DB2, DB2/UDB, Adabas, SQL Server, Oracle Output: HTML,PDF,Excel2K
WF Client: Servlet, CGI
 
Posts: 133 | Registered: May 12, 2007Report This Post
Guru
posted Hide Post
Hi,

even with the last proposal i am getting an Excel output with HTML Tags instead of data.
I am using Excel 2000 and not 2003.
Is that the reason?

Majid


WebFocus 7.6.5
AND WebLogic server as web server
sql2005 as database server
 
Posts: 273 | Location: Europe | Registered: May 31, 2007Report This Post
Expert
posted Hide Post
Majid,

Spot on. The output you are seeing is actually XML (that Excel interprets) and for that you need the later version of Excel.

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
Guru
posted Hide Post
Thanks Tony, this will give me an extra hard time to make our company migrate.

Regards.


WebFocus 7.6.5
AND WebLogic server as web server
sql2005 as database server
 
Posts: 273 | Location: Europe | Registered: May 31, 2007Report This Post
Platinum Member
posted Hide Post
i don't recall having xls2k not supporting multisheet ??
is it a excel or w/f version issue ??


WF Server: 7.1.4 on Z/OS and Linux, ReportCaster
Data: DB2, DB2/UDB, Adabas, SQL Server, Oracle Output: HTML,PDF,Excel2K
WF Client: Servlet, CGI
 
Posts: 133 | Registered: May 12, 2007Report This Post
Guru
posted Hide Post
Sam,

When I first starting creating multitabbed excel spreadsheets, any of my users who did not have Excel 2003 experienced the same problem that Majid is now experiencing. We are on WebFOCUS 5.3.6 and when we upgraded the users to Excel 2003, the problem no longer existed.


Glenda

In FOCUS Since 1990
Production 8.2 Windows
 
Posts: 301 | Location: Galveston, Texas | Registered: July 07, 2004Report This Post
Guru
posted Hide Post
Hi Glenda,

Yes it is running under Excel2003.

Thanks


WebFocus 7.6.5
AND WebLogic server as web server
sql2005 as database server
 
Posts: 273 | Location: Europe | Registered: May 31, 2007Report This Post
Member
posted Hide Post
-*******************************************************************************
-* WebFocus Technique: Create an Excel spreadsheet with multiple worksheets.
-* This procedure creates one Excel spreadsheet with five worksheets. The five
-* worksheet tabs will have the value of the first sort field. Note: Only the
-* first sort will be used for the creation of worksheets. Also, a NOPRINT
-* statement following the first sort field does not change the tab names.
-*******************************************************************************
TABLE FILE CAR
PRINT MODEL
RCOST
BY COUNTRY NOPRINT SUBTOTAL
BY CAR
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K BYTOC
END


WF 7.6.10 Unix Oracle
 
Posts: 22 | Location: Milano, Italy | Registered: September 16, 2005Report This Post
Expert
posted Hide Post
Woody,

What you failed to notice in Marian's original post was that she wanted to run two different reports as opposed to one as per your example.

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
Member
posted Hide Post
Tony,

You are correct. I read the post too quickly.

I apologize for my mistake.

W


WF 7.6.10 Unix Oracle
 
Posts: 22 | Location: Milano, Italy | Registered: September 16, 2005Report This Post
Expert
posted Hide Post
Woody, no apologies required, we can all get the wrong end of the stick occasionally Frowner
I just wanted to bring out the point just in case someone comes along and tries the example you gave but with two fexes and couldn't understand why it wouldn't work.

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
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders