Has anyone gotten an Excel template file to work with output in multiple tabs?

The code I'm using is like this:

TABLE FILE RAWVA
SUM VAL
ACROSS CAT
ON TABLE PCHOLD AS TMPL1 FORMAT EXL2K OPEN TEMPLATE ValueAdd SHEETNUMBER 1
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT='VA by CAT', $
ENDSTYLE
END
-RUN


TABLE FILE RAWVA
SUM VAL
BY CAT
ON TABLE PCHOLD AS TMPL1 FORMAT EXL2K CLOSE TEMPLATE test SHEETNUMBER 2
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT='Chart', $
ENDSTYLE
END
-RUN


When it runs I get this error message box:
"Problems During Load"
Problems came up in the following areas during load:
Missing file: C:\IBIT0001.xht
Missing file: C:\IBIT0002.xht

If I cancel, I get an Excel document with multiple tabs, with the corect tab names, but no data in them.


Anyone else out there tried to do this?

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

Sorry - should have mentioned:

WebFOCUS v.7.1
Excel 2003
server running Windows 2000

From the IBI website:

EXL2L Template Feature:
Is there a way to create a COMPOUND report using the EXL2K Template feature
introduced in WebFOCUS Release 7.1?


Solution:

Yes, it is possible to create a COMPOUND report using the EXL2K Template
feature. However, it is not via the EXL2K COMPOUND report syntax (i.e.
using OPEN/NOBREAK/CLOSE commands). Instead, it is running one EXL2K
request using the template feature with HOLD instead of PCHOLD and writing
that HOLD file to a new file with a .mht extension, so this new file can
be used as the template for the NEXT EXL2K report, etc. You can actually
repeat this until the last request. The last request will use PCHOLD FORMAT
EXL2K TEMPLATE with the TEMPLATE name of the prior HOLD file (again, with
a .mht extension). Each of the requests MUST reference a DIFFERENT SHEETNUMBER
(EXCEL worksheet) so as to avoid overwriting the data. You will need to FILEDEF
or APP FI each of the prior HOLD files (again, with the .mht extension). The
first request will reference the original .mht file with all of the
appropriate number of worksheets to satisfy the number of requests being run
(to hold the raw data). There will be another worksheet that will pull the
data from these other worksheets and display it to the user.

Sample Technique using the CAR FOCUS database:

-* Original .mht template file in this example is called COMPXLS_SINGLE.mht
APP HOLD SESSION
APP FI COMPXLSH DISK COMPXLSH.MHT
TABLE FILE CAR
HEADING
"Report 1: W Germany"
PRINT DCOST BY COUNTRY BY CAR
IF COUNTRY EQ 'W GERMANY'
-* Using HOLD instead of PCHOLD and referencing ORIGINAL template file (.mht).
-* Note that the HOLD AS name is FILEDEF'd or APP FI'd to a file with .mht
-* extension.
ON TABLE HOLD AS COMPXLSH FORMAT EXL2K TEMPLATE 'COMPXLS_SINGLE' SHEETNUMBER 1
END
TABLE FILE CAR
HEADING
"Report 2: England"
PRINT RCOST BY COUNTRY BY CAR BY MODEL
IF COUNTRY EQ ENGLAND
-* Since this is the last request, PCHOLD is used instead of HOLD.
-* Note that the template filename is the HOLD file (.mht file) from prior
-* request.
ON TABLE PCHOLD FORMAT EXL2K TEMPLATE 'COMPXLSH' SHEETNUMBER 2
END

From my localhost, without template:

TABLE FILE CAR
SUM RETAIL_COST
ACROSS COUNTRY
BY MODEL
ON TABLE PCHOLD FORMAT EXL2K OPEN
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT='VA by CAT', $
ENDSTYLE
END
-RUN
TABLE FILE CAR
SUM RETAIL_COST
BY MODEL
ON TABLE PCHOLD FORMAT EXL2K CLOSE
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT='Chart', $
ENDSTYLE
END
-RUN


EDIT: also Excel Template

This message has been edited. Last edited by: Tom Flynn,

I've got several jobs that create multi-tab spreadsheets using nothing but PCHOLD. They run from RC and Dev Studio just fine. I haven't tried them in MRE.

There is a difference between a multisheet spreadsheet and a template, and Tom has covered both fairly well. Unless you have formulas or are pulling data from one sheet to another use the compound open/close logic and create a multisheet workboot, it is easier. You do have to create a template in EXCEL first and save it as mht format in your app path. Include any formulas and/or extra sheets in that file.

Compound with EXL2K does work, even EXL2K generating multiple tabs. MWatson's plan to invoke EXL2K COMPOUND with each sheet having it's own TEMPLATE I think may be too ambitious.

Can you try using one TEMPLATE

Thanks Tom - there were a couple of issues I had to work through, but I did end up getting it to work.

Like Tom said, you have to output each report as an Excel template file, that you then use for the next report.

APP FI COMPXLSH DISK COMPXLSH.MHT
APP FI COMPXLSH1 DISK COMPXLSH1.MHT
APP FI COMPXLSH2 DISK COMPXLSH2.MHT

ON TABLE HOLD AS COMPXLSH FORMAT EXL2K TEMPLATE 'BaseTemplate' SHEETNUMBER 1
...
ON TABLE HOLD AS COMPXLSH1 FORMAT EXL2K TEMPLATE 'COMPXLSH' SHEETNUMBER 2
...
ON TABLE HOLD AS COMPXLSH2 FORMAT EXL2K TEMPLATE 'COMPXLSH1' SHEETNUMBER 3
...
ON TABLE PCHOLD FORMAT EXL2K TEMPLATE 'COMPXLSH2' SHEETNUMBER 4


