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. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] 2 Webfocus requests on one EXCEL spreadsheet

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] 2 Webfocus requests on one EXCEL spreadsheet
 Login/Join
 
Member
posted
I am working in WebFocus 7.7.03, and I have a client request that he wants 2 Reports that have different data and headings in one Excel Spreadsheet. Does anyone know if this is possible.

This message has been edited. Last edited by: <Kathryn Henning>,


WebFOCUS 7.7.03
Linux
all output (Excel, HTML, PDF)
 
Posts: 6 | Registered: July 21, 2010Report This Post
Expert
posted Hide Post
Sure - Compound Excel report, in one sheet or multiple sheets (tabs).


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
Expert
posted Hide Post
TABLE FILE CAR
SUM
SALES
BY COUNTRY

HEADING
"CAR SALES"

ON TABLE PCHOLD FORMAT EXL2K OPEN NOBREAK
END

TABLE FILE GGSALES
SUM
DOLLARS
BY REGION

HEADING
"GG SALES"

ON TABLE PCHOLD FORMAT EXL2K CLOSE
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
Guru
posted Hide Post
You could always use the document composer tool...


WebFOCUS 7.7.03/8.0.08
Dev Studio 7.7.03/8.0.08
App Studio 8.0.08
Windows 7
ALL Outputs
 
Posts: 402 | Location: Upland, IN | Registered: June 08, 2012Report This Post
Master
posted Hide Post
This is an oldie but would you have code that put these each in their own tab in the same workbook?


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Virtuoso
posted Hide Post
Using the above sample, to have the result in separate sheet
TABLE FILE CAR
SUM SALES
BY COUNTRY

HEADING
"CAR SALES"

ON TABLE PCHOLD FORMAT XLSX OPEN
END

TABLE FILE GGSALES
SUM DOLLARS
BY REGION

HEADING
"GG SALES"

ON TABLE PCHOLD FORMAT XLSX CLOSE
END
-RUN


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Master
posted Hide Post
Thanks...never would have thought this...if I wanted a third tab does the:

ON PCHILD FORMAT XLS simply lose the:OPEN?


Thanks!


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Platinum Member
posted Hide Post
This example works for me ...

SET EXCELSERVURL = ''
-*
TABLE FILE CAR
PRINT
COUNTRY
CAR
MODEL
BODYTYPE
SEATS
SALES
WHERE RECORDLIMIT EQ 5
ON TABLE SET COMPOUND OPEN
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT='CAR', $
ENDSTYLE
END
-RUN
TABLE FILE GGSALES
PRINT
SEQ_NO
CATEGORY
PCD
PRODUCT
DATE
UNITS
DOLLARS
WHERE RECORDLIMIT EQ 5
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT='GGSALES', $
ENDSTYLE
END
-RUN
TABLE FILE BROKERS
PRINT
BROKER_ID
DEPARTMENTS
BROKER_NAME
WHERE RECORDLIMIT EQ 5
ON TABLE SET COMPOUND CLOSE
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT='BROKERS', $
ENDSTYLE
END
-RUN


WebFocus 8.201M, Windows, App Studio
 
Posts: 227 | Location: Lincoln Nebraska | Registered: August 12, 2008Report This Post
Master
posted Hide Post
And if you want to use templates, you can place reports on specific sheets in the workbook by using the following syntax:

ON TABLE HOLD AS tmp1 FORMAT XLSX TEMPLATE template_name SHEETNUMBER 1

followed by

ON TABLE HOLD AS tmp2 FORMAT XLSX TEMPLATE tmp1.xlsm SHEETNUMBER 2

etc.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
Master
posted Hide Post
quote:
ON TABLE HOLD AS tmp1 FORMAT XLSX TEMPLATE template_name SHEETNUMBER 1


I can really use this but want to understand...

Lets say I have an Excel file stored on a share that has a Definitions tab; a glossary if terms if you will:

X:\Finance\Myreports\glossarytab.xlsx.

Is it possible to run a WF report and tell WF to open the glossarytemplate file and insert my report onto a new tab for which I provide a name? This would be awesome!


I am on WF 8008.

I am trying to decipher the syntax below:
ON TABLE HOLD AS tmp1 FORMAT XLSX TEMPLATE template_name SHEETNUMBER 1


Is template_name they path to my my file ? Do I enter it in quotes:
'X:\Finance\MyReports\glossarytemplate.xlsx'

Is SHEETNUMBER 1 a parameter or the tab name I am placing the data onto or a new tab the data will be placed onto.



THANKS!


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Virtuoso
posted Hide Post
Let pretend that you want to put your data starting on sheet two (2) since sheet one (1) is your glossary tab (info tab)

ON TABLE HOLD AS tab1 FORMAT XLSX TEMPLATE glossarytemplate.xltx SHEETNUMBER 2

