Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

New TIBCO Community Coming Soon
In early summer, TIBCO plans to launch a new community—with a new user experience, enhanced search, and expanded capabilities for member engagement with answers and discussions! In advance of that, the current myibi community will be retired on April 30. We will continue to provide updates here on both the retirement of myibi and the new community launch.

What You Need to Know about Our New Community
We value the wealth of knowledge and engagement shared by community members and hope the new community will continue cultivating networking, knowledge sharing, and discussion.

During the transition period, from April 20th until the new community is launched this summer, myibi users should access the TIBCO WebFOCUS page to engage.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Excel 2007 Compound Reports?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Excel 2007 Compound Reports?
 Login/Join
 
Platinum Member
posted
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
 
Posts: 121 | Registered: November 17, 2005Report This Post
Platinum Member
posted Hide Post
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
 
Posts: 195 | Location: Johannesburg, South Africa | Registered: September 13, 2008Report This Post
Platinum Member
posted Hide Post
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
 
Posts: 195 | Location: Johannesburg, South Africa | Registered: September 13, 2008Report This Post
Master
posted Hide Post
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.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
Virtuoso
posted Hide Post
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 :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Platinum Member
posted Hide Post
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
 
Posts: 121 | Registered: November 17, 2005Report This Post
Virtuoso
posted Hide Post
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 :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Platinum Member
posted Hide Post
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
 
Posts: 121 | Registered: November 17, 2005Report This Post
Virtuoso
posted Hide Post
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 :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Master
posted Hide Post
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.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
Platinum Member
posted Hide Post
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 ***
 
Posts: 196 | Location: London, UK | Registered: December 06, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Excel 2007 Compound Reports?

Copyright © 1996-2020 Information Builders