none
How to limit the number of rows displayed in table in report? RRS feed

  • Question

  • Hi,

    I have select 50 rows through sql query and i want to display 10 rows at a time in the table in report. So can u help me to get the solution?


    Thanks, Pranil Yambal | Pranil.Yambal@hotmail.com
    Saturday, January 29, 2011 8:56 AM

Answers

  • Hi,

    One option is to generate group rows in sql query, for this you can use row_number() or to evenly devide you can use NTILE() in sql query. Based on that group number, you can create a group and set a page break after each group. So, only 10 rowes will get displayed in each page.

    You can also use dataset filter to filter out some rows. But, this all depends on your dataset.

    If you only want those 10 records in report, i suggest you should only pick those 10 records from SQL.

    If you can add some more details, it would help in answering. If this does not answer your query, kindly revert.


    -Chintak (My Blog)

    • Marked as answer by Pranil Yambal Monday, January 31, 2011 5:29 PM
    Saturday, January 29, 2011 10:21 AM

All replies

  • Hi,

    One option is to generate group rows in sql query, for this you can use row_number() or to evenly devide you can use NTILE() in sql query. Based on that group number, you can create a group and set a page break after each group. So, only 10 rowes will get displayed in each page.

    You can also use dataset filter to filter out some rows. But, this all depends on your dataset.

    If you only want those 10 records in report, i suggest you should only pick those 10 records from SQL.

    If you can add some more details, it would help in answering. If this does not answer your query, kindly revert.


    -Chintak (My Blog)

    • Marked as answer by Pranil Yambal Monday, January 31, 2011 5:29 PM
    Saturday, January 29, 2011 10:21 AM
  • Hi,

     Can you send me some example for  grouping in query for limiting no of rows. So that i can try it.


    Thanks, Pranil Yambal | Pranil.Yambal@hotmail.com
    Saturday, January 29, 2011 11:53 AM
  • Hi,

    I have select 50 rows through sql query and i want to display 10 rows at a time in the table in report. So can u help me to get the solution?


    Thanks, Pranil Yambal | Pranil.Yambal@hotmail.com


    Pranil ,

    Here you go , Restricting no: of rows in a page MR Challen has many options to do so .

    http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/1460bbe2-da79-4bdd-b2d7-0d08d4948144

    Thanks .

     

     

     


    Rajkumar Yelugu
    Saturday, January 29, 2011 7:28 PM
  • You can try to add top 10 to your query. select top 10 * from table. Go to the following blog for more detial. http://ssrsdeveloper.blogspot.com/p/sql101.html

     

    Saturday, January 29, 2011 9:19 PM
  • Hi,

    The methods suggested by Chintak should work. However, you could also use the RowNumber function coupled with Ceiling function on a list control to limit the number of rows as required. Using this method would not require any changes to the dataset. Please find the steps below.

    1. Drag a list control into the report body
    2. Click the list control, you would notice (details) group in the group panel at the left-bottom corner.
    3. right-click the (details) group, and select group properties, then click Add button to type =Ceiling(RowNumber(Nothing)/10) in the expression, click ok and close the properties window.
    4. Now drag your table control into the list control

    Hope this helps.


    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

    BH

    Monday, January 31, 2011 6:10 AM