[CLOSED] 2 Webfocus requests on one EXCEL spreadsheet

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, 2005

Ignored post by Francis Mariani posted

Francis Mariani

Expert

posted

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, 2005

Ignored post by Francis Mariani posted

Guru

posted

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, 2012

Ignored post by J posted

Master

posted

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, 2013

Ignored post by RobertF posted

Virtuoso

posted

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: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013

Ignored post by MartinY posted

Master

posted

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, 2013

Ignored post by RobertF posted

jfr99

Platinum Member

posted

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, 2008

Ignored post by jfr99 posted

jgelona

Master

posted

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, 2006

Ignored post by jgelona posted

Master

posted

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, 2013

Ignored post by RobertF posted

Virtuoso

posted

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: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013

Ignored post by MartinY posted

Master

posted

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, 2013

Ignored post by RobertF posted

Master

posted

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, 2013

Ignored post by RobertF posted

Master

posted

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, 2013

Ignored post by RobertF posted

Virtuoso

posted

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: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013

Ignored post by MartinY posted

Master

posted

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, 2013

Ignored post by RobertF posted

Master

posted

Hey, btw...thanks for trying to help!!!!

WebFOCUS 8206.08
Windows, All Outputs

Posts: 603 | Registered: June 28, 2013

Ignored post by RobertF posted

Master

posted

before I give up, anyone?

WebFOCUS 8206.08
Windows, All Outputs

Posts: 603 | Registered: June 28, 2013

Ignored post by RobertF posted

Virtuoso

posted

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: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013

Ignored post by MartinY posted

J.L. Hinds

Gold member

posted

Make sure the template file has more tabs then you need.

WebFOCUS 7.6
Windows, All Outputs

Posts: 74 | Registered: December 23, 2013

Ignored post by J.L. Hinds posted

Virtuoso

posted

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: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013

Ignored post by MartinY posted

Master

posted

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, 2013

Ignored post by RobertF posted

Doug

Expert

posted

This is very good information. Bookmarked it for when I need it.

Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005

Ignored post by Doug posted

Master

posted

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, 2013

Ignored post by RobertF posted

Virtuoso

posted

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: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013

Ignored post by MartinY posted

Master

posted

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, 2013

Ignored post by RobertF posted

Virtuoso

posted

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: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013

Ignored post by MartinY posted

Master

posted

Thank...whooo hooo!

WebFOCUS 8206.08
Windows, All Outputs

Posts: 603 | Registered: June 28, 2013

Ignored post by RobertF posted

John_Edwards

Virtuoso

posted

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, 2007

Ignored post by John_Edwards posted