Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Handle Excel exceeds maximum 65,536 rows in SSRS 2008R2


By:   |   Read Comments (2)   |   Related Tips: > Reporting Services Formatting

Problem

You have created report in SSRS2008R2 and your report contains more than 65,536 records. When you export the report into Excel you get Export Error message “Exceeding 65,536 rows limit”. How can we resolve this issue?

Solution

SQL Server Reporting Services allows you to export data into multiple formats (i.e. Excel, PDF, XML, etc.) and you can find all the supported formats here. SSRS 2008R2 supports the .xls file extension for Excel, but these Excel files support a maximum of 65,536 records per sheet. When you try to export a SSRS report which has more than 65,536 records it will fail to export the report into Excel.

In this tip I will explain the problem with an example and later demonstrate the solution. This tip assumes that you have previous real world work experience building a simple SSRS Report. To demonstrate the solution, I will use the Adventureworks2008R2 sample database for SQL Server and SQL Server 2008R2 Reporting Service.

Step 1: Create New SSRS Report

Let's create a sample report for the demo purposes. Please change the report name to Sample_Report. You can refer to the below image.

Adding New Report

Step 2: Create Data Source and Dataset in SSRS

I have already created a data source connection to the Adventureworks2008R2 sample database, so let's create the below dataset for this sample report.

Main Dataset: This dataset will be used for the report body and it returns more than 65,536 records.

SELECT SalesOrderDetailID, OrderQty, ProductID, UnitPrice, LineTotal  
FROM Sales.SalesOrderDetail

After creating the Main dataset, the Report Data Pane looks like the below image.

Report Data Pane Window

Step 3: Add Tablix in Reporting Services

Let's add a Tablix for data viewing and exporting purpose. I will put all of the data fields from the Main Dataset into the Tablix. After adding the Tablix, the sample report looks like the below image.

Adding Tablix

If you will preview the report and export it to Excel you will get an Export Error window as shown in the below image. This Error message clearly states that a sheet can have a maximum of 65,536 rows, but you have requested 121,320 rows.

Report Preview With Excel Row Limit Issue

Step 4: Resolving Exceeding 65,536 Rows Issue in SSRS

We have a solution to overcome this problem which is to restrict the number of records per sheet. We will be able to successfully export the report into Excel if records do not exceed more than 65,536 rows including the header rows.

Please follow the below steps to resolve the issue.

Add a Parent Group to the existing top level row group; recently created Tablix doesn't have any row group so right click on Details (under Row Groups section), click on "Add Group" and then "Parent Group...". You can refer to the below image.

Adding Parent Row Group

Once you click on the Parent group in the previous step, it will open a Tablix Group. In the Group By expression, enter =Int((RowNumber(Nothing))/65520). Using this expression I am declaring a maximum of 65,520 Tablix records will be exported per sheet, the remaining 16 rows I have left for the report header. You can modify the formula depending on your report header needs. There is no need to check the Add group header and Add group footer checkboxes. You can refer to the below image.

Tablix New Parent Row Group Expression Window

As you can see from the below image, a new row group named "Group1" has been created and SSRS has automatically added a new column in the Tablix.

Report After adding Row Group

Right click on the new group "Group1" and click on Group Properties. You can refer to the below image.

Group Properties

Once you click on Group Properties, it will open a Group Properties window. Please give a proper name to this group, because SSRS by default gives a group name which is not descriptive. It is important to give a useful name so that it will make sense to the next person who is trying to understand the report definition. You can refer to the below image.

Group Properties - Group Name

Next, click on Page Breaks for the Group Properties and check "Between each instance of a group". Don't choose to break at the start or end of a group. You can refer to the below image.

Group Properties - Page Break

SSRS automatically creates sorting when a group is created and sorting is the same as the Group By expression. When the "Page_Break_Group" group was created, sorting was also created on it. In our case this sorting expression will cause an error because the Rownumber() function is not supported in the sorting expression, so we have to remove it. To remove the sorting, go to the Sorting section of Group Properties and delete the sorting. You can refer to the below image.

Group Properties - Remove Sorting

We don't need the first column in the Tablix, this column was created automatically when we created the "Page_Break_Group" group, so this can be deleted.

To delete the first column, right click on the first column and click "Delete Columns". We have to keep the group because it contains the Ceiling function, so choose the second radio button "Delete columns only" and click "OK". You can refer to the below image.

Delete Group Column

Step 5: Ready to Export the SSRS Report into Excel

We have done all necessary changes, now you can preview and export the report into Excel.

Next Steps


Last Update:






About the author
MSSQLTips author Ghanesh Prasad Ghanesh Prasad leads a team in Microsoft Business Intelligence and has over 5 years of experience working with SQL Server.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Friday, February 10, 2017 - 2:41:12 PM - bosteve Back To Top

Great article! Thanks for this tip.

 

 

I am using SSRS 2008 R2. It works fine when I run in ReportBuilder.

However, after uploading to the Report Server, It doesn't download, after about 1 minute, I get a login prompt (ive been logged out).

EventViewer report that ReportingServicesService.exe throws a COMException: "This network connection does not exist. (Exception from HRESULT: 0x800708CA)".

Have you run into this before? Any ideas on how to resolve it?

Thanks in advance. 

 

 

 


Sunday, October 23, 2016 - 10:00:17 PM - Harish Back To Top

 Hey thanks buddy this worked

 


Learn more about SQL Server tools