We can design a SSRS report with a constraint of only N rows per page. It will be interesting if I explain this with an example.
Scenario
Report developer get a request from team lead saying… Design a simple report with table component and display all rows available in a table x. But, you should show only 10 rows per page in the SSRS report. PS: I am expecting this report to be done with in 15 mins.
The brave report developer just design a report by pulling data to the report without our the 10 rows per page constraint.
I will be showing you that how to display only N rows in the report in this article.
Implementation
Design a report as described in the article. This implementation topic has page break configuration in the report.
Step 1: A simple design to show all rows in a table using table component.
Step 2: Add a group to the detail row
Step 3: Add group expression “=Ceiling((RowNumber(Nothing)) / Nth row)” and Click OK
Step 4: You may get the following error if you run the report after step 3.
Step 5: Open Row group properties and remove sort expression.
Step 6: Now, report will show 30 rows in a group. Next step is to define page break for the group. Go to page breaks tab and select page break option
Step 7: Now, hide the group header and detail column from the table component and run the report.
Thanks for reading. Please subscribe for e-mail for instant article delivery to your inbox. For latest updates vision Facebook page SQLServerRider.com and like the page.
SQL Server Rider 
Thanks…
one more question………how to delete duplicate row in sql server with out using cursors.
Interesting Question.
Just move the unique records to a stage table and truncate the source table.
Finally, move records from the stage to source table.
this is not my question…we have the table having duplicate records ..how to remove those duplicate records in that table only.
OK, could you share your table design with sample data? we can examine and design the simplest way to remove the duplicate rows.
Go to the properties select Hide duplicates as true
WITH CTE_DUPLICATE_DELETE
AS
(SELECT HTNO,ROW_NUMBER() OVER (PARTITION BY HTNO ORDER BY HTNO) AS DUPLICATES FROM [STUDENTS])
DELETE FROM CTE_DUPLICATE_DELETE WHERE DUPLICATES>1
Hi…The above code will help you to delete duplicate values in your table..@Rajendra
Thanks Rajendra!
Works fine. thanks for posting this, I just did one additional step to get this behaviour in HTML format: In tablix properties check box for “Keep together on one page”.
Thanks Kaleem.
its very use full to me
Thanks
Thanks Mallis!
Excellent job……..
hi,
if i have a group already in tablix then this method for page break is not working. please give me solution
You can have another group and define the page break expression.
Have another group as a parent of the existing group? Or as “adjacent” (before or after)?
you have created only one group if i already have parentgroup and detail. i need to print only 10 records per page ..how can i do that pls help me…
Hi I have a report with an existing report grouped by user with multiple rows of different data for each user
i.e
row1 Firstname lastname Dob
row2 Address
row3 HealtProblems
each users details uses over 30 rows with a page break between each user what I want is a page break after 20 rows within each users details,I have tried your methodbut it adds the group after my existing one unless I add it in the grouping window but either way I don’t get a group header for the new group, I can’t find the hide header and detail option only the hide group and when I try to run it I get get the following error “The report failed because the report could not be built.” with a specific error “[rsInvalidDetailDataGrouping] The grouping ‘Group2’ has a detail member with inner members. Detail members can only contain static inner members.”
Any ideas would be greatley appreciated thanks
Excellent step by step article.
Hi, is there a way so that instead of every Nth row it breaks only once?
In my report I have a table on the first page which needs to be broken at the 10th row but any remainder rows are not to be broken
e.g. table of 30 rows, first 10 rows on 1st page and remaining 20 rows on the next rather than breaking at the 20th row making a 3rd page
thanks
Hello John Smith
Yes, It is possible to do in SSRS. Please refer this link for the solution.
Thanks a bunch. You rock!
You are welcome.
hello okay?
use VS.Net 2013 I tried to do “= Ceiling ((RowNumber ((Nothing)) / 25)” It is failing.
Can you help me?
Can you please make sure that you have implemented all the steps given in the blog?. Because, that expression contains built-in functions only and it should not give any error.
Thanks for answering!
Sorry if you have errors in my writing, I use google translator.
So I put this script in the VS.Net ReportViewer “= Ceiling ((RowNumber ((Nothing)) / 25).”
Sure I followed your step by step, but a line is highlighted in red, indicating an error.
You’ve done the reportviewer in VS.net?
I’m from Brazil!
I am using SSRS. I have not tried this in VS.net. I think report viewer component is using the same layout as SSRS and it should not give this error. I will check this in report viewer component.
Sorry if I made mistake in my writing, I use google translator.
“Sorry if you have errors in my writing, I use google translator.”
Thanks I’ll be waiting,
I hope you have success, I also try to do here,
if I succeed, I’ll let you know if I do not warn and because I could not yet.
Now all right!
It was all settled now.
Your code is right!
It was my fault, I left the group “visible = true”.
is now working fine.
Thanks and sorry for the misunderstanding.
Great post!
Made in Brasil ;D
I am glad you have done. Thanks. Have a great day!!!
Thanks for Page breaks for Nth rows..i have a question,
i want to display column header for every page.
Eg: S.NO Employee Name Sal Tax Amt….
Could you please help on this…
Awesome…..!!!!!
Reblogged this on AXpedia and commented:
Page break at nth row in SSRS AX 2012.
Thanks for this topic!
I did it and now when I’m exporting report to excel every group is printed on its our list. What I need to configure to print all rows on one List ?
I did the quick search and found this. Please check this.
http://stackoverflow.com/questions/22061198/suppressing-page-breaks-upon-exporting-a-reporting-services-report-to-excel
Good one.. 🙂 Thanks for posting..
I’m getting this error msg:
A group expression for the grouping ‘Group1’ uses the RowNumber function with a scope parameter that is not valid. When used in a group expression, the value of the scope paramenter of RowNumber must equal the name of the group directly containing the current group.
Can you help me?