Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CASE-OPENED] EXL2K vs EXL07 performance
Go
New
Search
Notify
Tools
Reply
  
[CASE-OPENED] EXL2K vs EXL07 performance
 Login/Join
 
Gold member
posted
I have the following fex:

TABLE FILE APRSALU
PRINT
'APRSALU.APRSALU.APRSALU_SALU_CODE'
'APRSALU.APRSALU.APRSALU_SALUTATION'
BY 'APRSALU.APRSALU.APRSALU_PIDM'
WHERE APRSALU.APRSALU.APRSALU_SALU_CODE EQ 'CFML';
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END

This is a simple table dump to an Excel spreadsheet. Using EXL2K is runs in about 4 seconds. Using EXL07 it runs in about 2 minutes. Does anyone have any ideas?
My users and I thank you.
Paul

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


Prod: WF 7.7.05, BID, MRE, 7.7.06M Server, Windows 2008, RedHat, Oracle 11gR1, MS Office 2010
Test: I wish we had one!
 
Posts: 75 | Location: Wichita, KS | Registered: February 19, 2008Reply With QuoteReport This Post
Expert
posted Hide Post
I would change PCHOLD to HOLD and rerun in both formats, just to confirm it's not some problem with the web browser having issues transferring the file...


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Gold member
posted Hide Post
Thanks Francis. Did as you suggested and changed the line to:
ON TABLE HOLD FORMAT EXLxx
where xx is either 2K or 07. Results are the same. EXL2K ran in about 4 seconds and EXL07 ran in over 2 minutes.


Prod: WF 7.7.05, BID, MRE, 7.7.06M Server, Windows 2008, RedHat, Oracle 11gR1, MS Office 2010
Test: I wish we had one!
 
Posts: 75 | Location: Wichita, KS | Registered: February 19, 2008Reply With QuoteReport This Post
Virtuoso
posted Hide Post
you have both Excel2 and excel7 installed on your computer?




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Reply With QuoteReport This Post
Gold member
posted Hide Post
No Frank, just Excel 2007. I am replicating what the user had on my computer which has Windows7, IE8, Microsoft Office 2007. There machine is comperable, just less ram.


Prod: WF 7.7.05, BID, MRE, 7.7.06M Server, Windows 2008, RedHat, Oracle 11gR1, MS Office 2010
Test: I wish we had one!
 
Posts: 75 | Location: Wichita, KS | Registered: February 19, 2008Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Hi Paul,

i have similar Problems with Excel 2007 as an Output Format. Some characters (Rumanian) that EXL2K can handle by displaying them
as a rectangle, causes Output crashes in EXL07 Output. we opened a case and they told me to change our environment to unicode.
As we are also using PMF, this not possible. PMF is not ready for unicode. So i have to tell my customers that we have to use Excel 2000
in 2011!!!

Christian


WF Production Version: 7.7.02M
WF Test Version: 7.7.02M
Developer Studio: 7.7.02
HTML, EXL2K, FLEX, PDF,PPT
 
Posts: 156 | Location: Essen Germany | Registered: December 02, 2010Reply With QuoteReport This Post
Expert
posted Hide Post
I scanned the v7.7 documentation and there isn't much on format EXL07. This comment in the New Features document, "Additional details can be found in Technical Memo 4690: Creating Report Output in Excel 2007 Format" doesn't help much because I cannot find it on the Information Builders website.

I finally found the document: TM4690 - Saving Report Output in Excel 2007 Format.

In the section, "Building the .xlsx Workbook File", it states:

quote:

Microsoft changed the format and structure of the Excel workbook in Excel 2007. The new .xlsx file is a binary compilation of a group of xml files. Generating this new file format using WebFOCUS is a two step process that consists of generating the xml files containing the report output and zipping the xml documents into the binary .xlsx format. The Reporting Server performs the xml generation process. The zipping process can be completed either
by the client (WebFOCUS Servlet) or the server (JSCOM3):

- WebFOCUS Servlet: The WebFOCUS Client within the application server performs the zipping process. This can be done within the local client or through a remotely accessed client. In Version 7 Release 7.02, the servlet method is the default approach defined for each WebFOCUS client, with the client by default pointing to itself.

- JSCOM3: The java layer of the Reporting Server performs the zipping operation. This option can be used within secured environments because it does not require URL access to a remote client.

The bottleneck might be here.


This document is quite detailed about the features of EXL07, I don't have a clue why this information is not included in the Creating Reports manual.

Oh, and by the way, "Format EXL07 in WebFOCUS Version 7 Release 7.02 does not support the following features currently supported for EXL2K:"

