By: Ghanesh Prasad | Comments (6) | Related: > 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.
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.
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.
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.
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.
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.
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.
Right click on the new group "Group1" and click on Group Properties. You can refer to the below image.
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.
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.
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.
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.
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
- This sample report will not repeat the Tablix header on the second page, if you want to repeat the Tablix header then check out this article.
- Excel Export Rows Limitation is one of the issues in SSRS 2008R2. You can find the list of other Excel Export Limitations here.
- Check out other tips on SQL Server Reporting Services Parameters.
- Check out all of the SQL Server Business Intelligence Tips and Tricks.
- Check out all of my tips here.
About the author
Ghanesh Prasad leads a team in Microsoft Business Intelligence and has over 5 years of experience working with SQL Server.
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
