1. Background
The purpose of this article is how to implement Page Break and grouping while exporting bulk data to excel. It provides a solution to resolve the issue with excel 2003 limitations of 65,000 records. SQL Server Reporting services by default export the report into excel 2003 format (.xls). Excel 2003 has worksheet of size 65,536 rows by 256 columns. When we export a report which has more than 65,536 records into excel. It gives the following error:
To resolve this problem, here I am going to share some useful information and my experience regarding how to resolve the export issue with excel ‘s 65,536 records limitation while working in SSRS.
2. What is Excel 2003’s 65,536 records limitation?
Microsoft Excel 2003 has a limitation of 65,536 records in a sheet. As we know that SQL Server Reporting Service is one of the most popular tools for creating the reports. Many times, a developer has to create reports for bulk data. In this case, when we go to export the report, it gives error due to limitations of excel. Microsoft Excel is the most flexible format to see the report. It supports dynamic reports which have drilldown, drill through and interactive sort. We can see the report more easily in excel as compare to other formats. So for enterprise data level, it is necessary to resolve the issue to get advantages of Microsoft excel with SQL Server Reporting Services.
3. Step by Step procedure to create a report
Here I am going to create a simple tabular report with a table that has more than 65,536 records.
a. While creating dataset for report. Check the count of the table for which we are going to create report
The table has 121710 records.
b. Dataset for report
Select query that retrieve all the records from the table:
c. Layout design
I have created a simple report that has three columns – CalendarYear, SalesRegion and SalesAmount:
d. Preview the Report.
Here I run the report for all the records available in “GlobalSales_Fact” table: You can see the report has total 2767 pages. It has 1, 21, 710 records because we are extracting all the records from GlobalSales_Fact table.
e. Export the Report to Excel (Default format is xls)
f. While exporting, Reporting Service gives the error:
Excel Rendering Extension: Number of rows exceeds the maximum possible rows per sheet in this format: Row Requested: 121716, Max Rows: 65536
v Create a Row Group: Go to Row Group pane, Right click on the Details èAdd Group è Parent Group.
v Tablix Group window comes. Here we have to put GroupBy Expression for this new Group.
v Click on the expression button and put the expression to makes group of each 65,536 record sets.
Expression: =CInt(Ceiling(RowNumber(Nothing)/65000))
Click ok.
v Row Group has been created. We can see the Group1 in the Row Group Pane:
v In the report Layout, you can see Group1 is shown in the Report Layout:
v Right Click on the Group1 column èDelete Columns
v Delete Column window comes, Choose Delete column only and click on:
v Now you can see the Group1 column is deleted but Group1 is still available in the report that groups every 65,500 records:
v Go to Group1 property by right click on Group1 in Row grouping pane:
v In the Group property, go to Page Beakè Page Break Optionè Check the box for “Between each instance of a group” and “at the end of group”.
v Right click on the Tablixè go to Tablix property. Tablix property windows comes: Check “Add Page break after” and in column header, check “Repeat header columns on each page.”
v After implementing Page Break and Grouping, run the report and export it to excel. Now Report is exported successfully. You can see a Save window for saving the excel report:
v Save and Open the excel.
v In the Excel sheet, Data exported into sheet1 and sheet2. Sheet1 has 65,500 records as per the grouping we implemented and sheet2 has 56710 records.
Total records in the table = Total Records in sheet1 + Total records in sheet2
121710 = 65,500 + 56,710
v Output of Sheet1:
v Output of Sheet2:
5. Conclusion
By using Page Break & appropriate grouping, we can export the bulk data report from SSRS to excel 2003. This is very useful in reporting where we have to deal with bulk data e.g data warehouse.
----------------------------------------------------End of Article---------------------------------------------------
