Focal Point
[SOLVED] Compound Excel Template not working

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

November 25, 2015, 01:17 PM
Pondog
[SOLVED] Compound Excel Template not working
Initially I was tasked with creating a template for an Excel pivot table. Took some doing but got it done (with help from everyone). It was inevitable that the request is now for two pivot tables in the same Excel report; on different tabs.
After much research I was able to create the code, but I've reached an impasse. The code below will work if I run it to HTML. It'll work independently, that is, each pivot table will run to the same Excel template if I run them separately. If I run them together they won'r work. I get the following error:

1
0 NUMBER OF RECORDS IN TABLE= 10557 LINES= 10557
0 NUMBER OF RECORDS IN TABLE= 43976 LINES= 43976
(FOC3291) TEMPLATE FILE: Template specification error

I know it's got to be some obscure code somewhere. However, being a newbie, just about all code is obscure code. Any advice would be greatly appreciated.

Here's the code I'm using.


-?&
SET EXCELSERVURL=''
-RUN
SET WEBARCHIVE = ON
SET COMPOUND = OPEN
ENGINE DB2 SET DEFAULT_CONNECTION UABHS

SQL DB2 PREPARE SQLOUT_TES FOR

SELECT
	CUR_STATUS,
	REASON,
	CUR_ST_DT,
	FIN,
	CLINIC,
	SUNRISE.MRN,
	PT_NAME,
	RDYC_ST_DT,
	DOS_DT,
	CREATEDT AS "MinDateadded",
	MAX ( CASE
		WHEN DATEADDED IS NOT NULL THEN 1
		ELSE 0
	END ) AS COLUMN0000,
	CASE
		WHEN MIN ( DATE ( DAYS ( DATEADDED ) - 1 ) ) < CUR_ST_DT THEN 1
		ELSE 0
	END AS COLUMN0001,
	ENCOUNTER,
	COUNT( DISTINCT TRNNUM ) AS TRNNUM,
	CLINICDESC,
	SCHLOCDSC,
	NOTES,
	USER_ID
FROM
	MSOFILES.SUNRISE SUNRISE
	LEFT OUTER JOIN WAREAVM.VISDETAIL VISDETAIL
	ON
		SUNRISE.FIN = VISDETAIL.HQFINNUM
	LEFT OUTER JOIN WARETES.TESDETAIL TESDETAIL
	ON
		VISDETAIL.VISNUMBER = TESDETAIL.VISNUM AND
		CURDATE( ) = TESDETAIL.DATEADDED AND
		'Edits' = TESDETAIL.STATUS
	LEFT OUTER JOIN WARESCHED.SCHLOCATNS SCHLOCATNS
	ON
		TESDETAIL.LOCATION = SCHLOCATNS.SCHLOCNUM
	LEFT OUTER JOIN WARESCHED.CLINICS CLINICS
	ON
		SCHLOCATNS.SCHCLNNUM = CLINICS.CLINICNUM

WHERE
	CUR_STATUS <> 'Complete'

GROUP BY
	CUR_STATUS,
	REASON,
	CUR_ST_DT,
	FIN,
	CLINIC,
	SUNRISE.MRN,
	PT_NAME,
	RDYC_ST_DT,
	DOS_DT,
	CREATEDT,
	ENCOUNTER,
	CLINICDESC,
	SCHLOCDSC,
	NOTES,
	USER_ID
ORDER BY
	1,
	2

END
END



TABLE FILE SQLOUT_TES

BY  CUR_STATUS AS 'Sunrise Status'
BY  REASON AS 'Reason on Hold'
BY  CUR_ST_DT AS 'Sunrise Stat Dt'
BY  FIN AS 'Sunrise FIN'
BY  CLINIC AS 'Sunrise Clinic'
BY  MRN
BY  PT_NAME AS 'Patient Name'
BY  RDYC_ST_DT AS 'Date Ready to Review'
BY  DOS_DT AS 'Date of Service'
BY  MinDateadded AS 'First Date Added to TES'
BY  COLUMN0000 AS 'Cnt Enc in TES'
BY  COLUMN0001 AS 'Cnt in TES Prior to Staus'
BY  ENCOUNTER AS 'TES Enc#'
BY  TRNNUM AS 'TES Cnt of Trns'
BY  CLINICDESC AS 'TES Clinic'
BY  SCHLOCDSC AS 'TES Location'
BY  NOTES AS 'Sunrise Notes'
BY  USER_ID AS 'Sunrise User ID'
ON TABLE SET BYDISPLAY ON
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT XLSX TEMPLATE 'two_pt_tmplt.xltm' SHEETNUMBER 2
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END
-RUN


