I have a pretty simple SSRS 2012 report which is basically a select * from a SQL Table. I have more than 5 million records to be exported to Excel 2010 , so trying to implement a page break after every 1 million records so that the report becomes exportable to Excel. Column1, Column2 Asia, 100 Europe, 50 Africa, 20 Africa, 30 Africa, 23 Australia, 30 In above Example if i page break after every 3 records, then Africa would get split on 2 Pages which is undesirable. Also, if i group on Column1 and insert a page break after each group then there would be so many unnecessary pages. I am able to Page break after every x records, but how to prevent the split of column 1 across 2 pages. If splits are to occur then a new page should be used for that particular value. Any thoughts?

I am assuming that you are doing the page break after every x records by grouping on a count. If so then select the group and in properties set KeepTogether = true. With this setting, the only time a group will be split on separate pages is when it contains more records than a single page will hold.Please Mark posts as answers or helpful so that others can more easily find the answers they seek.

Appreciate your response Tim, but it doesnt work.

Sorry. If your dataset were as described in your original post and you are grouping on =CInt(RowNumber("DataSetName")/3) and adding a page break after group to break on every 3rd record, then you would need to add a child group on Fields!Column1.Value. You don't need the group row and leave the Fields!Column1.Value in the first detail column as you show. Set this group's KeepTogether=True.Please Mark posts as answers or helpful so that others can more easily find the answers they seek.

Thats what I tried Tim, but it doesnt work as said earlier. This is actually more tougher than it seems i guess. I may be missing something because your solution seems quite logical, yet it doesnt work. If you get it to work please let me know too.

Hi Ashish, Firstly why the reporting services is used to export a report of 5 million rows ? You can use Integration services +Views to do the same its an burden for report server to fetch 5 million rows and export it...Strongly advised to move to SSIS+Views . To answer your question. You can sort the data which you are getting into the report [Sorting not a report level ! ] But still sorting costs you dearly at SQL server engine level as well and later apply a page break after 1 million records. If you need after 1 million records you may need to create a outer group which will have 1 - 5 values and add a page break on it.

Thanks Rakesh, unfortunately this is a auditing report and i cannot move it to SSIS. I can sort it in SQL Server I agree. I like your idea of making an outer group with values 1 to 5 and adding a page break on it. But the question is how to get those values depending on the conditions? Can you help me with SSRS Expression for doing it? if not at SSRS, I am even ready to move this logic to SQL Server, but I am struggling with the query!

I tested locally as follows: Add table to report. Set dataset to Dataset1 (the dataset containing Region, Count fields). Delete right data column. Add Region field to left data column and Count field to right data column. Add parent group from Detail (Row Groups pane) > Group1 (grouping formula below). Add parent group to Group1 >Region. Based on the requirement for 1,000,000 records per page and assuming 5 million records, the grouping formula for Group1 is =Ceiling(RowNumber("Region")/1000000) Add a page break to Group1 to get the break every 1,000,000 records. CInt from earlier formula resulted in first page break at 500,000, then at 1,000,000 afterwards. Ceiling fixes that. Set KeepTogether = True on Region. I tested this (scaled down. my dataset had ~2500 records instead of 5 million) and the result was one page for each region. If a region had more records than was allowed on one page, it would spill into a second page. This was the case for my Africa records. My page break was a 400 and Africa had ~475 records.Please Mark posts as answers or helpful so that others can more easily find the answers they seek.

Hi Tim, I made 3 groups as per your instructions. Region -> Group1 -> Details Region : Grouped on Column Region Group1 : Grouped on the expression you specified (=Ceiling(RowNumber("Region")/1000000) ) Details: Not Grouped! Tried Setting, KeepTogether for Top most (Region) group, but the Regions still get split. Doesnt work! Appreciate your help though.

Did you add page break to group1? Even with that, it will split if records for 1 region exceeds 1,000,000.Please Mark posts as answers or helpful so that others can more easily find the answers they seek.

