Excel with large data

[dsds]

Has anyone rendered a report in excel with one million rows? I have it scheduled. Would it throw an error if i download it from the server.

[wyconian]

Hi

I've never tried downloading a report that big.  I would imagine the issue is going to be the limitaion of the maximum number of rows in excel (prior to office 2007 this was 65k(ish) rows, it's more in newer versions but probably no 1million).

Obviously that's a  massively large report, if it does export to excel it's going to be a massive excel file.  What does the report do?  Is having such a massive report going to be useful to your users?  You have to ask whether anyone is going to use such a big report. 

I'd suggest talking to your users to see if you can get a more focused requirement and generate a smaller report or different views of the report for different users.

Good Luck

[Lynn]

......BI AND ETL ARE TWO DIFFERENT THINGS......BI != ETL......BI AND ETL ARE TWO DIFFERENT THINGS......BI != ETL......BI AND ETL ARE TWO DIFFERENT THINGS......BI != ETL......BI AND ETL ARE TWO DIFFERENT THINGS......

I feel better now :)

[bdbits]

If you use Excel 2007 as the output format, it has a max of 1,048,576 rows. So that would be cutting it pretty close. I do not know if Cognos itself will have any problem with loading that many rows, and it's going to soak up a fair bit of RAM in the process. So it may work. But I am with wyconian - why are you doing this? Are your users even going to be able to do anything with a spreadsheet that large?

Lynn - you may feel better, but now thanks to your scrolling text, I feel worse.  :P

[Lynn]

yes, I admit the scrolling text is obnoxious :)

Another thing to be aware of is that even Excel 2007 rendered from Cognos will split data across worksheets at the 65K row limit. That means a million rows would yield a workbook with a lot of worksheet tabs. I believe the reason for this is to support those opening 2007 files using the compatibility pack.

Using CSV as an output format would allow you to open a result set of more than 65K rows in a single sheet at which point you could save as an Excel 2007 file.

[wyconian]

I quite like the scolling banner  :)

[RobsWalker68]

Me too, but better if it flashed and in red too  ;D

[Lynn]

Me too, but better if it flashed and in red too  ;D

Subtlety

is overrated

Alas, I don't think the old HTML <blink> tag is an option  :-X

[RobsWalker68]

hmm... that's a fail  ;)

[bdbits]

Oh, my eyes... it burns, it burns!

[Shivanand]

Thats true,
If the number of rows are more than 65k, exporting results in excel 2007 will split the report into several worksheets.
exporting it in .csv is a good option however formatting would be lost and if there are any headers with merged cells those rows would be lost.

in cognos 10, administrator can work on certain settings so that the reports are rendered in excel 2007 in one single sheet instead of splitting it in multiple sheets.

"In previous versions of IBM Cognos Business Intelligence, report output in Microsoft Excel format was limited to a maximum of 256 columns by 65,000 rows. Although this remains as the default worksheet size, administrators can now enable larger worksheets and change the maximum number of rows in a worksheet - up to a maximum of 16,384 columns by 1,048,576 rows - by using advanced server properties. This number matches the Microsoft Excel 2007 worksheet size limitations.

For more information, see the IBM Cognos Business Intelligence Administration and Security Guide."

Thanks
Shivanand