SQL DB2 PREPARE SQLOUT_PIV FOR
SELECT DISTINCT
	DOC_DT,
	DOC_TM,
	MRN,
	PT_NAME,
	CLINIC,
	CLINIC_NUM,
	FIN,
	CUR_STATUS,
	CUR_ST_DT,
	CUR_ST_TM,
	OH_ST_DT,
	OH_ST_TM,
	IP_ST_DT,
	IP_ST_TM,
	C_ST_DT,
	C_ST_TM,
	RDYC_ST_DT,
	RDYC_ST_TM,
	DOS_DT,
	DOS_TM,
	NOTES,
	REASON,
	USER_NAME,
	USER_ID,
	LASTUPDATE,
	FILENAME
FROM
	MSOFILES.SUNRISE SUNRISE
ORDER BY
	8,
	22
END
END


TABLE FILE SQLOUT_PIV
PRINT
     MRN
     PT_NAME AS 'Patient Last Name'
     CLINIC AS 'Clinic Code'
     FIN AS 'HQ FIN'
     CUR_STATUS AS 'STATUS'
     CUR_ST_DT AS 'Curr Status Date'
     RDYC_ST_DT AS 'Ready to Review Date'
     DOS_DT AS 'DOS'
     OH_ST_DT AS 'On Hold Date'
     IP_ST_DT AS 'In Process Date'
     C_ST_DT AS 'Complete Date'
     NOTES AS 'Notes'
     REASON AS 'On Hold Reason'
     USER_ID AS 'User'
ON TABLE SET BYDISPLAY ON
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT XLSX TEMPLATE 'two_pt_tmplt.xltm' SHEETNUMBER 4
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END

SET COMPOUND = CLOSE 

This message has been edited. Last edited by: Pondog,


WebFOCUS 8.1.05
Windows, All Outputs
November 26, 2015, 03:56 AM
Wep5622
You're outputting both tables to PCHOLD directly, that won't work.

What does is to HOLD the first Excel sheet and use it as the template for the 2nd one.

Off the top of my head, you need to change:
ON TABLE PCHOLD FORMAT XLSX TEMPLATE 'two_pt_tmplt.xltm' SHEETNUMBER 2

into:
ON TABLE HOLD FORMAT XLSX AS 'WHATEVER' TEMPLATE 'two_pt_tmplt.xltm' SHEETNUMBER 2


And then change:
ON TABLE PCHOLD FORMAT XLSX TEMPLATE 'two_pt_tmplt.xltm' SHEETNUMBER 4

into:
ON TABLE PCHOLD FORMAT XLSX TEMPLATE 'WHATEVER.xltm' SHEETNUMBER 4



WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
November 27, 2015, 10:06 AM
Pondog
Hey Wep5622, that makes sense in a roundabout way. I'll give it a shot. Thanks!!


WebFOCUS 8.1.05
Windows, All Outputs
November 27, 2015, 11:50 AM
Ricardo Augusto
I have it working using like this:

...
ON TABLE PCHOLD FORMAT EXL2K TEMPLATE JatoHL_RENAVAM SHEETNUMBER 3
...
 



WebFOCUS 8.1.05 / APP Studio
November 30, 2015, 12:04 PM
Pondog
I've made the changes as suggested by Wep5622 and have progressed to the point I'm now receiving a different error:

1
0 NUMBER OF RECORDS IN TABLE= 10263 LINES= 10263
0 NUMBER OF RECORDS IN TABLE= 44492 LINES= 44492
(FOC3289) TEMPLATE FILE: Error opening file
(FOC3317) Error processing template file.
(FOC3298) ERROR FOUND IN A COMPOUND REPORT
Compound Report is TERMINATING.....

I did some research and figured I was missing a FILEDEF command for the first HOLD file. I've included it, yet still receive the error above. The changes I've made to the initial code are:

After the SET COMPOUND = OPEN command I added:
  FILEDEF temp_tmplt DISK temp_tmplt.xltm
-RUN 


For the fist HOLD file I now have:
ON TABLE HOLD  AS temp_tmplt FORMAT XLSX  TEMPLATE 'two_pt_tmplt.xltm' SHEETNUMBER 2 


