SSRS Report - Implementing Page Break while exporting bulk data to Excel 2003

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

4.     How to resolve this excel records limitation issue by using Grouping and Page Break in SSRS?

               

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.