How to export a report across multiple Excel sheets/tabs

[Former Member]

I am currently aware that, based on reading other threads and forums that in BOXI R3 (WebI), I am able to export a report, that is greater than 65,536 records, to more than one Excel sheet or tab. However, I am struggling on how to exactly do this? Is this a setting under my preferences? If anyone can walk me how to do this, it would be greatly appreciated. Thank you.

Keep in mind that we are using Excel 2003, and do not have Excel 2007, nor is this an option at this time for my company (otherwise, I'd be able to export up to 1MM+ records).

[Former Member · Accepted solution]

Hi jitapong,

From report itslef you can see Export to Excel(Save to my computer as --> Excel), select it and BO take care of rest.

As per my knowledge, BO will show them in 2003 only.

Thank You!

[Former Member]

Hi jitapong,

When a Web Intelligence document is exported to a Microsoft Excel Workbook, the export is handled by the Web Intelligence report engine. Excel is not required to be installed on the machine to execute the export. So, regardless of whether Excel 2003 , Excel 2007, or no Excel is installed, the export is performed using the parameters built into the report engine. This export is done in the Excel 97-2003 format.

A limitation of pre-2007 Excel is the inability to create a worksheet greater than 65,535 rows long. Excel 2007 overcomes that inability thus allowing users to create worksheets of much greater length.

Resolution:

Web Intelligence 3.1 versions overcome the 65,000 row limitation by exporting the document to multiple worksheets within the Excel Workbook. Each worksheet will be 65,535 rows long. For example, if a query returns 150,000 rows of data the resultant export to Microsoft Excel would produce a single workbook with 3 worksheets. 2 worksheets of 65,535 rows and 1 worksheet of 18,930 rows. This export is compatable with Excel 2003 and 2007.

Regards,

Chirag

[Former Member]

Hi,

In WebI to have one option Save to my computer-> ****.xls. Try it if it doesn't work out then copy each report tab and paste in a excel tab.

Cheers,

Suresh Aluri.

[Former Member]

jitapong,

To export a report, that is greater than 65,536 records you must artificially create distinct multiple tabs on your report that uses an indicator like row-count and a report filter to limit each tab to a series of rows. For example, you have 260,000 rows of output. You could create four tabs and on tab one use a report filter of 1 thru 60000, on tab two use a filter of 60001 thru 120000, etc, etc. When you go to export your entire report, four tabs will appear in your spreadsheet. Providing that you're using Excel 2007, you can create a fifth tab, and copy and paste the rows from each of the first thru four tabs and paste into the fifth tab and have all 260,000 rows in one tab (then delete tabs 1 thru 4 if you want).

thanks,

John