Reporting Services - Limiting Records Per Page

  • This is probably an elementary question, but for the life of me I can't decide the proper direction to go with it.

    I simply need to create a report which shows only ten records per page. The incoming selection is unknown - may be only a few records, may be hundreds. Regardless of number of pages, must show only ten records per page.

    Is there a table limiting function in SSRS that I can use, or should I approach this with a stored procedure?

    Just looking for a kick in the right direction.

    Thanks!

  • I think you need to do it with an SP as I cannot find a way to break after n records.

  • Add a group to your report and use the following expression to group on:

    =Floor((RowNumber(Nothing) - 1) / 10)

    Also set the 'Page break at and' property for the group.

    Peter

  • Excellent Peter, I should have thought of that, too! It's a simple solution.

  • Peter Brinkhaus (4/27/2008)


    Add a group to your report and use the following expression to group on:

    =Floor((RowNumber(Nothing) - 1) / 10)

    Also set the 'Page break at and' property for the group.

    Peter

    Thank you, Peter! After more research we'd come to a similar conclusion using

    =Ceiling((RowNumber(Nothing)) / 10)

    It is quite reassuring to know we are going along the same path. We've only been using SSRS in this shop for a bit more than a year, migrating from Crystal, and these forums have saved us from frustration many times in the year.

    As an aside I should mention that I love this site and these forums. I've been reading here for several years, but this was the first time I ever had to post a question, and the speed of the responses is incredible. You guys are a godsend and your input is priceless.

    Matt

  • This solution shows 1 record at time but the next record will be 11th record. so the data will be like it shows 1st record then 11th then 21st and so forth..

    any other solution??

    Thanks.

    Gunjan.

  • I guess you added one of the expressions mentioned before to the group expression of a detail row (you can right-click on the detail row, select Edit Group... and then add a Group On expression). This results in the behavior you described. What I tried to suggest was to first add a group (right click the detail row and choose Insert Group rather then Edit Group), add the Group On expression and remove the header and footer.

    Peter

  • Thanks Sir.

    It worked...

    Thanks.

    Gunjan.

  • Awesome! I had to create a report with basically 1 record per page.

    So I want to see a separte form for each customer.

    List properties > Grouping and Sorting properties

    Add: =Ceiling((RowNumber(Nothing)) / 1) to Group on: Expression list

  • I tried that and I got the following error:

    A group expression for the list ‘list1’ uses the 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.

    My understanding is that the GROUP ON EXPRESSION is the definition of the group, so it defines the scope. So where do I add the expression =CInt(Ceiling(RowNumber(Nothing)/25))?

    The specific steps I take are Edit Details Group Properties of List Box -> Group ON Expression = ?

  • I am trying in Ssrs 2008 but when i used Ceiling(Rownumber(Nothing)/10) it displayed only one row in the report am i doing anything wrong(i have 12 rows)

  • I am trying the same expression as suggested int he forum to restrict number of records per page to 20. But it does not work. Still it displays 55 records per page.

  • I am using the following as you suggested successfully.

    Floor((RowNumber(Nothing) - 1) / Parameters!RowsPerPage.Value)

    However, adding this grouping broke the interactive sort that we had in place. The sort currently only sorts those records within each page.

    Any suggestions?

  • Hey it's great to restrict the number of rows per page

    I have one problem.i need to restrict the number of pages for report.

    I have 100 pages report but my client want only first 20pages, for some reports he want last 20 pages.

    how can do that???????????:w00t:

    thanks in advance

    sank

  • Sank,

    You may want to create a new thread for this question as it is a bit different than the main question on this page.

    I don't know how to do what you are being asked to do with reporting services. Will the client only ever want the first 20 or last 20 pages? What determined what is first and what is last? Is there a way of summarizing the data that reduces the # of pages yet still provides the necessary information? Honestly, I can't really imagine a case where a 100 page report is useful so I'm of the opinion that the client needs to rethink what they really want and need in this case.

Viewing 15 posts - 1 through 15 (of 44 total)

You must be logged in to reply to this topic. Login to reply