Then to add another tab (third one)
ON TABLE HOLD AS tab2 FORMAT XLSX TEMPLATE tab1.xltx SHEETNUMBER 3

And as the last one (that will "close" the excel file and return the result)
ON TABLE PCHOLD FORMAT XLSX TEMPLATE tab2.xltx SHEETNUMBER 4


BUT first, if you want to have the tab named as you wish, you should have your template with the tab already defined and properly named.
Also, the template has to be in a folder that is accessible by WebFOCUS path (I normally put them under baseapp)


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Master
posted Hide Post
Ok, I am ignorant here...I never knew there was an xltx file type...outside of WF, we have what we *call* 'templates'...they are type xlsx or xlsb..essentially we just store them in a folder called: templates and when we need them we first refresh the data source and then simply open the particular 'template' file refresh it and save it elsewhere....BTW, can this process be used with a xlsx or xlsb file?

Anyway...for now I created an xltx file; accessible by WF: (well at least the xlsx files are as accessible, we hyperlink to some of these via dashboards so I assume the xltx file type would not make a difference.)

The file is named:

X:\Finance\Templates\Myglossary.xltx

The glossary is on a tab named: Glossary

There is a *blank* tab therein named: CarData


I am not having much luck.


This is the code:

TABLE FILE CAR
SUM
CAR.BODY.RETAIL_COST
BY LOWEST CAR.COMP.CAR
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL

ON TABLE HOLD AS CarDataTab FORMAT XLSX TEMPLATE X:\Finance\Templates\Myglossary.xltx CarData

ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END


This is the error:

(FOC002) A WORD IS NOT RECOGNIZED: CarData



My assumption iwa what I call CarDataTab above is the hold file and a xlsx file would be created from an Excel TEMPLATE called: X:\Finance\Templates\Myglossary.xltx and the Car data returned would appear on the existing tab: CarData.

Again, the xltx format is new to me but nevertheless my fex ends in error.

Do you see anything?


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Master
posted Hide Post
quote:
X:\Finance\Templates\Myglossary.xltx



also tried this:

TABLE FILE CAR
SUM
CAR.BODY.RETAIL_COST
BY LOWEST CAR.COMP.CAR
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL

ON TABLE HOLD AS tab1 FORMAT XLSX TEMPLATE X:\Finance\Templates\Myglossary.xltx SHEETNUMBER 2
ON TABLE PCHOLD FORMAT XLSX TEMPLATE tab2.xltx SHEETNUMBER 3

ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END

Error:
(FOC3289) TEMPLATE FILE: Error opening file
(FOC3317) Error processing template file.


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Master
posted Hide Post
ok, I hunted around and understand SHEETNUMBER n is a reserved word...still it will not work, I deleted tab: CarData from my template file:

TABLE FILE CAR
SUM
CAR.BODY.RETAIL_COST
BY LOWEST CAR.COMP.CAR
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL

ON TABLE HOLD AS tab1 FORMAT XLSX TEMPLATE X:\Finance\Templates\Myglossary.xltx SHEETNUMBER 2
ON TABLE PCHOLD FORMAT XLSX TEMPLATE tab2.xltx SHEETNUMBER 3

ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Virtuoso
posted Hide Post
Robert, you miss an important part : each ON TABLE HOLD is for each tab and cannot be used in the same request.
Something such as below
TABLE FILE CAR
SUM  CAR.BODY.RETAIL_COST
BY LOWEST CAR.COMP.CAR
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE NOTOTAL

ON TABLE HOLD AS tab1 FORMAT XLSX TEMPLATE X:\Finance\Templates\Myglossary.xltx SHEETNUMBER 2

ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
 INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END 

TABLE FILE CAR
SUM DEALER_COST
BY LOWEST COUNTRY
BY CAR
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE NOTOTAL

ON TABLE PCHOLD FORMAT XLSX TEMPLATE tab1.xltx SHEETNUMBER 3

ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
 INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END 


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Master
posted Hide Post
Well, I understand better but still no luck. I deleted the CarData tab from my template. Is that ok---keep it simpler for now....?

I checked and this file (X:\Finance\Templates\Myglossary.xltx) is accessible from WF -- I stuck the template on a dashboard via a hyperlink just to make sure it opened.


I ran your exact code.

TABLE FILE CAR
SUM CAR.BODY.RETAIL_COST
BY LOWEST CAR.COMP.CAR
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL

ON TABLE HOLD AS tab1 FORMAT XLSX TEMPLATE X:\Finance\Templates\Myglossary.xltx SHEETNUMBER 2

ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END

TABLE FILE CAR
SUM DEALER_COST
BY LOWEST COUNTRY
BY CAR
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL

ON TABLE PCHOLD FORMAT XLSX TEMPLATE tab1.xltx SHEETNUMBER 3

ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END


I get this error now:

