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] Two Spreadsheets at once

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Two Spreadsheets at once
 Login/Join
 
Master
posted
I am trying to create two reports from the same fex, but I don't think that I can do it because we are running EXCEL 2000.

Any suggestions?

Thanks! Confused

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


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Expert
posted Hide Post
What do you mean by two reports.
Are they completely separate ?
Do they both need to be returned to the clients browser ?

Can they be compound reports ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Gold member
posted Hide Post
This is an example of a compound report:

TABLE FILE CAR
PRINT *
ON TABLE HOLD AS CARHOLD
END

TABLE FILE CARHOLD
PRINT COUNTRY CAR MODEL SALES
WHERE COUNTRY EQ 'ENGLAND';
HEADING
"REPORT 1"
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
-*ON TABLE PCHOLD FORMAT PDF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN, SQUEEZE=ON, ORIENTATION=PORTRAIT,$
TYPE=REPORT, GRID=OFF, FONT='ARIAL', SIZE=9,$

ENDSTYLE
ON TABLE PCHOLD FORMAT EXL2K OPEN NOBREAK
END


TABLE FILE CARHOLD
PRINT
COUNTRY CAR MODEL SALES
WHERE COUNTRY EQ 'ITALY';
" "
" REPORT 2 "
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
-*ON TABLE PCHOLD FORMAT PDF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN, SQUEEZE=ON, ORIENTATION=PORTRAIT,$

ENDSTYLE
ON TABLE PCHOLD FORMAT EXL2K CLOSE
END


WebFOCUS 8103, Windows, App Studio
 
Posts: 80 | Location: NYC | Registered: November 13, 2008Report This Post
Master
posted Hide Post
You might also be able to use BYTOC. In either case, you need Excel 2000 or higher. Neither COMPOUND or BYTOC will work with 97.
TABLE FILE CAR
PRINT CAR MODEL SALES 
BY COUNTRY 
WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY'
HEADING
"REPORT FOR <COUNTRY"
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN, SQUEEZE=ON, ORIENTATION=PORTRAIT,$
TYPE=REPORT, GRID=OFF, FONT='ARIAL', SIZE=9,$

ENDSTYLE
ON TABLE PCHOLD FORMAT EXL2K BYTOC
END

  


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
Master
posted Hide Post
Here is what I am testing with...

I would like to provide Report #1 and #2 as EXCEL Spreadsheets, and I need them to be
tabbed, or be able to print each as a separate page.

I am having an issue because my workplace is running the Office2000 version of EXCEL.

TABLE FILE CAR
PRINT
CAR
MODEL
DEALER
RETAIL
BY COUNTRY
ON TABLE HOLD AS ALLCARS
END
-*
TABLE FILE ALLCARS
-* Report #1
PRINT
CAR
MODEL
BY COUNTRY
WHERE COUNTRY NE 'ENGLAND';
END
-*
TABLE FILE ALLCARS
-* Report #2
PRINT
CAR
MODEL
RETAIL
BY COUNTRY
WHERE COUNTRY EQ 'ENGLAND';
WHERE RETAIL LE 10000;
END
-RUN


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Expert
posted Hide Post
If you are running 7.6.6 as your signature indicates, you could try the PDF Layout Painter with the output type specified as Excel. That would give you a tab for each report.


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
Expert
posted Hide Post
Do you want to create two separate Excel files by running one fex with that one fex having two "ON TABLE PCHOLD FORMAT EXCEL" statements? If so, I do not believe that you can do that as you can only open one excel file per fex execution.




   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
Master
posted Hide Post
Thanks Ginny,

Does anyone have a sample of that with the car or empfile?


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Expert
posted Hide Post
SET HTMLARCHIVE=ON
COMPOUND LAYOUT PCHOLD FORMAT EXL2K
UNITS=IN, $
SECTION=section1, LAYOUT=ON, METADATA='0.5^0.5^0.5^0.5', MERGE=OFF, ORIENTATION=PORTRAIT, PAGESIZE=Letter, $
PAGELAYOUT=1, NAME='Page layout 1', text='Page layout 1', TOC-LEVEL=1, BOTTOMMARGIN=0.5, TOPMARGIN=0.5, METADATA='BOTTOMMARGIN=0.5,TOPMARGIN=0.5,LEFTMARGIN=0,RIGHTMARGIN=0,', $
COMPONENT='report1', TEXT='report1', TOC-LEVEL=2, POSITION=(1.775 0.832), DIMENSION=(4.480 3.957), METADATA='Z-INDEX: 100; LEFT: 1.775in; OVERFLOW: auto; WIDTH: 4.48in; POSITION: absolute; TOP: 0.832in; HEIGHT: 3.957in', $
COMPONENT='report2', TEXT='report2', TOC-LEVEL=2, POSITION=(0.938 5.516), DIMENSION=(5.833 4.686), METADATA='Z-INDEX: 100; LEFT: 0.938in; OVERFLOW: auto; WIDTH: 5.833in; POSITION: absolute; TOP: 5.516in; HEIGHT: 4.686in', $
END
SET COMPONENT='report1'
-*component_type report
-INCLUDE ggsales_compound_rpt
SET COMPONENT='report2'
-*component_type report
-INCLUDE centsales_rpt
COMPOUND END


