June 16, 2011 at 2:20 pm
Hi all,
Over the past few days, I've been trying to modify a report to handle the 65,536 row limitation of Excel exporting. My current problem seems to be getting the page breaks to happen in a subgroup.
Originally, the subgroup was a subreport and I read in other threads that subreports can't be paged (even though I found out through my own experimenting that they can be paged when they are not in a multi-cell tablix...they can be paged in a single-cell tablix). One recommendation I followed was denormalizing the data by combining the queries of the main and sub reports which has led me to where I am now.
I've seen (and tried) this grouping technique for row count:
=Int((RowNumber(Nothing)-1)/65000)
This grouping works fine as a top level group, but it isn't causing page breaks when used as a subgroup.
My report has 2 groups and 1 details row. I'm currently using a nested tablix for the inner group because the columns aren't the same width as the outer tablix, but I could merge some cells and get them to line up if that is necessary. I did try it without the nested tablix before and it did not seem to make a difference.
When I put the RowNumber grouping as the top-most group in the outer tablix, I am able to page the report but my "Grand Total" row is getting included on every page (not acceptable).
Any help would be greatly appreciated,
Derek
June 23, 2011 at 9:01 am
If this is a known limitation of SSRS, some advice on how to restructure the report would also be helpful. 😉
June 23, 2011 at 9:53 am
I am not quite clear on what you are trying to accomplish. Are you trying to get exports to Excel to be limited to 65000 rows per tab regardless of natural grouping or do some groups need to be broken into separate tabs because they exceed 65000 rows?
We use Excel 2007 here, so I can't test, but when I do a document map and export to Excel, each group that is shown on the document map is on a different tab in Excel.
June 23, 2011 at 9:58 am
Daniel Bowlin (6/23/2011)
Are you trying to get exports to Excel to be limited to 65000 rows per tab regardless of natural grouping or do some groups need to be broken into separate tabs because they exceed 65000 rows?
The former. I'd like it to limit per tab regardless of natural grouping. Such that if someone were to look at the end of tab #1, they could continue reading the information at the top of tab #2.
June 23, 2011 at 11:39 am
Then probably the easiest way to go would be to add a couple of columns to your dataset query. One for a T-SQL Row_Number, and another for a field that calculates a row based grouping based on the row number where rows 1 - 65000 are group 1, rows 65001 to 130000 are group 2 etc. and then do a document map based on those groups.
And just a bit of personal opinion.....no one really looks at reports with that much data. If anything they want to do some additional number crunching and I would suggest that you simply build the number crunching into your report and stop worrying about how to page the data. But...there is an exception to every rule & opinion. I hope this helps. Good luck.
June 23, 2011 at 1:37 pm
Daniel Bowlin (6/23/2011)
And just a bit of personal opinion.....no one really looks at reports with that much data. If anything they want to do some additional number crunching and I would suggest that you simply build the number crunching into your report and stop worrying about how to page the data. But...there is an exception to every rule & opinion. I hope this helps. Good luck.
I completely agree. In this case, though, the report serves more as a data dump for clients to keep a local copy. I've discussed not using Excel for this, but dropping the feature of exporting to Excel is not allowed.
Daniel Bowlin (6/23/2011)
Then probably the easiest way to go would be to add a couple of columns to your dataset query. One for a T-SQL Row_Number, and another for a field that calculates a row based grouping based on the row number where rows 1 - 65000 are group 1, rows 65001 to 130000 are group 2 etc. and then do a document map based on those groups.
When I try this on the parent group, I only get the first group. When I try it on the subgroup, I still only get a single tab (not including the document map tab...which throws errors when I click on their links).
Here is an example of what I'm trying to accomplish:
=====================
# Page 1
=====================
------------------
Parent Group 1:
------------------
Header Info, More Header Info
Subgroup 1:
Detail line 1
Detail line 2
Subtotal 1
Detail line 3
Detail line 4
Subtotal 2
...
Detail line 64,999
Detail line 65,000
Subtotal 32,500
=====================
# Page 2
=====================
Detail line 65,001
Detail line 65,002
Subtotal 32,501
(End Subgroup 1)
Grand Total 1
(End Parent Group 1)
------------------
Parent Group 2:
------------------
Header Info, More Header Info
Subgroup 2:
Detail line 65,003
Detail line 65,004
Subtotal 32,502
(End Subgroup 2)
Grand Total 2
(End Parent Group 2)
Edit: Removed footer from groups in the code
June 27, 2011 at 8:00 am
Does that help at all?
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply