Focal Point
Excel 2007 Compound Reports?

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

September 05, 2013, 02:29 PM
KellyT
Excel 2007 Compound Reports?
Hello,
We have a number of webfocus reports that are currently using EXL2K compound reports.

We are planning on upgrading to Microsoft 2013. The Microsoft 2013 will not oepn EXL2K spreadsheets.

So I would like to change the EXL2K to EXL07. However, the compound report will only show the first tab and not all the other tabs.

CODE:
on table PCHOLD Format EXL07 OPEN
on table PCHOLD Format EXL07 OPEN
on table PCHOLD Format EXL07 CLOSE

when I have this code and I try running in Developer studios I get an error message saying: "NO Query information to decode"
(I do NOT have microsoft 2013 installed yet - others in my department do and are getting the same error)

we are currently on version 7703. I just installed hotfix 7 but doesn't seem to be helping at all.

Thank you in advance,
Kelly


Prod: WebFOCUS 8.2.0.4
OS: Windows
Outputs: HTML, PDF, Excel, PPT
In Focus since 2005
September 06, 2013, 08:00 AM
Twanette
Hi Kelly,

From what I recall, COMPOUND reports were not supported with the EXL07 format in WF 7.7.
I will see if I can find where I read that.


WebFOCUS 8.2.06 mostly Windows Server
September 06, 2013, 08:06 AM
Twanette
Found it - at the very end of this document:
http://documentation.informati...f_wf_7703/TM4690.pdf

Format EXL07 in WebFOCUS Version 7 Release 7.02 and higher does not support the
following features currently supported for EXL2K:
Row Overflow (generating overflow worksheets when the sheet row limit is reached).
Bursting.
Formula.
Cell Locking.
Compound Reports.
Images.
WebFOCUS Graphs.
Pivot Tables.
.... and a few more.

Frowner


WebFOCUS 8.2.06 mostly Windows Server
September 06, 2013, 09:13 AM
jgelona
Twanette, I just read that document and not supporting templates with embedded macros is just plain shortsited. We have Excel 2010 and there is a template format with macros. It has an extension of .xltm instead of .xltx.

We are still on 7.6.11 but have plans to go to 8 later this year so all of our reports are EXL2K.

We only have about a hundred reports where we use templates with embedded macros. Currently I have to have a keep an install of EXCEL 2003 so I can maintain and create new .mht files. I was hoping to get rid of that when we went to WebFOCUS 8 and generate native EXCEL 2010 documents but is sounds like that is not the case.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
September 06, 2013, 11:42 AM
Wep5622
You can send separate reports to separate worksheets in EXL07 format. I recently did that, but it's a bit unintuitive...

Basically, you create an initial EXL07 worksheet based on your template and the first TABLE request and hold that as an EXL07 file. Next, you can use that file as a template for the next TABLE request, etc.

Like so:
TABLE FILE CAR
PRINT CAR BY COUNTRY
ON TABLE HOLD AS TPL01 FORMAT EXL07 TEMPLATE MYTEMPLATE SHEETNUMBER 1
END

TABLE FILE GGSALES
SUM DOLLARS BY COUNTRY
ON TABLE HOLD AS TPL02 FORMAT EXL07 TEMPLATE TPL01 SHEETNUMBER 2
END

TABLE FILE ...
...
ON TABLE PCHOLD FORMAT EXL07 TEMPLATE TPL02 SHEETNUMBER 3
END

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


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 :
September 06, 2013, 02:45 PM
KellyT
Thanks everyone.

Wep5622, I understand what you are saying but when you say Sheet 1 or Sheet 2. Is this the name of the tabs? i tried using Sheet 1 and an error message said "A word is not recognized: Sheet".

Thanks


Prod: WebFOCUS 8.2.0.4
OS: Windows
Outputs: HTML, PDF, Excel, PPT
In Focus since 2005
September 09, 2013, 04:15 AM
Wep5622
quote:
Originally posted by KellyT:
Wep5622, I understand what you are saying but when you say Sheet 1 or Sheet 2. Is this the name of the tabs? i tried using Sheet 1 and an error message said "A word is not recognized: Sheet".


Ah sorry, I was working from memory as I couldn't quickly remember in which report I used that (we have several hundred).

It should read SHEETNUMBER, not SHEET.


