Home > SSRS > Page Break at Nth row in SSRS Report

Page Break at Nth row in SSRS Report


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.

 

Advertisements
  1. subbareddy
    June 21, 2011 at 9:29 am

    Thanks…
    one more question………how to delete duplicate row in sql server with out using cursors.

  2. June 21, 2011 at 9:37 am

    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.

  3. subbareddy
    June 22, 2011 at 8:55 am

    this is not my question…we have the table having duplicate records ..how to remove those duplicate records in that table only.

    • June 24, 2011 at 1:54 pm

      OK, could you share your table design with sample data? we can examine and design the simplest way to remove the duplicate rows.

    • Santhi
      April 18, 2014 at 12:12 am

      Go to the properties select Hide duplicates as true

  4. T.Rajendra
    July 17, 2011 at 3:02 pm

    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

  5. October 5, 2011 at 10:36 pm

    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”.

  6. mallis
    October 25, 2011 at 9:52 am

    its very use full to me
    Thanks

  7. Rajesh Palo
    April 26, 2012 at 12:14 am

    Excellent job……..

  8. Srini
    September 27, 2012 at 6:06 am

    hi,
    if i have a group already in tablix then this method for page break is not working. please give me solution

    • September 28, 2012 at 3:02 pm

      You can have another group and define the page break expression.

      • Misha
        September 9, 2015 at 4:00 pm

        Have another group as a parent of the existing group? Or as “adjacent” (before or after)?

  9. manojkumar
    October 3, 2012 at 2:27 am

    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…

  10. October 10, 2012 at 6:12 am

    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

  11. Marimuthu
    March 6, 2013 at 11:54 am

    Excellent step by step article.

  12. John Smith
    June 14, 2013 at 7:27 am

    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

    • June 14, 2013 at 10:11 am

      Hello John Smith

      Yes, It is possible to do in SSRS. Please refer this link for the solution.

  13. Chip Thomas
    January 22, 2014 at 11:03 am

    Thanks a bunch. You rock!

  14. Tonsmit
    February 10, 2014 at 11:08 am

    hello okay?
    use VS.Net 2013 I tried to do “= Ceiling ((RowNumber ((Nothing)) / 25)” It is failing.
    Can you help me?

    • February 10, 2014 at 11:19 am

      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.

  15. Tonsmit
    February 10, 2014 at 11:54 am

    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?

  16. Tonsmit
    February 10, 2014 at 11:56 am

    I’m from Brazil!

    • February 10, 2014 at 12:15 pm

      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.

  17. Tonsmit
    February 10, 2014 at 12:16 pm

    Sorry if I made mistake in my writing, I use google translator.
    “Sorry if you have errors in my writing, I use google translator.”

  18. Tonsmit
    February 10, 2014 at 12:24 pm

    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.

  19. Tonsmit
    February 10, 2014 at 3:11 pm

    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

  20. April 9, 2014 at 6:21 am

    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…

  21. April 28, 2014 at 10:42 pm

    Awesome…..!!!!!

  22. April 28, 2014 at 10:43 pm

    Reblogged this on AXpedia and commented:
    Page break at nth row in SSRS AX 2012.

  23. Anton
    May 15, 2015 at 4:02 am

    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 ?

  24. Ramana
    September 2, 2015 at 12:37 pm

    Good one.. 🙂 Thanks for posting..

  25. Luis Madureira
    December 9, 2015 at 6:19 am

    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?

  1. October 30, 2014 at 8:44 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: