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

Question

I have an SSRS report that cannot be exported to excel when the number of rows is greater than the Excel 2003 limit of 65536

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

I tried to add an extra level of grouping with page break in the expression

=ceiling(rownumber(nothing)/65536) 

However, this counts the detail lines, but does not account for the group footer. Thus, ripples are estimated to be 53,000, while the actual number of rows exceeded 65536.

Next expression

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

will give me the actual number of rows, including the group footers, but SSRS will not allow the group to express the current value.

How can I force a page break after 65536 lines to allow export to Excel? I was hoping to accomplish this in the report definition and not add the estimated page number to the request.

Any help is much appreciated

* UPDATE - Sample data *

 ItemDescription , Location , Quantity Red lorry , M25 , 5 Red lorry , M6 , 2 Yellow lorry , M1 , 3 

The report is grouped by ItemDescription with a common for this element, 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 that out of my 3 lines of data, I have 5 lines of report, including detail lines and a footer. SSRS can tell you how many rows of data are in my dataset, but I need to consider the footers for page breaks.

Answer 1

Hi, this link can help you. I had a similar problem a few years ago.

Tablix SSRS page break with Rownumber, only one row group and no default group expression

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

an answer was suggested

Answer 2

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

65,000 give you extra room for any header and footer. Then do a page break in this group “Between each instance of the group” and “Also at the end of the group” and you have successfully overcome the Excel file limit problem.

This is what we usually use without any problems. I don't think anyone will notice unless you specifically use all 65536 lines.