- Table of Contents (BYTOC).
- Row Overflow (generating overflow worksheets when the sheet row limit is reached).
- Bursting.
- Formula.
- Cell Locking.
- Compound.
- Multiple sheets.
- Multiple tables on a single sheet.
- Images.
- WebFOCUS Graphs.
- Pivot Tables.
- Templates with macros.
- Cell sizing with SQUEEZE and WRAP.
- ToolTips for Drilldown Hyperlinks.
- Preserving leading and internal blanks.

Half a step forward, five steps back. I would stick with EXL2K as it sometimes takes quite a while for a new bell to ring sweetly.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Gold member
posted Hide Post
Wow Francis. That is quite a list of unsupported features. We use several of these. Your suggestion is most beneficial. Go back to EXL2K. I also will spend some time going over the document your so kindly found.
Thanks all for your help.
Paul

Another interesting piece of information came from the Admin Console when I looked up what our system was set to:

IBIF_EXCELSERVURL cannot be used when Web server security is enabled. This includes Basic authentication, IWA, SSL, and third party Security products (such as SiteMinder,Clear Trust, Oblix, Web Seal). In these cases, the Web server security settings can prevent WebFOCUS from creating the EXCEL 2007 file. IBIF_EXCELSERVURL can be used with Basic authentication when the credentials are supplied as follows:

 http://userid:password@host:po...lias/IBIEXCELSERVURL 


However, this is not recommended since the password is displayed as plain text in the WebFOCUS Administration Console and the Web server logs.

Thanks again Francis.

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


Prod: WF 7.7.05, BID, MRE, 7.7.06M Server, Windows 2008, RedHat, Oracle 11gR1, MS Office 2010
Test: I wish we had one!
 
Posts: 75 | Location: Wichita, KS | Registered: February 19, 2008Reply With QuoteReport This Post
Gold member
posted Hide Post
I am considering an upgrade to 7.7.03. Do you know if the same limitations for EXL07 are still true?
Thanks,
Bob


WebFOCUS 7.7.05M, gen 144, Windows 2008 Server R2 64-bit, Tomcat 6.0.33, IIS 7.0, SQL Server, Excel 2013, PDF, HTML, FOCUS files.
 
Posts: 88 | Location: Seattle | Registered: March 29, 2007Reply With QuoteReport This Post
Guru
posted Hide Post
Bob,
I would stick with EXL2K. We just started using EXL07 and some reports work fine and others die a horrible death in EXL07 output only(HTML and PDF work fine.) So, I'm thinking there is something in my report that is not compatible with EXL07, I just can't seem to figure out what it is.
Good luck!


WebFOCUS Server 8.1.05
Windows 2008 Server
WebFOCUS AppStudio 8.1.05
Windows 7 Professional
IE 11 and Chrome Version 43.0.2357.124 m.
Mostly HTML, PDF, Excel, and AHTML
 
Posts: 272 | Location: Kalamazoo, Michigan | Registered: September 30, 2010Reply With QuoteReport This Post
Gold member
posted Hide Post
Thank you. I think we came to that conclusion too although it was disappointing.


WebFOCUS 7.7.05M, gen 144, Windows 2008 Server R2 64-bit, Tomcat 6.0.33, IIS 7.0, SQL Server, Excel 2013, PDF, HTML, FOCUS files.
 
Posts: 88 | Location: Seattle | Registered: March 29, 2007Reply With QuoteReport This Post
Platinum Member
posted Hide Post
I know this is an old thread, but I've been having this same problem for forever, and refused to accept it.

Just today I think I've found a fix, and if it really bears out, I can't believe it's this simple. In that technical memo above, it mentions how to switch the method for zipping the XLSX file, and you can either have the servlet do it, or JSCOM3 do it. When I set to JSCOM3 instead, it ran quickly and resulted in the small file I was hoping for.

You can check the value of IBIF_excelservurl in the Admin Console with Configuration/Client Settings/General. By default it's set to point to itself, but if you blank it out, it will use JSCOM3 instead.



Prod: 8.1.0.4, Dev/App Studio: 8.1.0.4 Dev:App Studio, Dev Studio 8.1.0.4 OS:Windows 7 Output:AHTML, Excel 2007

The life of a designer is a life of fight against the ugliness.
 
Posts: 138 | Location: North Carolina | Registered: August 10, 2012Reply With QuoteReport This Post
Gold member
posted Hide Post
quote:
J.Hines


J.Hines, thank you a lot for your publiched workaraund. I am on 809 but my EXL07 generation for big file was a real trouble and your pointed solution helped a lot!


Wf7704/WF8,Win64/32
 
