If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Exporting Report to Excel Help

Started by KANUL, 18 Nov 2008 10:52:14 PM

Previous topic - Next topic

KANUL

Hi Every one
When i try to export my report to excel it takes 16-20 min for 2000 records. Can any one guide my for settings to be made so that performance can improve.

Thank

sivabisani

Hi,

To improve the performance of a report we can do the following things.
In report level:
1)you can reduce the calculation and conditions in the report
2) you can limit the cut copy paste operations for user's
3) you can limit access no of report's at peek time you can improve performance at report level
4)you can produce the report execution method in to the concurent it may usefull at sometime to improve  the performance of a report.
In model:
1)you can remove loop's at that time you can improve performance at model level
2)use indexes you can improve performance

And regarding the u r current question u may found this type of issues with excel.
Excel issues
The following issues were identified in Microsoft Excel and affect producing Cognos 8 reports in Excel format.
Unable to Load Images from the Cognos 8 Content Store in a Report
If a report contains an image whose URL points to the Cognos 8 content store, Microsoft Excel generates an access violation error and shuts down.
This problem is a known issue in the Microsoft knowledge base, and Microsoft is currently investigating the problem. This problem occurs only in Excel 2000 and 2002.
   
A Blank Worksheet is Opened
If Microsoft Excel cannot download a worksheet within a timeout period, Excel may instead open a blank worksheet.
A Warning Message Appears When Excel Opens a Cognos 8 Report
Each time Microsoft Excel opens a Cognos 8 report, the following message appears:
Some of the files in this Web page aren’t in the expected location. Do you want to download them anyway? If you’re sure the Web page is from a trusted source, click Yes.
The Excel workbook in HTML/XML format requires the presence of the file filelist.xml. Cognos 8 does not allow the creation of local files on the client side. In addition, a local file that contains URLs introduces a security issue. Consequently, this message will appear whenever you open a Cognos 8 report in Excel.
Using Reports Saved in XLS Format
If you open a report that was saved in XLS format or run a report in XLS format, and security settings in your browser are set so that you are prompted to open or save the report, do not click Save. If you save the report, the spreadsheet content will not be saved. This is because Excel reports in Office 2000 HTML format use relative paths to the spreadsheets. The relative URL paths are no longer available when you open a saved XLS report.
Instead, click Open first and then choose to save the report.
Loading Excel Reports in Netscape 7.01 Is Not Supported
This version of Cognos 8 does not support loading Microsoft Excel reports in Netscape 7.01.
Nested Labels in Charts Are Not Supported
Currently, it is not possible to specify nested labels for the category axis via XML.
Data Series Are Truncated
Microsoft Excel may group data series or categories differently when compared to a chart produced by Cognos 8.
A 1 KB buffer limit in Excel limits the maximum number of data series per chart to 120. Data series over 120 are truncated.
Charts and Custom Colors
If a Cognos 8 report contains a chart that uses custom colors, Excel may not be able to add the custom color to the Excel color palette. Excel will attempt to match the custom color to one of its available standard colors. As a result, colors in Excel may vary from those seen in HTML or PDF.
We recommend that you use standard colors in charts.
Repeating Pie Charts
If you have a report that has repeating pie charts, and you define a chart title, Excel will show each pie with a title that is a concatenation of the chart title and the data series. For example, if the chart title is Quantity Sold by Order Method and Product Line and the data series is Order method, the title of each pie in Excel will be
Quantity Sold by Order Method and Product Line, order method
Discrete Axis Label Skip Control in Charts
In Cognos 8 charts, you can control the skipping of discrete axis labels. This feature is not supported in Excel charts.
Formatting Limitations
About 70% of the formatting functions available in Cognos 8 are supported in Microsoft Excel. The following table shows which formatting functions are supported in Excel and which are not.
Microsoft Excel does not support the overline text format.
Text Strings with More Than 255 Characters
Cells in Microsoft Excel have a limit of 255 characters. If your report contains text strings that are longer than 255 characters, they will be formatted as text and appear as ######.
Reports with More Than 256 Columns
Microsoft Excel limits the size of a worksheet size to 65536 rows by 256 columns. If your report contains more than 65536 rows, it is split into multiple worksheets. The number of worksheets that your report can contain is limited by the physical memory of your computer. If your report contains more than 256 columns, the following error occurs:
Reports with more than 256 columns cannot be rendered in Excel.
Table and Column Width
Microsoft Excel does not support using percentages to determine the width of tables. If the report contains only one table, the value of the width attribute for the Table element in the report specification determines the width of the table in the Excel worksheet. If the report contains more than one table, Excel determines the width of all the tables in the worksheet. If the tables are nested, the width specified for the outer table is used and, if necessary, the width is adjusted to accommodate data in the nested tables. The columns and rows around the table are merged to preserve the appearance of the nested table. When the workbook is saved, only a single table is saved per worksheet.
Excel Formats and Secure Socket Layer (SSL)
SSL is supported for the following formats and Microsoft Excel versions.
Format    Version
Excel 2000 single sheet    Microsoft Excel 2000, 2002, XP, 2003
Excel 2000    Microsoft Excel 2003
Excel 2002    Microsoft Excel 2002, XP, 2003
Number Format Becomes Currency Format in Japanese Excel
A report uses the Number data format and you save it as Microsoft Excel output. When you open the report in the Japanese version of Microsoft Excel, the data format is listed as Currency rather than Number. This occurs because Japanese Excel interprets the standard Number data format slightly differently than other versions of Excel.
The value appears correctly in Number format. For example, if you specified five digits as your number format, five digits still appear. In Excel, click the Custom number format to see the exact format string being used.
Report Shows Data in Wrong Columns
A report contains a large amount of data that is presented using a large number of nested report objects, such as tables and blocks. When the report is produced in Excel format, some of the data appears in the wrong columns. This occurs because Excel has a 64K limit on how many nested cell objects can appear in a single spreadsheet.
Avoid exceeding the Excel limit by redesigning the report to present the data using non-nested structures.

Let me Know if u want  more points or information

Thanks
siva



harish.malik

Brilliant!!

What an informative post.

Thanks Siva for sharing such a lot of useful information.

Regards,
~Harish

KANUL


Ashish_571

I am facing a problem in running a report in excel format in Cognos. When run with small data , it runs fine. But when the data it large, it pops up the prompt to save or open the report and when I click on either of them the tab closes and nothing happens. Please suggest what could be the issue and how it could be resolved