Only display # rows per page in a SSRS report.

One problem I ran into and tried every solution on the net (that I could find) with no luck. I had to display only 22 rows per page, with blank rows -- if the data is not filled in for the other rows.

Step 1: SQL
So, first thing is to get your sql query to return, not row numbers but page numbers. So in this case, I used the row_number function found in SQL-Server subtracting 1 and then divided by the number of rows I needed per page:

((row_number() over(order by license_no)) -1) /22 as 'Page'

Step 2: Add Parent Group
In my report I needed to display a header, a footer, detail information, and empty rows if not exceeding 22. This is of course done with SSRS's Table.

First add a new parent group and in the Group Properties/General add a group on to the "Page" field. This group should span the whole page.

Don't bother with the page break sections, this will just mess things up.

Step 3: Add Child Group with blank rows
The child group is what I used to hold the blank rows.  Within that group I added 22 rows with no information in the textboxes.

Step 4: Change visibility of blank rows
Right-clicking on the row, and select Row Visibility. Select the bullet: "Show or hide based on an expression". Within each row the expression will check if the row count is less than the ((page + 1) * rows_per_page) - (rows_per_page - position)
Example (Note:"section2" is my dataset):
[Row 22 Expression]: =IIF(CountRows("Section2") < ((Fields!Page.Value + 1) * 22),false,true)
[Row 21 Expression]: =IIF(CountRows("Section2") < ((Fields!Page.Value + 1) * 22) - 1,false,true)
...


This is all that is needed to acomplish this task.

If you need to hide a row until the end, I used the following visibility expression:
=IIF(Fields!Page.Value = Last(Fields!Page.Value,"Section2"),false,true)

And of course if you need to repeat the header on each page, like I do. This is found in the Tablix Properites/General and just put a check mark in the "Repeat header rows on each page".

How to update large count of rows without locking them

Found this interesting, how to update large databases, so that you don't have to lock rows
http://blogs.msdn.com/sqlpfe/archive/2010/01/06/tsql-coding-patterns-i.aspx

So instead of the usual:

 UPDATE dbo.Foo

SET Column = 'Value'

Try:

DECLARE @UpdatedRows(PK_Id int NOT NULL PRIMARY KEY)

DECLARE @var INT

SELECT @var=0 -- this resets @@ROWCOUNT=1

WHILE @@ROWCOUNT >0

BEGIN

UPDATE TOP(1500) BAR

  SET Column='Value'

OUTPUT inserted.PK_ID

INTO  @UpdatedRows

FROM  dbo.BAR as BAR

WHERE NOT EXISTS (SELECT 1 FROM @UpdatedRows UPD WHERE UPD.PK_ID=BAR.PK_ID)

END

I prefer one of the ways the commentator offered: (easier to follow)

WHILE @@ROWCOUNT >0

BEGIN

 UPDATE TOP(1500) BAR

   SET Column='Value'

 WHERE

   Column <> 'Value'

END