Pages

Sunday 29 July 2012

Page Break at Nth row in SSRS Report Step by Step


We can design a SSRS report with a constraint of only Nth rows per page.

I am showing you that how to display only Nth rows in the report in this article.

Here is an example from  AdventureWorks Database.

SQL Scripts.

SELECT TOP 1000 [StateProvinceID]
      ,[StateProvinceCode]
      ,[CountryRegionCode]
      ,[Name]
      ,[rowguid]
      ,[ModifiedDate]
  FROM [AdventureWorks].[Person].[StateProvince]

Select the Header Row of the table in the report.
Go to Properties Window.
 Under the BackgroundColor property, Select color from drop down.


Now create a group.



After creating the group go to the group properties and click on fx button.



And set the expression for group properties.



Expression :
=Ceiling((RowNumber(Nothing)) / 15)

( Note : You can put Nth number in place of 15. For example if you want to your page break affect after each 25th row than you have to put 25 in place of 15 )  

 Now, report will show 15 rows in a group. Next step is to define page break for the group. Go to page breaks tab and select page break option.



And now run the report. Below the preview of report.








3 comments:

  1. This post is probably where I got the most useful information for my research. Thanks for posting, maybe we can see more on this.
    Are you aware of any other websites on this
    msbi


    ReplyDelete
  2. 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

    ReplyDelete
  3. Hi ,
    Thanks for the article ; I would like to find out how can you apply grouping for this report ,

    Eg : if i say “select name,location,col2,col2 from saletable ” and have the data displayed straight in to the table with the above grouping then it works perfectly.
    But if I try to have a grouping with a parent then it complains regarding this.
    Eg : have a list box then have the table of records , then have a list box and try to set the grouping expression as =Filed!location.Value then you will get this error

    RowNumber function with a scope parameter that is not valid. When used in a group expression, the value of the scope parameter of RowNumber must equal the name of the group directly containing the current group. (rsInvalidGroupExpressionScope) Get Online Help

    Do you know how to resolve this issue.

    ReplyDelete