Friday, March 2, 2012

SSRS - 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

               
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---------------------------------------------------

11 comments:

  1. Excelent Article! A great solution for companys that still use Excel 2003.

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. I had a problem applying this solution to a report that already had groups with totals. The group having the 65000th row was being split across two Excel tabs, with the group total calculated and displayed for each split.

    The solution I found and which seems to work ok, is the following:

    - Instead of checking the page break on the new CInt(...65000) group (Group1 in the example), I checked page break in the child group that was getting split. In that group's Page break/Disabled property (in the properties pane when clicking on the child group in the row groups pane), I put the following expression:

    =RowNumber("Group1") + CountRows("ChildGroup") < 65000

    so the child group has it's page break only when it contains the 65000th row.
    Though I have not tried it with more than (65000 * 2 = 130000) rows, it should also work in this case since Group1's row numbers are reset on each grouping.

    ReplyDelete
  4. To avoid splitting a child group, this expression is better:

    =RowNumber("Group1") <> 65000

    The other expression I gave yesterday could apply to several groups, which results in too many tabs in Excel.

    ReplyDelete
  5. Another important thing is to take account that the RowNumber fonction does not count group header and footer lines. It only seems to count lines of data.

    However, these lines, when added to the data lines, must be kept under the Excel limit, so take caution to use a figure lower than 65000 in these cases... especially if you have groups with two lines of data, plus a header and two total footer lines, for example.

    ReplyDelete
    Replies
    1. Thanks you so much Daniel for providing nice solution.

      Delete
  6. I followed the same , but I got error, "RowNumber cannot be used in sort expression"

    ReplyDelete
  7. solved it, I removed the sort properties, which automatically added in Group's sort property

    ReplyDelete
  8. How can we apply a similar grouping on Columns so after 256 columns the rest of the columns saved to sheet2 in the same excel.

    ReplyDelete
  9. Hello I have 1505339 rows in rdlc and i am trying to exporting error occur "Exception of type 'System.OutOfMemoryException' was thrown."

    ReplyDelete
  10. Hi Everyone,
    Below is my case. Help me to fix.
    I have a group which contains more than 10000 rows for some categories. I set initial toggle display for detail to hide. So that when user click + sign in group, it will show detail. Now the problem is that when group expands, it is trying to show detail in single page and browser crash. I want the drill down to show in multiple page. Any suggestions?

    Note: Keep together is not set. So, if toggle display and hide property for detail is not set it is showing in multiple pages which is not an issue. The issue is when I set toggle display based on group and initial render is only 7 rows with aggregated it is showing in single page. Hope I explained clearly.

    ReplyDelete