You can write your own 2 reports on demo files and substitue their names on the -INCLUDE statements.


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
Master
posted Hide Post
Will try this...Thanks!


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Expert
posted Hide Post
Ginny,

I replaced the two "INCLUDE" fexes with two different, but basic, "TABLE FILE CAR" requests and I only get one file with two tabs. However, I thought that Tomsweb originally wanted to produce two separate excel files from within one fex.
quote:
I am trying to create two reports from the same fex
Is that correct?

PS: Ginny's solution works in 764 too.
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Virtuoso
posted Hide Post
And later on Tom said:
quote:
I would like to provide Report #1 and #2 as EXCEL Spreadsheets, and I need them to be tabbed, or be able to print each as a separate page.

So Ginny's solution should be ok.


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Expert
posted Hide Post
Tom,
Are you "settling for" the tabbed resolution? Or, do you really want the "two separate and discrete Excel files" as the result of the execution of a single fex?
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Guru
posted Hide Post
Try coding the first report as
FORMAT EXL2K OPEN

and the second report as

FORMAT EXL2K CLOSE


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
Virtuoso
posted Hide Post
Tom,

If you want the reports tabbed, here is a solution:

  
-* File tomsweb5.fex
TABLE FILE CAR
PRINT
CAR
MODEL
DEALER
RETAIL
BY COUNTRY
ON TABLE HOLD AS ALLCARS
END
-*
DEFINE FILE ALLCARS
TAB/I1=IF COUNTRY NE 'ENGLAND' THEN 1 ELSE IF RETAIL LE 10000 THEN 2 ELSE 0;
END

TABLE FILE ALLCARS
PRINT
CAR
MODEL
RETAIL
BY TAB NOPRINT
BY COUNTRY
WHERE TAB NE 0;
ON TABLE PCHOLD FORMAT EXL2K BYTOC
END


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Master
posted Hide Post
I tried Daniel's suggestion:

quote:
TABLE FILE CAR
PRINT
CAR
MODEL
DEALER
RETAIL
BY COUNTRY
ON TABLE HOLD AS ALLCARS
END
-*
DEFINE FILE ALLCARS
TAB/I1=IF COUNTRY NE 'ENGLAND' THEN 1 ELSE IF RETAIL LE 10000 THEN 2 ELSE 0;
END

TABLE FILE ALLCARS
PRINT
CAR
MODEL
RETAIL
BY TAB NOPRINT
BY COUNTRY
WHERE TAB NE 0;
ON TABLE PCHOLD FORMAT EXL2K BYTOC
END


and I got this garbage when EXCEL opened
in my browser.

Perhaps there is some setting I need to change or activate in the WebFOCUS profile whatever,
but I wanna believe that this can be done even
if you are using Microsoft Excel 2000.


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Master
posted Hide Post
I tried Daniel's code and it worked fine for me. Please confirm that you have Excel 2000 and not Excel 97. I have had an issue where we created a report with BYTOC that a user with 97 got the underlying code rather than the report when they tried to open it.


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
Platinum Member
posted Hide Post
I have a compound report which I use the bytoc command in. It is one fex that generates four separate tabs. In my fex I use the following command ON TABLE SET COMPOUND 'BYTOC' Your highest sort field must be the field you want the tabs generated on.


WF 7.7.05
HP-UX - Reporting Server, Windows 2008 - Client, MSSQL 2008, FOCUS Databases, Flat Files
HTML, Excel, PDF
 
Posts: 149 | Location: Dallas, TX | Registered: June 08, 2007Report This Post
Master
posted Hide Post
I opened EXCEL and clicked on Help on the toolbar.
I then clicked on About Microsoft Excel, where
the dialogue box stated across the top: Microsoft
Excel 2000 (9.0.4402 SR-1).

