Let’s say we have more than 65556 rows in our report output. If we export it in excel, we will experience an export error that says “Exceeding 65556 rows limit”. We have a solution to overcome from this issue is to restrict the number of records on per page that will not exceed more than 65556 rows including header rows. We need to use this expression on your table’s parent group.
=Int((RowNumber(Nothing)-1)/65500).
For more details about to restrict the number of records on per page, take a look into Restrict Number of Records on Per Page.
It will solve the excel limit issue but opens another issue. The issue you will face is this change will impact all the other formats like PDF, CSV etc.
Below we will see how to implement Restrict Number of Records on Per Page for Excel only that will not impact other export formats.
Let’s follow following steps.
Step 1: Design your table as per your requirement without implementing any Restrict Number of Records on Per Page logic.
Step 2: Make a copy of the existing table and paste it on the report. Let’s say the name of this table is TableForExcelOnly and original table name is TableNotForExcel
Step 3: Select TableNotForExcel and open property window by pressing F4 key
Now set this visibility expression under Hidden property =IIF(Globals!RenderFormat.Name="EXCEL",TRUE,FALSE)
Step 4: Select TableForExcelOnly and open property window by pressing F4 key
Now set this visibility expression under Hidden property
=IIF(Globals!RenderFormat.Name="EXCEL",FALSE,TRUE)
And DataElementOutput property as NoOutPut
Step 5: Now implement the Restrict Number of Records on Per Page logic on the TableForExcelOnly as discussed in starting of this post.
Now you are done. Preview your report and check report output.
To execute this sample report, you need to only change the datasource of the report.
Let’s discuss few of the properties we have used above.
RenderFormat.Name
This is a new method in SSRS 2008R2 that returns the report rendered format.
DataElementOutput
This property Indicates whether the item appears in output rendered by the XML rendering extension. It has following possible values:
Output : Indicates the item appears in the output.
NoOutput : Indicates the item should not appear in the output.
Auto : If the item is a text box with a constant value, such as a label, the item does not appear (NoOutput). If the item is a rectangle, the output is the same as it is for ContentsOnly. For all other report items, the item appears in the output (Output).
You can see some important usage of the DataElementOutput property under