And for the second/final HOLD file I have:
ON TABLE PCHOLD FORMAT XLSX TEMPLATE 'temp_tmplt' SHEETNUMBER 4 



Just to be sure I didn't mess anything up along the way I went back and ran each report separately and they each load the proper data into the proper template sheets.

Is there something obvious I'm overlooking in the syntax or the placement of the commands?


WebFOCUS 8.1.05
Windows, All Outputs
November 30, 2015, 01:15 PM
Ricardo Augusto
Is two_pt_tmplt.xltm in your approot( /ibi/apps/project) path?

If yes you don't need that FILDEF.

Can you try for both HOLDs:


HOLD1
ON TABLE PCHOLD FORMAT EXL2K TEMPLATE temp_tmplt SHEETNUMBER 2

HOLD2
ON TABLE PCHOLD FORMAT EXL2K TEMPLATE temp_tmplt SHEETNUMBER 4

PS:

The template temp_tmplt MUST BE on .MHT format.

I hope it helps.


WebFOCUS 8.1.05 / APP Studio
November 30, 2015, 04:47 PM
Pondog
Ricardo,
I tried making the changes you suggested but I receive the following error:
 "IBFSException 32027: probable server crash (unknown error 0)"  


This is accompanied with a page full of XML code that looks very ugly.

I tried running it with the FILEDEF and without. I made sure I had saved the template as "temp_tmplt.mht" as well.


WebFOCUS 8.1.05
Windows, All Outputs
December 01, 2015, 04:11 AM
Wep5622
@Pondog: You need neither the FILEDEF nor, I think, the COMPOUND declaration.

I think what's missing in your second TABLE request is the file extension to the previously created template:
ON TABLE PCHOLD FORMAT XLSX TEMPLATE 'temp_tmplt.xlsx' SHEETNUMBER 4


I've never used COMPOUNDs with Excel and don't really know what a compound excel-sheet would be like. Yet I've regularly used XLTX and XLTM Excel '07+ templates to create multi-sheet workbooks based on those templates. We've got some complicated stuff here with macro's and buttons and whatnot...


@Ricardo: Pondog is using XLSX (aka EXL07) format.
With EXL2K format, the actual file is indeed .MHT(ML) because the internals of XLS files are a well-guarded secret.
Not so for XLSX, where the native (and documented!) format is used.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
December 01, 2015, 09:18 AM
jgelona
Pondog, you don't need the COMPOUND declaration. I've never found a use for that with Excel compound documents. Also, your template file has to be in the path, we put ours in baseapp since baseapp is always in the path.

Since you are using XLSX and not EXL2K, your hold statements should look like this, assuming your template contains a macro.
ON TABLE HOLD AS TEMP1 FORMAT XLSX TEMPLATE 'template.xltm' SHEETNUMBER 2
....
ON TABLE PCHOLD FORMAT XLSX TEMPLATE 'temp1.xlsm' SHEETNUMBER 4


The first HOLD creates a file temp1.xlsm.

Then the PCHOLD basically adds to it.

The thing to be careful of in is the amount of output. The reporting can hangs when using multi-sheet templates with an embedded macro. IBI has confirmed this. We are currently on 8.0.9 running Suse Linux on an IBM ec12 mainframe. I have a report that has 4 sheets and depending on what the user selects can return up to 30,000 rows. As an EXL2K spreadsheet, returning everything, the report runs in 17 to 30 seconds. That is from the time the user presses the Enter key until the spreadsheet opens. In XLSX, the report never finishes and the reporting server hangs. This limit seems to be somewhere around 10K rows. When IBI ran it on another server platform, it took 20 minutes to run, but the EXL2K version still ran in under a minute. Excel files (.xlsx, .xlsm, .xltm) are basically zip files and I think the issue is how IBI Builds them when there are multiple sheets. This seems to happen only with multi-sheet templates with an embedded macro.

The problem is that I have to keep a copy of Excel 2003 on my workstation to maintain the .mht files used with EXL2K format. Excel 2010 supports .mht files, but it strips out macros when they are saved.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
December 01, 2015, 12:15 PM
Pondog
Wep5622, Jgelona, thanks for the information; especially regarding the row count. This all may be a moot point since I'm returning 50k+ rows.

