Focal Point
[CLOSED] 2 Webfocus requests on one EXCEL spreadsheet

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/1567038966

March 26, 2014, 02:43 PM
Mike Pepich
[CLOSED] 2 Webfocus requests on one EXCEL spreadsheet
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)
March 26, 2014, 02:56 PM
Francis Mariani
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
March 26, 2014, 03:01 PM
Francis Mariani
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
March 27, 2014, 08:55 AM
J
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
September 27, 2018, 04:56 PM
RobertF
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
September 28, 2018, 07:40 AM
MartinY
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
September 28, 2018, 08:35 AM
RobertF
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
September 28, 2018, 09:13 AM
jfr99
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
September 28, 2018, 09:22 AM
jgelona
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.
October 04, 2018, 01:32 PM
RobertF
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
October 04, 2018, 02:02 PM
MartinY
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
October 04, 2018, 03:04 PM
RobertF
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
October 04, 2018, 03:19 PM
RobertF
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
October 04, 2018, 03:56 PM
RobertF
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
October 04, 2018, 04:08 PM
MartinY
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
October 04, 2018, 04:37 PM
RobertF
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
October 04, 2018, 04:37 PM
RobertF
Hey, btw...thanks for trying to help!!!!


WebFOCUS 8206.08
Windows, All Outputs
October 05, 2018, 08:48 AM
RobertF
before I give up, anyone?


WebFOCUS 8206.08
Windows, All Outputs
October 05, 2018, 09:14 AM
MartinY
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
October 05, 2018, 09:29 AM
J.L. Hinds
Make sure the template file has more tabs then you need.


WebFOCUS 7.6
Windows, All Outputs
October 05, 2018, 10:04 AM
MartinY
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
October 05, 2018, 10:21 AM
RobertF
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
October 05, 2018, 10:23 AM
Doug
This is very good information. Bookmarked it for when I need it.
October 05, 2018, 10:44 AM
RobertF
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
October 05, 2018, 10:45 AM
MartinY
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
October 05, 2018, 11:13 AM
RobertF
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
October 05, 2018, 11:24 AM
MartinY
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
October 08, 2018, 08:12 AM
RobertF
Thank...whooo hooo!


WebFOCUS 8206.08
Windows, All Outputs
November 17, 2020, 11:23 AM
John_Edwards
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.