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
This post is probably where I got the most useful information for my research. Thanks for posting, maybe we can see more on this.
ReplyDeleteAre you aware of any other websites on this
msbi
Hi, is there a way so that instead of every Nth row it breaks only once?
ReplyDeleteIn 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
Hi ,
ReplyDeleteThanks 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.