[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

Posts: 116 | Location: Birmingham, Al | Registered: July 23, 2015

Wep5622

Virtuoso

posted