You run into a problem if one of your reports produces no data, and breaks the 'chain'. There may be a better workaround, but what I did is make a FOCUS database file with one record that said "No data..", and each time I built a new hold/template file, I first sent the output of that FOCUS database file to it. That way it created the new hold file with "No data" in a given tab, then overwrote that hold file if there really WAS any data to go in that tab.

Mr. Watson,

Don't thank me, YOU DID IT. I just gave you some documentation, YOU incorporated it.

Now THAT'S IMPRESSIVE.

Well Done...

MWatson,

I have tried this but could not get it to work. When I tried
..
APP FI TEST_TMPL DISK TEST_TMPL.MHT
...
ON TABLE HOLD AS TEST_TMPL FORMAT EXL2K TEMPLATE 'ACTUAL_TMPL' SHEETNUMBER 1
...
ON TABLE PCHOLD FORMAT EXL2K TEMPLATE 'TEST_TMPL' SHEETNUMBER 2

I get the error (FOC3289) EXL2K TEMPLATE FILE: Error opening file (FOC009) INCOMPLETE REQUEST STATEMENT.

And when I tried --

..
APP FI ACTUAL_TMPL DISK ACTUAL_TMPL.MHT
...
ON TABLE HOLD AS ACTUAL_TMPL FORMAT EXL2K TEMPLATE 'ACTUAL_TMPL' SHEETNUMBER 1
...
ON TABLE PCHOLD FORMAT EXL2K TEMPLATE 'ACTUAL_TMPL' SHEETNUMBER 2
...

it does not give me any error but the generated report shows empty sheet1 though there is data in it.

Any idea?

in each step do you FILEDEF or APP FI your output file as .mht? In the first step, the .mht file that you created in EXCEL is picked up:
APP FI ACTTMPL DISK ACTUAL_TMPL.MHT
TABLE FILE filename
...
ON TABLE HOLD AS TESTTMP1 FORMAT EXL2K TEMPLATE 'ACTTMPL' SHEETNUMBER 1
END
...
in your second sheet you have to FILEDEF your new output
APP FI TESTTMP2 DISK TESTTMP2.MHT
TABLE FILE filename
...
ON TABLE PCHOLD AS TESTTMP2 FORMAT EXL2K TEMPLATE 'TESTTMP1' SHEETNUMBER 2
END

You may need to specify the path in your APP FI and you may need to limit the filename (in my case ACTTMPL and TESTTMP2) to 8 characters or less.

Thanks Pat. I missed to FILEDEF the new output before writing to second sheet. I will try with this now.

Pat, I tried what you suggested, but still no luck

I don't know if a FILEDEF for a template will work. I've never tried it. I always put the templates in baseapp on the Reporting Server and they are always found. No FILEDEF needed.

BTW, I got one report that builds a workbook with over 100 worksheets.

JG,
The original template does go in the baseapp, but any time I have had to write to more than one sheet (and it has been a while since I have done this as you can't use templates in 5.3) I had to start the second sheet where I saved the first and each step had to have a filedef for the output which became my new template.

jgelona, Pat,

The FILEDEF is due to the age old reason - if it's not in the APP PATH then FILEDEF it.



T

Should the temporary (intermediate .mht files) templates exist physically in the app path? I have only one .mht file (ACTUAL_TMPL.MHT) in my app path.

Not unless you need them for some obscure reason - such as testing and process flow checking.

Just FILEDEF them to disk and that will create them in the EDATEMP folder and they will be erased at end of process.

Just make sure that you either deliver the final output to the user (PCHOLD) or that you HOLD the file to a Application or other Reporting server folder using FILEDEF.

T

Thanks Tony.

I have to write to two sheets and so using one temporary .mht which I FILEDEF before use. But still it gives me the error "(FOC3289) EXL2K TEMPLATE FILE: Error opening file" on the second template statement.

Many thanks to all of you.

It is woking fine now. I missed -RUN after FILEDEF.

Tony,

It's been a while since I have done this, but my experience was the same as Helpme. If I didn't FILEDEF the interim sheets all I had when I finished was my last sheet in the original template even though everything was being stored in baseapp and even if I did an APP PATH in the program.

Here's how I FINALLY got it to work. For simplicity, just two reports.
The template in baseapp is called exltemp.mht.

First, the APP HOLD and APP FI:

APP HOLD BASEAPP
APP FI TEMP2 DISK baseapp/TEMPLATE2.MHT
-RUN

The HOLD statement for the first report:

ON TABLE HOLD AS TEMP2 FORMAT EXL2K TEMPLATE baseapp/exltemp SHEETNUMBER 1

Now, this is what took me forever to finagle. Rather than using TEMP2 in the second (PCHOLD) report, I use the name of the filedef'd template rather than the alias. I don't know why I can't use the alias, but every permutation I tried to use (with/without baseapp/) resulted in the "template not found" error.

ON TABLE PCHOLD FORMAT EXL2K TEMPLATE template2 SHEETNUMBER 2

This message has been edited. Last edited by: J.Hines,

J.Hines, we use this a lot and I've never come across this issue because I have always used the same name for the DDNAME and Template name.

FILEDEF TEMPLAT1 DISK templat1.mht
FILEDEF TEMPLAT2 DISK templat2.mht

The HOLD and PCHOLD want the actual template name not the DDNAME.

Actually, I wouldn't put them in baseapp because if someone else runs the same report with different parameters at the same time, they will clobber each other. The format above uses the default edatemp folder.

Has anyone tried out the concept in this thread, but using format EXL07 where the first template is an .XLTX file, rather than .MHT?

It seems as if filedef-ing the HOLD file with extention XLTX creates an unreadable template file...