6

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.

JamieA
  • 1,984
  • 4
  • 27
  • 38
  • 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 Sep 25 '13 at 16:28
  • 3
    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 Sep 25 '13 at 21:08
  • 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 Sep 26 '13 at 10:42
  • 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 Jul 29 '14 at 13:12

2 Answers2

4

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

Community
  • 1
  • 1
Rohith Nair
  • 1,080
  • 1
  • 17
  • 33
  • Thanks. This will prevent the error, but it is a little unsophisticated, and could lead to 2 or 3 times as many excel worksheets as are actually needed. – JamieA Aug 04 '14 at 16:37
  • Not the elegant after I was after, but prevents the Excel exporting error, so +50. For the record, the only elegant way I found to this was to calculate my page break in the SQL, and add a report grouping on it. Surprised that SSRS does not have a way of dealing with this – JamieA Aug 05 '14 at 12:30
  • sorry @JamieA i couldn't help you with that in detail. For a simple dataset SSRS always works perfectly, but when the report becomes complex, all these little things will annoy us and takes our time to sort out (10 months with SSRS project) :( – Rohith Nair Aug 05 '14 at 16:30
2

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.

buzzzzjay
  • 1,140
  • 6
  • 27
  • 54
  • Thanks for your answer, apologies it took so long to revisit! However, the question states that I had already tried using a similar grouping expression with a page break. 65000 does not give enough room for headers and footers as my first grouping level includes a group footer, and I will have thousands of these footers to take into account which are not counted in the RowNumber() function – JamieA Oct 30 '13 at 12:51