e.Spreadsheet, Excel & the 64,000 Row (and 256 Column) Question

If I had a dollar for every time someone asked how e.Spreadsheet handles Microsoft Excel's 64K row limitation, I would be filthy rich. Here's the scoop:

Excel 2003 files (and earlier) can only contain 64K (65,536, to be precise) rows per worksheet, and only 256 columns per workbook.

e.Spreadsheet actually doesn't have these limitations - it can produce workbooks that contain much more than 64K rows and 256 columns per worksheet. Furthermore, millions of rows of query data can participate in the creation of a report, even if the resultant report contains far fewer rows of summary, aggregate information.

BUT...when you open an e.Spreadsheet-produced XLS file within Microsoft Excel (or web-browser Excel plugin), Excel throws out any rows over 64K and columns over 256. Not good, not what the report developer intended, and there is nothing we (Actuate) can do about it.

There are alternatives, though:

1) Realize that the 64K row limit applies to the report OUTPUT, not to the number of database rows that can participate in the creation of the report. For example, a report might summarize, in 50,000 report lines, over 3 million rows of data. The report lines would each summarize/aggregate a portion of the data.

This is often a point of confusion because virtually all competitive Excel report plug-ins (including Excel itself) require that all database data be dumped into the Excel workbook before it can be summarized. Not so with e.Spreadsheet. It can aggregate and summarize data before writing out the Excel workbook.

2) The end-user can run and view the report within the e.Spreadsheet Designer, to view the extra rows and columns that e.Spreadsheet produces. In this way, Excel is never involved.

3) Use the e.Spreadsheet Engine to create a Java applet for viewing over-sized e.Spreadsheet files over the web. This is an option if you don't want end-users to need the e.Spreadsheet Designer installed on their desktops. Again, Excel is never involved.

4) Use report parameters (both run-time and view-time), hyperlinks to other reports, and SmartSheets to design reports that display only the data that each end-user individually wants. It is a very, very, very rare individual who truly wants to see over 64K rows and 256 columns of data per worksheet. 99.9% of the time, the end-user wants to view a summary of all that data with the ability to "drill-down" to detail by triggering a parameterized sub-report.

5) Design the workbook so that it bursts data onto multiple worksheets if the amount of data on any one worksheet exceeds 64K rows or 256 columns. This is easily accomplished with a few Report Functions (formerly known as MatrixScript commands) placed on the template worksheet.

For example, in version 9, if you put the following Report Functions on the worksheet, it will split the report into multiple worksheets that each have no more than 100 rows.

group(trunc(rownum()/100))