I also should have mentioned that it matters a lot where you use HOLD and where you use PCHOLD in this case.
All the preliminary Excel sheets that are being used as the next template in the chain should use HOLD, while only the final Excel sheet that you want to output should use PCHOLD.
That totally makes sense when you are aware of why you're doing, but it easily escaped at least my attention initially!

Then again, all I got from IBI was an example without much of an explanation about how it worked - you got more luck Wink


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 :
September 09, 2013, 03:20 PM
KellyT
Hi Wep5622,

Thanks for the help. But when I try the following code:

TABLE FILE CAR
PRINT
CAR.ORIGIN.COUNTRY
CAR.COMP.CAR
CAR.CARREC.MODEL
CAR.BODY.BODYTYPE
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS TPL01 FORMAT EXL07 TEMPLATE KELLYTESTING SHEETNUMBER 1
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
TITLETEXT='Trkld',
$
ENDSTYLE
END
TABLE FILE CAR
PRINT
CAR.ORIGIN.COUNTRY
CAR.COMP.CAR
CAR.CARREC.MODEL
CAR.BODY.BODYTYPE
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL07 TEMPLATE TPL01 SHEETNUMBER 2
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END

I get the following error message:
0 NUMBER OF RECORDS IN TABLE= 18 LINES= 18
(FOC3289) TEMPLATE FILE: Error opening file
(FOC3317) Failed to unzip EXL07 template file.
0 NUMBER OF RECORDS IN TABLE= 18 LINES= 18
(FOC3289) TEMPLATE FILE: Error opening file
(FOC3317) Failed to unzip EXL07 template file.


I also followed this:
https://techsupport.informatio...om/sps/81032542.html
and still got the same message.

Thanks again,
Kelly


Prod: WebFOCUS 8.2.0.4
OS: Windows
Outputs: HTML, PDF, Excel, PPT
In Focus since 2005
September 10, 2013, 05:18 AM
Wep5622
Ah, that rings familiar!

Perhaps it helps to SET EXCELSERVURL = '' ?

For some reason the default engine for the server is set to use the WF servlet, which for some reason doesn't work - I haven't received a satisfactory explanation for that yet.
Changing that setting to '' mysteriously changes the engine to be used to JSCOM3, which understands what needs to be done.

Boggles the mind, doesn't it?


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 :
September 11, 2013, 09:07 AM
jgelona
KellyT,

We are still on 7.6.11 so take this for what it is worth.

Your template KELLYTESTING has to be in the app path and make sure is has the correct extension. According to the documentation referenced by Twanette, it has to have a .xltx extension and cannot contain a macro. If it does have a macro, I would be interested in knowing if an extension .xltm works.

Second, we have always done a FILEDEF for TPL01 before the first table request. In 7.6.11 it looks like this:

FILEDEF TPL01 DISK tpl01.mht


Just replace .mht with the correct extension for EXL07.

Also if you are filling more than 2 sheets (we have some where we fill up to 20 sheets). You would need something like this:

FILEDEF TPL01 DISK tpl01.mht
FILEDEF TPL02 DISK tpl02.mht
FILEDEF TPL03 DISK tpl03.mht
...
FILEDEF TPLNN DISK tplNN.mht
...
ON TABLE HOLD AS TPL01 FORMAT EXL07 TEMPLATE KELLYTESTING SHEETNUMBER 1
ON TABLE HOLD AS TPL02 FORMAT EXL07 TEMPLATE TPL01 SHEETNUMBER 2
ON TABLE HOLD AS TPL03 FORMAT EXL07 TEMPLATE TPL02 SHEETNUMBER 3
...
ON TABLE PCHOLD FORMAT EXL07 TEMPLATE TPLNN SHEETNUMBER NN


When you create a new template, if the default is 3 sheets and you are going to fill 5 sheets, you have to add 2 blank sheets to the file. The template has to have at least the number of sheets you are going to fill. Also, you don't have to fill the sheets in order. You can fill sheet 3, then 1, then 2 if you need to.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
September 16, 2013, 12:40 PM
Ian Dalton
Hi Kelly,
We have had a similar problem and if you refer to Tech. Support Case No. 92022507 which I raised, basically you will need to upgrade your server to 7.7.05M. That will enable you to have Excel 2007 Compound Reports. We are on 7.7.03 and have tried lots of suggestions but all to no avail.
Regards,
Ian


_______________________
*** WebFOCUS 8.1.05M ***