0 NUMBER OF RECORDS IN TABLE= 18 LINES= 10
(FOC3289) TEMPLATE FILE: Error opening file
(FOC3317) Error processing template file.
0 NUMBER OF RECORDS IN TABLE= 18 LINES= 10
(FOC3289) TEMPLATE FILE: Error opening file
(FOC3317) Error processing template file


Perhaps this does not work in WF 8008????

BTW, I tried putting single quotes arount the file path too....This would be amazing if I can get it to work...


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Master
posted Hide Post
Hey, btw...thanks for trying to help!!!!


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Master
posted Hide Post
before I give up, anyone?


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Virtuoso
posted Hide Post
My mistake.
On second table file must hold like this (xltx changed by xlsx). You need to reference the template only once.
ON TABLE PCHOLD FORMAT XLSX TEMPLATE tab1.xlsx SHEETNUMBER 3


I also have my template under baseapp.
I'm not sure that you can reference "X:\Finance\Templates\" since the location is probably not in the APPPATH.

Otherwise, my sample does work properly once the template has at least 3 pre-defined tab in it.


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Gold member
posted Hide Post
Make sure the template file has more tabs then you need.


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 74 | Registered: December 23, 2013Report This Post
Virtuoso
posted Hide Post
quote:
Make sure the template file has more tabs then you need.

No need for that. I have several template that only have the number of tab that I need.


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Master
posted Hide Post
OK, yes I did not know it had to be saved in the baseapp area. I was not even sure how to do so. I just copied it from my share and pasted to baseapp..it put it in the Other folder...which surprising to me I did not have to reference in my file path.

I also needed to add a few extra tabs.

When I ran the fex it opened and to my surprise the two car files were there!

Now, where/how do I name the tabs...do I do that in the template and just make sure I order my fexes appropriately, or can I name them in the fex....guessing the former?

Also, I wonder if everyday users who would select the fex off a hyperlink on the dashboard page will be secured for all this.

TABLE FILE CAR
SUM CAR.BODY.RETAIL_COST
BY LOWEST CAR.COMP.CAR
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL

ON TABLE HOLD AS tab1 FORMAT XLSX TEMPLATE &APPROOT/baseapp/MyGlossary.xltx SHEETNUMBER 2

ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END


TABLE FILE CAR
SUM DEALER_COST
BY LOWEST COUNTRY
BY CAR
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL

ON TABLE PCHOLD FORMAT XLSX TEMPLATE tab1.xlsx SHEETNUMBER 3

ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Expert
posted Hide Post
This is very good information. Bookmarked it for when I need it.
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Master
posted Hide Post
I have discovered SHHETNUMBER n seems to refer to the order I place the sheets in the template. I think...So if my glossary is the second tab from left to right, that code above overwrites the file. But if I make it say the 4th tab..then I get CAR data on the two that precede it.


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Virtuoso
posted Hide Post
quote:
Now, where/how do I name the tabs...do I do that in the template

Name them in the template

quote:

it put it in the Other folder...which surprising to me I did not have to reference in my file path.

Since baseapp is in the path all other sub-folder are accessible.

quote:

I have discovered SHHETNUMBER n seems to refer to the order I place the sheets in the template. I think...So if my glossary is the second tab from left to right, that code above overwrites the file. But if I make it say the 4th tab..then I get CAR data on the two that precede it.

Yes. In my sample I have assume that the Glossary is in first tab so I've placed the two other report in tab 2 and 3.
So you could place the first TABLE FILE result in SHEETNUMBER 1 and the second one in SHEETNUMBER 3 where you will have your glossary in tab 2


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Master
posted Hide Post
Last question...what if I just want to add data to tab ONE...ie the second CAR code was not needed...what does the code look like...

Where / How do I use the ON TABLE PCHOLD which seems to be the one that opens the sucker....needing the ON HOLD first?


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Virtuoso
posted Hide Post
ON TABLE PCHOLD FORMAT XLSX TEMPLATE 'MyGlossary.xltx' SHEETNUMBER 1


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Master
posted Hide Post
Thank...whooo hooo!


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Virtuoso
posted Hide Post
This stuff is killin' me right now. I have templates on this install and write to them, but my new one is crashing and burning. The call --

  
ON TABLE PCHOLD AS Call_Center_Monthly_Summary_&YYMD FORMAT XLSX TEMPLATE 'Call_Center_State_Stats_Template.xltm' SHEETNUMBER 2 


It appears to send to the template (finds it, appears to want to write to it) but the resulting xlsx output reports damaged and after repair presents an empty spreadsheet.

Question -- is there something I need to do to the spreadsheet template to prep it to accept data? I don't recall doing that last time. As it stands, I'm parroting tried and true source code in WF and getting a damaged output. I'm just trying to write into an empty second tab.



 
Posts: 1012 | Location: At the Mast | Registered: May 17, 2007Report 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     [CLOSED] 2 Webfocus requests on one EXCEL spreadsheet

Copyright © 1996-2020 Information Builders