Disable Excel 2007 Multi-tab Generation

We are using the Excel 2007 output format for reports and we have a few large reports that generate more than 65,000 rows of data. In that situation, Cognos generates an Excel 2007 file with multiple tabs. I realize that this is for backwards compatibility of older versions of excel since 2007 can handle up to 1 million rows per tab. All our users are using 2007 and we would like to disable generating multiple tabs if more than 65000 rows are returned. Is there a way to disable this?

Hi Joe,

the 65000 is indeed a sheet limitation of excel 2003. but not a limitiation for csv. So if the layout format is not of any importance, you can try the export to csv format. I am not familiar with any option to force the export to a single sheet… maybe in a system.xml file ?

btw. which Cognos version are you using?

Thanks for the thought…I’ll read some more documentation. We are using Cognos 8.4.1.

There’s one option I can think of, but this option disables de Excel 2007 option entirely. Look in Administration guide under “Customization” there is a list of objects you can hide/disable for particular groups/roles…

The limitation of 65,000 rows exists because although Excel 2007 can support up to 2 million rows, not everybody has Excel 2007 installed so to provide compatibility with previous versions of Excel, Cognos have limited Excel output to 65,000 rows per sheet, hence if your output is > 65,000 rows you will get multiple sheets, and there is not much you can do about it.

Cognos is designed and works best with Summary output, if your output is >65,000 rows then you should consider applying filters, or summarily your data to restrict the output or use CSV

Dont know of this helped but I just solved it by inserting a large number under the List Properties > Data > Rows per page.
In Report Studio obviously :smiley:

Worked for me…