When I tried Daniel's suggestion with ON TABLE PCHOLD
FORMAT EXL2K BYTOC, I got the underlying code rather
than the report when I opened it up.

I am using WF 766

What?!?! Idea


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Platinum Member
posted Hide Post
BYTOC is only available with excel 2002 or higher. From the documentation:

quote:
Excel Table of Contents (TOC) enables you to generate a multiple worksheet report in which a separate worksheet is generated for each value of the first BY field in the FOCUS report.

Note: This feature can be used only with EXCEL 2002 or higher releases because it requires the Web Archive file format, which was not available in Excel 2000 and earlier releases.



WF 7.6.11
Output: HTML, PDF, Excel
 
Posts: 123 | Location: UK | Registered: October 09, 2003Report This Post
<JG>
posted
Sorry Tom you can not do it in any way shape or form unless you convert the WebFOCUS output to a binary using some sort of converter.

quote:
Note: This feature can be used only with EXCEL 2002 or higher releases because it requires the Web Archive file format, which was not available in Excel 2000 and earlier releases .
 
Report This Post
Master
posted Hide Post
Thanks everyone!


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Platinum Member
posted Hide Post
Tom - If you are executing this application on a mainframe server, please see if the following would work for you to split output to multiple excel files.
The concept is to build a JCL from inside the WF fex and submit it to email each file individually.

High level Steps:

1) Go thru the necessary initial processing steps and generate your "master" hold file.
2) Split the master-hold file into multiple individual hold files, the way you want the final output split.
Say you name them HOLD1, HOLD2, HOLD3, etc., sequentially.
3) -SET a variable to store the number of output files from above. (2 in your case, will work fine for any number).
4) DYNAM alloc a mainframe flat file (lets say FLAT-0 for now)
5) -WRITE your shop standard JOB card to FLAT-0
6) -REPEAT loop start with &variable counter from 1 to &count in #3 above
7) DYNAM allocate a unique flat file in each loop flow by using the loop counter in the filename
8) The DD name for each alloc above should also have the loop counter in name for uniqueness like H&CNT..F
8) TABLE FILE HOLD&CNT (will automatically run thru each of your split HOLDx files from #2) above.
9) output of this table-file should be something like this -
ON TABLE HOLD FORMAT EXL2K AS H&CNT..F
10) -WRITE (append) to file FLAT-0 above, a new Job step using H&CNT..F. as the attachment file.
This job step will be whichever standard mainframe jcl utilitly your shop uses to email from mainframe jobs.
11) -ENDLOOP
12) At this point, FLAT-0 will have a jobcard at the top, and an email-utility job-step for each excel file.
13) DYNAM submit FLAT-0.
14) The job will run on the mainframe and send an email per file generated. Works like a charm.

Above steps are just a high level concept. You can get as fancy as you need this to be.
You could hard-code the the email-to parmeter in the job-step -WRITE or define it in a variable too.
The _REPEAT loop times can be dynamic based on the master-hold file contents.
All the the email-utility parameters can be variablized, like the from/to email ids, attachment names. etc.
We actually have an HTML launch page that lets users key-in their email address, runs the app and sends the output to which ever email address was keyed-in by the user.
From the HTML page edits, I have locked the possible domains the users can key-in email addesses, so they do not send to external parties even by mistake.

As long as this process is used on the mainframe, it works with any version of excel, and any version of WF.

Hope this helps.
Sandeep Mamidenna


-------------------------------------------------------------------------------------------------
Blue Cross & Blue Shield of MS
WF.76-10 on (WS2003 + WebSphere) / EDA on z/OS + DB2 + MS-SQL
MRE, BID, Dev. Studio, Self-Service apps & a dash of fun !! Music
 
Posts: 218 | Location: Jackson, MS | Registered: October 31, 2006Report This Post
Platinum Member
posted Hide Post
Tom - I was not sure if you had a chance to see my earlier message. Reposting to get this back to the top of the list.


-------------------------------------------------------------------------------------------------
Blue Cross & Blue Shield of MS
WF.76-10 on (WS2003 + WebSphere) / EDA on z/OS + DB2 + MS-SQL
MRE, BID, Dev. Studio, Self-Service apps & a dash of fun !! Music
 
Posts: 218 | Location: Jackson, MS | Registered: October 31, 2006Report 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] Two Spreadsheets at once

Copyright © 1996-2020 Information Builders