Yes Tim. I had added that too. :(

Please send you rdl to timp at ala moad dot com (no space between ala and moad). There is a difference somewhere.Please Mark posts as answers or helpful so that others can more easily find the answers they seek.

SELECT A.NAME, A.SALARY, (ROW_NUMBER() OVER (ORDER BY A.NAME))/100000 As 'OGroup' FROM AUDIT A Insert this 'OGroup' at the outermost group of the table or matrix or tablix and a pagebreak to that group.

I sent my rdl to Tim. Rakesh, I had tried that query and it doesnt work. Even though it does give page numbers after every x records, but it doesnt look ahead and see if a particular NAME is about to split in 2 pages!

Hi Ashish, Thanks for your posting. Based on your scenario, we can use the conditional page break to achieve the requirement. Please follow the steps below: 1. Click the Details group in the Row Groups pane. 2. From the Tablix member Properties pane, expand Group-> PageBreak. 3. Set the BreakLocation to End and set the Disable property to the expression like below: =IIF(rownumber(nothing) mod 1000000=0,false,true) The screenshot below is for your reference: If you have any questions, please feel free to ask. Regards, Charlie Liao

Hi Charlie..Appreciate your help. This is a normal page break for 1M recrods which you describe above. In my case i need to prevent group splits as well. Unfortunately I have tried this and didnt get it to work. I sent my rdl to Tim, and if he makes any breakthoughs, I will let everyone know.

A couple more questions: If the records for 2 or more consecutive countries will fit on a single page, do you want them too?You want no more than 1M records per page, correct? So if one group has 1.2M records, you want 1M on one page and the .2M on the next, correct?Assuming above is correct, if the next group is <.8M records, do you want them on the same page with the .2M overflow?Please Mark posts as answers or helpful so that others can more easily find the answers they seek.

"Yes" to all your 3 questions! Thanks Tim!

That is a different behavior than mine. I looked at your rdl and it was nearly identical to mine except that I was sorting on Region in the Region group. No significant effect. I worked on this some more yesterday and will work some more today as time allows. In the interim. the solution I posted results in a separate page for each country and will create a second page for any country that has more than 1M records. It is a work-around for now.Please Mark posts as answers or helpful so that others can more easily find the answers they seek.

Appreciate your help Tim. You have been very kind on this! Unfortunately each country on a seperate page is not acceptable here. On an average,each country will have 6 records which is too less to have on a seperate page considering Excel's 1 M row limits.

I think the only way I can figure to do what you need is with some custom code. The problem is, in order to break on or before 1M records on a page we need to know where the previous page break is in the record set. Page info is only available in the header and footer. I am working on a custom code function that will store the index value of the last record on each page in an array, making it available to calculate the max break point for the next page. I will post it when I have it tested.Please Mark posts as answers or helpful so that others can more easily find the answers they seek.

I had some time to play with this and ran into some unexpected issues. One of those issues is a limitation of my environment. It is low on disk space so I cannot cache large query results. I think this is resulting in a truncated query result set but here is the solution: Add a field to your query to calculate group count: COUNT(1) OVER (PARTITION BY GroupField) AS GroupCount (where GroupField is the field you will group on in the table (i.e. Country)Add a parent group to the Details in Row Groups. Group on GroupField (Country).Add an integer parameter for MaxPageRecords and set default to appropriate value.Add this code to the code module: Public rtn As Integer = 0Public Function PageBreakIndex(ByVal GroupCount As Integer, ByVal DataIdx As Integer, ByVal GroupIdx As Integer, ByVal MaxPageRecords As Integer) As Integer If (DataIdx-GroupIdx)+GroupCount > rtn+MaxPageRecords Then If DataIdx = rtn + MaxPageRecords Or GroupIdx = 1 Then rtn = DataIdx End If End If If rtn = 0 And DataIdx > MaxPageRecords Then rtn = DataIdx End If Return rtn End Function Set page break on details as described by Charlie 1. Click the Details group in the Row Groups pane. 2. From the Tablix member Properties pane, expand Group-> PageBreak. 3. Set the BreakLocation to Start and set the Disable property to the expression like below: =IIf(RowNumber(Nothing)=Code.PageBreakIndex(Fields!GroupCount.Value,RowNumber(Nothing),RowNumber("Country"),Parameters!MaxPageRecords.Value),False,True) See if this gets the results you need. Edit: The last if statement in the code was added because I found that the rtn variale was getting reset with a new page load when dealing with a lot of records per page. So when rtn=0 we reset it to the DataIdx but only when we are on the second or later page (DataIdx > MaxPageRecords). Please Mark posts as answers or helpful so that others can more easily find the answers they seek.

Let me know if this code approach works for you. It does not require any additional references so should be plug and play.Please Mark posts as answers or helpful so that others can more easily find the answers they seek.

Thanks Tim..Will check and get back to you soon.

The solution provided kind off works but not completely. On the first page load it shows more than MaxPageRecords records, but if i use the Back button after navigating to a different page, then it becomes as expected. Looks great with this in the browser using Back button once. Once exported to excel it behaves very weird with some random behaviuor. Appreciate all your hard work Tim. Thanks a ton!