Posts: 68 | Registered: February 20, 2004Reply With QuoteReport This Post
Master
posted Hide Post
I'm on 8.0.08 and it is a real problem for me as well. With EXL07 and JSCOM3, doing a workbook (4 worksheets) takes 20-30 minutes to complete and runs in about 10 seconds with EXL2K. This report locks up the Reporting Server and it is not that big of a report. I have to kill the JSCOM3 process to free things up. IBI has a real issue here. We've opened a problem report. This is the error we get:

(FOC1522) ntjprerr: Java heap space. CPJAVA: Error in processing EXECUTE command for Class ibi.jsexcel.JscomExcelUnzip

I've tried setting it to 2GB and it makes no difference (my tech rep says the default is 1GB but I couldn't find that documented anywhere). I've sent IBI the program and data files so they should be able to recreate it. Actually, I need to touch base with them today.

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


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 948 | Location: Oklahoma City | Registered: October 27, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
After all these years, EXL07 is still not ready for prime time.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
the problem my users have is that exl2k does not open in IE9+. It can only be saved and then reopened. [now i'm in 768 , so that may be a factor.]
Does anyone else have that problem?
....
from my new 7706, exl07 opens just fine, but i haven't turned security on as yet, so (from what i read above), i may have interesting times ahead. I'll try J.Hines idea. This is a very good thread.




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Reply With QuoteReport This Post
Expert
posted Hide Post
oh man it gets worse
in 7706, if i leave the format as EXL2K
i get this message
"The file you are trying to open, 'WFServlet.xls' is in a different format than[sic!] specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?"

jeeeez.....

ps: i'm opening in excel 2010

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




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Hi Susannah,
As far as I know, this is "normal" because Excel2010 detect that you are opening a file with a format from a previous version which is okay.

Boring to answer to this question but seems to be MS...


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2282 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Expert
posted Hide Post
Merci, Martin....
update: some of my users with E10 get this issue, some don't. sigh.




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Reply With QuoteReport This Post
Virtuoso
posted Hide Post
susannah, we "fixed" the issue by adjusting each user's Windows REGISTRY as suggested by Microsoft here: http://support.microsoft.com/kb/948615

We relied on our HelpDesk department to push the update through the network and the annoying message went away.



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Reply With QuoteReport This Post
Gold member
posted Hide Post
quote:
IBIF_excelservurl

Please suggest Where can i set this value in WF 7.6.11.


WF 8.1.04,Infoassist,Oracle, Excel, PDF,HTML.
 
Posts: 82 | Registered: January 06, 2014Reply With QuoteReport This Post
Master
posted Hide Post
quote:
Originally posted by susannah:
"The file you are trying to open, 'WFServlet.xls' is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?"


Susannah,

The reason you get this is that WFServlet.xls is not a true .xls file and Excel 2007 and higher detect this. This is one of Microsoft's security features. The registry fix suggested by njsden works. We did the same thing. The registry update was pushed out agency wide.

Also, many of our users are not what I would call technically sophisticated. They did not like worksheets opening in the browser so we made other agency wide changes so that worksheets would open in Excel, not IE.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 948 | Location: Oklahoma City | Registered: October 27, 2006Reply With QuoteReport This Post
Master
posted Hide Post
Solution: Stop using IE and Excel ! I don't use either with EXL07 output.

I use Chrome as the default browser and either Lotus Notes Symphony or OpenOffice Calc as the spreadsheet.

It does download, of course, but with Chrome it isn't an annoying yellow bar that pops up behind the topmost window half the time. The download is right there in the bottom left corner and a single click opens it up in the spreadsheet. Sweet.


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Reply With QuoteReport This Post
Master
posted Hide Post
quote:
Originally posted by George Patton:
Solution: Stop using IE and Excel ! I don't use either with EXL07 output.


For some of us, this is not an option. We can't even install another browser on our PCs. We are a Windows and MS shop. "They" are in the process of installing new PCs Agency wide with Win7 and IE10. Mine is still XP Professional and IE8.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 948 | Location: Oklahoma City | Registered: October 27, 2006Reply With QuoteReport This Post
Master
posted Hide Post
Sigh ... I wish my users could appreciate how lucky they are. They bi tch all the time about the tools we provide and always want the shiniest toy they see, or the latest consumer-oriented, insecure, cloud-based application. They all have high-end laptops with Win 7 and nothing disabled but want tablets as well. They are provided with Blackberry Z10s but hate them because they aren't cool any more.. If only they knew how restrictive it is with big companies or the government ....


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CASE-OPENED] EXL2K vs EXL07 performance

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.