[CLOSED] 2 Webfocus requests on one EXCEL spreadsheet

March 26, 2014, 02:43 PM

Mike Pepich

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

March 26, 2014, 02:56 PM

Francis Mariani

Sure - Compound Excel report, in one sheet or multiple sheets (tabs).

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

March 27, 2014, 08:55 AM

You could always use the document composer tool...

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?

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

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!

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

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.

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!

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)

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?

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.

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

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

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...

October 04, 2018, 04:37 PM

RobertF

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

October 05, 2018, 08:48 AM

RobertF

before I give up, anyone?

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.

October 05, 2018, 09:29 AM

J.L. Hinds

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

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.

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

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.

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

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?

October 05, 2018, 11:24 AM

MartinY

ON TABLE PCHOLD FORMAT XLSX TEMPLATE 'MyGlossary.xltx' SHEETNUMBER 1

October 08, 2018, 08:12 AM

RobertF

Thank...whooo hooo!

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.