About Me

My photo
Northglenn, Colorado, United States
I'm primarily a BI Developer on the Microsoft stack. I do sometimes touch upon other Microsoft stacks ( web development, application development, and sql server development).

Wednesday, April 14, 2010

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