I've removed the FILEDEF and COMPOUND commands and am able to get it to go to an Excel template, however, when it opens up the first sheet (sheetnumber 2) contains no data. The final sheet (sheetnumber 4) populates correctly and the pivot table looks good. I know I've read somewhere in researching Excel pivot tables about others having a problem getting the first sheet to load, but, now that I'm actually looking for that reference, it remains elusive.

I've also found that I have to have two copies of my template on the server. One named with the original template name "two_pt_tmplt" and the other named with the HOLD file name "temp_tmplt". If I don't have both of them out there then I get a template processing error (FOC3289 TEMPLATE FILE: Error opening file). I can't figure out why this is. I thought the HOLD file was internal and created from the original template file. Am I misunderstanding the process?

This message has been edited. Last edited by: Pondog,


WebFOCUS 8.1.05
Windows, All Outputs
December 01, 2015, 02:13 PM
j.gross
There is (or once was) a requirement that the target sheet(s) have content stored in at least one cell. Could that be your problem?
December 01, 2015, 02:24 PM
Pondog
Hey J. I've run into that before. I now make sure my templates have two rows of info just to be sure.


WebFOCUS 8.1.05
Windows, All Outputs
December 01, 2015, 05:33 PM
Pondog
I've found that if I comment out the second half of the report and just run the first half (changing HOLD to PCHOLD) then the first worksheet (sheetnumber 2) populates as it should.


WebFOCUS 8.1.05
Windows, All Outputs
December 02, 2015, 06:25 AM
Wep5622
I'm pretty sure that happens because you stored the in-between state of the sheet as a separate template. It probably contains no data on the first sheet, so that's what you get in the final result.

I'm not sure why you think you need that template stored somewhere though? What happens if you don't?

Only the base template ("two_pt_tmplt") needs to be in your path. The temporary "templates", that include the data from the earlier passes, can remain in temporary space (so, no APP HOLD or FOCCACHE needed).

Something else to be aware of, there is a length limit to the path to the template of, IIRC, 64 characters. The server won't be able to find anything with a longer name.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
December 02, 2015, 09:24 AM
jgelona
All I have ever done is put a blank in cell A1 of Sheet2 when creating a template.

Pondog, if you have 2 files named 'two_pt_tmplt.xltm', I'd get rid of one of both of them and replace them with your current one and put it in baseapp.

Next, you must code your HOLD statements like I showed above. Your sig says you are on Windows so the case of your file name shouldn't matter. Using your file name:

ON TABLE HOLD AS TEMP1 FORMAT XLSX TEMPLATE 'two_pt_tmplt.xltm' SHEETNUMBER 2
...
ON TABLE PCHOLD FORMAT XLSX TEMPLATE 'temp1.xlsm' SHEETNUMBER 4


The first HOLD uses your template. The PCHOLD uses the temp1.xlsm file that is the output from the HOLD. In the first HOLD AS, TEMP1 can be whatever you want, but what ever you use, the following HOLD or PCHOLD then name following TEMPLATE must be 'filename.xlsm'. I've got around a 100 reports that use this and it always works. I have one report that builds a variable number of sheets in one workbook depending on the user selection (from 1 to 14 worksheets), this works.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
December 02, 2015, 09:44 AM
Pondog
Hey Wep5622, I didn't think I needed the "in-between" template stored anywhere either, however, if I don't have it on the server along with the original template then I receive the following error:

 0 XLSM FILE SAVED ...
 1
 0 NUMBER OF RECORDS IN TABLE=    10879  LINES=  10879
 0 NUMBER OF RECORDS IN TABLE=    46074  LINES=  46074
 (FOC3289) TEMPLATE FILE: Error opening file
 (FOC3317) Error processing template file.


If I do have the "in-between" template stored along with the original template then the process runs, however, it doesn't populate the first sheet.



While I noted jgelona's comment regarding the row count, the template I'm running is macro-free.

This message has been edited. Last edited by: Pondog,


WebFOCUS 8.1.05
Windows, All Outputs
December 02, 2015, 04:49 PM
Pondog
jgelona, That's the ticket; using ".xlsm" when I call the template for the second sheet. I had been using "xltm" since this is a macro-enabled template. I didn't know the hold file was created/saved as an .xlsm file. I totally missed that in your earlier post or disregarded it since my template doesn't contain a macro.

Thanks to Wep5622, Ricardo Augusto, jgelona, and j. gross for providing the insight!! I learned a lot on this one.


WebFOCUS 8.1.05
Windows, All Outputs