Want to show fix number of rows per page in SSRS

Question

i search google for showing fix number of rows per page in SSRS and found solution like 1) add group and put a expression like for the view =CEILING(RowNumber(Nothing)/10).

from this website http://gauravsqlserver.blogspot.in/2011/03/how-to-fix-number-of-rows-per-page-in.html i got the info and follow all the step but when i compile report then i am getting error like

A sort expression for the tablix ‘Tablix5’ uses the function RowNumber. RowNumber cannot be used in sort expressions.

so please guide me how to fix this error and also tell me am i on right track to show 10 records per page.

Answer 1

A few years ago, I was posed with an interesting report request that involved retrieving patient appointment records to place into an SSRS report. The format of the report was required to be in the form of a charge slip as shown below. And each page of the charge slip could not contain more than 10 patient records, but still contain a grid/table of 10 rows of patient data… even when there would be less than 10 records retrieved from the query or displayed on the final page. As you can see on the 1st screen shot, we are at page 2 of 2 of the report that shows the remaining 5 patient records and 5 empty rows.

(Note that the report retrieved 15 patients, as there are 10 patients in page one.) Also required was to keep the top and bottom portions of the report static and have those and the patient data fit into a page. Here are the steps that I took to achieve the requirements for the report:

1. I brought over a table object onto the Layout and created two table groups: one for the top and bottom portions of the report (table1_Group1) that also includes the detail data being retrieved from the query, and the middle portion (table1_Group2) that will determine the number of empty rows to display.

The top portion contains the fixed areas which are simply composed of two report header rows. Note, that the final report minimizes to the top 2 group header rows (1, 2 — above the table1_Group2 detail row highligted), and is expanded in the screen shot to show you the construction of the table for the report.

The bottom portion shows the multiple table1_Group1 footer rows needed to hard-code the clinical procedures for the charge slip form. And, in between I added the 10 footer rows from table1_Group2. These footer rows from Group2 is what will allow the report to mimic the desired remaining empty rows in the report form if less than 10 records are populated in the detail row.

But, you ask… how do you know ahead of time how many detail rows will be retrieved? How do determined how many empty rows do you display per page? How do you know not go beyond the required 10 rows of records per page? This is where the power of expressions come in to manipulate report properties.

2. After creating the table layout, right-click on the table outline to bring up the Grouping and Sorting Properties. Since it’s required that no more than 10 records display per page, set the ceiling to 10 in order for the table to group on the detail row for table1_Group1. The ceiling expression is as follows: =Ceiling(RowNumber(Nothing)/10) Also, place a check-mark in the following properties within the Group and Sorting Properties: – Include group header – Include group footer – Page break at end … and Click OK as shown below.

3. Now, we are at the step to begin manipulating the number of empty rows to show in table1_Group2. Select the 1st table footer row for table1_Group2 as shown below. Go to Properties, expand the Visibility property, and add the following expression to the Hidden property: =IIF(CountRows(“table1_Group”)>9, True, False What this expression does is… it will hide this row (TableRow6) when the number of detail rows referenced in table1_Group1 are greater than 9, meaning when there are 10 detail rows… hide this row from the report. This is the logic needed in each subsequent table1_Group2 footer, but as you go down each row, adjust the expression by subtracting one row in the expression till the last row.

So, for example, for the next row (TableRow7), the Hidden property expression is: =IIF(CountRows(“table1_Group1″) > 8, True, False)

This goes on and on till you reach the last Group2 footer row, shown as the 10th row, where the Hidden property expression now reads: =IIF(CountRows(“table1_Group1″) > 0, True, False) …meaning, when there are existing rows, hide the 10th row in the report. Overall, the expressions will automatically adjust the number of footer rows in Group2 that can be displayed (or hidden).

This final screen shot will clarify how these expressions in the Hidden property work:

Again, expressions in report properties are powerful enough to meet most report requirements, along with a little creativity and sweat.

Answer 2

In the group properties, go to Sorting.

You may find a default sorting created.

Delete it and try to run the report.