How to add SSRS page break after 65536 rows, counting group header/footer

6
votes

I have an SSRS report which is failing to export to excel when the row count is greater than the Excel 2003 limit of 65536

The report already has a grouping level with a group footer.

I have tried to add an extra grouping level with a page break on the expression

=ceiling(rownumber(nothing)/65536)

However, this counts the detail rows, but does not take into account the group footer. So the rownumber evaluates to 53000 while the actual number of rows has exceeded 65536.

The following expression 

=ceiling(RunningValue(Fields!myfirstgroup.Value, CountDistinct, Nothing) + rownumber(nothing) / 65536 )

will give me the actual row count including the group footers, but SSRS will not allow a group on a running value expression.

How can I force a page break after 65536 rows to allow an export to Excel? I had hoped to accomplish this in the report definition, and avoid adding a calculated page number in the query .

Any help much appreciated

* UPDATE - Sample data *

ItemDescription , Location , Quantity

Red lorry , M25 , 5

Red lorry , M6 , 2

Yellow lorry , M1 , 3

Report has a grouping on ItemDescription with a total for that item, so it will show

ItemDescription , Location , Quantity

    Red lorry , M25 , 5

    Red lorry , M6 , 2

      Total for Red Lorry,7

    Yellow lorry , M1 , 3

      Total for Yellow Lorry,3

This means from my 3 rows of data, I have 5 report rows including detail and footer rows. SSRS can tell how many details rows are in my dataset, but I need to take the footers into account for a page break.

2
sql-server-2008reporting-servicesreportingservices-2005ssrs-grouping
I think you can use shared variable to count number of rows and put it in group footer. Add an expression to give page break on the shared variable. – Pratik Kaje
Can you tell us a bit more about why you want to create a report with more than 65,000 rows that gets exported to Excel AND contains grouping with group footers? Is someone going to be looking at the totals in these footers? Could you export the rows to CSV instead? – Nathan Griffiths
It is a customer requirement to have this amount of data, in Excel, with grouping rows, so that is my aim. Rather than question the wisdom of this request, or produce an alternative solution, I am more interested in finding a solution to the technical problem – JamieA
Is the number of rows in the group headers/footers fixed? How are these calculated? Can you give a few rows of sample data, just half a dozen to show how the footer relates to the detail rows? – Ian Preston

2 Answers

4
votes

Hi this link might help you. I had similar sort of issue, a few years back.

SSRS Page break on Tablix with Rownumber ,just one row group and no group expression given by default

=Floor((RowNumber(Nothing)-1)/2000)

was the suggested answer

2
votes

Create a group with the following expression: =CInt(Ceiling(RowNumber(nothing)/65000))

The 65000 give you a little extra room for any headers or footers. Next, do a Page Break on this group "Between each instance of a group" and "Also at the end of a group" and you will successfully beat the excel file limit issue.

This is what we normally use without any issue. I don't think any one will notice if you don't specifically use all 65,536 rows.