Home > SSRS > Solution For Issue:- Max 65,536 Rows Limitation While Exporting SSRS Report to Excel 2003

Solution For Issue:- Max 65,536 Rows Limitation While Exporting SSRS Report to Excel 2003

Excel 2003 has limitation of supporting only 65,536 Rows in the worksheet.

Suppose we have  SSRS report which has more than 65,536 Rows data and the system where we export SSRS report to Excel Format having Excel 2003 installed on the system, then  we got the error as shown in below post.

Let’s  do workaround to resolve the issue.

Select table where rows are greater than the above said limit i.e. 65,536.

Lets use AdventureWorks2008R2 database to develop SSRS report to check the above issue and its resolution.

SSRS report dataset query:-

SELECT

SalesOrderDetailID,OrderQty,ProductID,LineTotal FROM Sales.SalesOrderDetail

Report Preview

After Preview the report lets export it into excel by clicking as given below screenshot:-

Report Export

After Clinking on Export it will show as waiting as its rendering the report in EXCEL as shown in below screen shot:-

Report Export Waiting

After waiting, once its reached the limit of 65536 rows, it will throw an error as shown in bleo screen shot.

Report Export Error

In production server we may get huge data, so we should handel this error and allow user to export large dataset into excel.

Logically we should split the data into different excel worksheets before we getting 65536 limit.

Lets see how to resolve this issue.

First create parent group on detail group as shown in below screenshot:-

Add Group

In Group Expression , please write the expression Ceiling((RowNumber(Nothing)) /65000) as shown in below snapshot:-

Add Group Expression

After the Group created it added one column with group as first column in the table.

Delete the Column as shown in below screen:-

Delete Column

After click on Delete columns it will ask for different options as shown below, Select Delete columns only.

Delete Columns only

After sucessfully deleted the Column we have still group. Click on Group properties as shown in beow screen:-

Group Properties

In Group Properties , go to Sorting property and delete the expression and click ok.

In Group Properties , go to Page Breaks Property and click the check box as shown in below screen:-\

Group Page Break

We are done with the changes, lets preview report again and try to export the report into Excel.

Please follow below link in website http://msbitips.com

Solution For Issue:- Max 65,536 Rows Limitation While Exporting SSRS Report to Excel 2003

Thanks
Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : BI Tools

Advertisements
Categories: SSRS Tags:
  1. Prabir
    December 24, 2012 at 9:04 pm

    Brilliant dude,mind blowing job..You solved my Problems

  2. Anna
    February 5, 2015 at 11:55 am

    thanks for this 🙂

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: