Exporting RDLC Report In Excel When Record Count Is Above 65536 In Report Viewer 10.0.0.0

Migrate the report viewer to latest versions like 11.0

Report viewer version 11.0 uses the latest version of MS Excel (i.e. XLSX format). By default, this type of an extension supports 1,048,576 records per sheet. With this large data support, the users don’t get the maximum rows conversion issue while exporting into an Excel sheet.

We have a few challenges for implementing this migration to the latest version. This can be the rework of the all the reports and report pages. This can be achievable with the small projects or fewer number of reports but for big projects, this solution is costly and requires extra man hours. In order to avoid this, the developers can choose the second option, as shown below.

Setting Page breaks for the RDLC

To overcome the limitation error, RDLC provides an option to break the pages into multiple sheets.

Example

Create a sample RDLC report with Report Viewer 10.0, using Adventure Works database, which is displaying above 66000 records.



By applying the steps, mentioned below in RDLC report, the large data can be easily exportable into multiple sheets.

Step 1

Open RDLC and select the data row of the table. Right-click on it, select Add Group and then select Parent Group.


Step 2

In this step, you should get a Tablix group Window. Enter the function, mentioned below in the Group by: Text box and click OK.

=Int((RowNumber(Nothing))/65520)


Step 3

In the next step, at the bottom of the RDLC report, you will find “Row Groups” pane. Here, right-click on the group name and select Group Properties.



You will get a Group Properties Window. We can give a proper group name here.



Check the “Between each instance of a group” option in Page Breaks from the left pane.



Next, in the Sorting section, delete the sort by function.



If you observe the report after performing the steps mentioned above, it adds Group1 column, as shown below. Select add delete the Column.



Now, save & run the report and export to Excel format. If everything works, the data should display in the multiple sheets.



If we observe in the sheet 2, the report headers won’t display. In order to display the headers, add the code, mentioned below.

Open the RDLC report in XML editor. Add the nodes, mentioned below at the first TablixMember node of TablixMembers node.

<KeepWithGroup>After</KeepWithGroup> <RepeatOnNewPage>true</RepeatOnNewPage>



Save, rerun the report and export to Excel. You can observe the data in multiple sheets with the headers in all the sheets.