Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
limit number of rows

Answered limit number of rows

  • Tuesday, December 28, 2010 3:16 PM
     
     
    Sign In to Vote
    0

    I want to limit the numer of rows per page to 20 in my Matrix

    so I added a group and with the followingg expression: floor((RowNUmber(Nothing)-1)/10)

    And set page break on group end

    in preview mode the message

    rownumber canot be used in sort expressions.

    is generated and the report won't show

    Who can help

     

    Jos van Hertrooij

     

     

     

All Replies

  • Tuesday, December 28, 2010 4:07 PM
     
     
    Sign In to Vote
    0

    Hi Jos,

    Could you try to make page header and footer bigger? This will make the area available for data rows smaller.

     

     

     

    Remember to mark as an answer if this post has helped you.
     
  • Wednesday, December 29, 2010 3:22 AM
     
     
    Sign In to Vote
    1

    As per the error message, it appears you've used RowNumber in the sort as well. Please apply the expression only in the group and remove if any from sort.

    Please try. Regards

     

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

    BH

     
  • Wednesday, December 29, 2010 5:13 AM
     
     
    Sign In to Vote
    1
    Go to Sorting menu under properties of the Group and delete the column which present in the sorting with the expression
    floor((RowNUmber(Nothing)-1)/10) and try it will work
    Nanda
     
  • Wednesday, December 29, 2010 8:38 AM
     
     
    Sign In to Vote
    0

    Hai Nanda,

    what I do is i add a new group to the row group pane named Pagebreak

    in the properties General i set  group on to =floor((RowNUmber(Nothing)-1)/10)

    in the sort it has my field [Price] this because it insists of having an entry there

    in Pagebraks i checked Between each instance of a group

    result:

    no error anymore but also no pagebreaks

    where am I going wrong

     

    Thanx in advance,

     

    Jos

     
  • Wednesday, December 29, 2010 9:19 AM
     
     
    Sign In to Vote
    0

    Press CTRL+ALT+O(Output window) and check warning are there, some time if there are any warnings it will not work.

    I have tried the same as mentioned above, it worked for me without any error/warning. Please check the group which you have created.

    Nanda
     
  • Thursday, December 30, 2010 7:53 AM
     
     Answered
    Sign In to Vote
    0

    Hi Jos,

     

    From your description, my understanding is that you want to use RowNumber function to limit the number of rows per page in Matrix. If I have misunderstood, please feel free to let me know.

     

    You can try to use custom code to achieve that, please follow the steps given below:

    1. I create a report with matrix. I write a query based on AdventureWorks to select all the HumanResources.Employee table records, order by Title (this is important), and then create a dataset. In the report, drag the "Title" field to rows cell, drag the "Gender" field to Columns cell, and drag the "EmployeeID" field to Data cell. Here is the query: 

     

    SELECT [EmployeeID]

    ,[Title]

    ,[Gender]

    FROM [AdventureWorks].[HumanResources].[Employee]

    ORDER BY [Title]

     

    2. In report properties, add below custom code: 

     

    Dim FlagTable As System.Collections.Generic.List(Of String)

     

    Dim Flag As Integer

     

    Function MyFunc(ByVal NewValue As String) As Integer

     

    If (FlagTable Is Nothing) Then

    FlagTable = New System.Collections.Generic.List(Of String)

    End If

     

    If (Not FlagTable.Contains(NewValue)) Then

    FlagTable.Add(NewValue)

    End If

     

    MyFunc = FlagTable.Count

     

    End Function 

     

    3. Add a parent group as the outermost group, set it to group on “=(Code.MyFunc(Fields!Title.Value)-1)\20". In "Page Breaks" tab, check the "Between each instance of group" option. And then delete sorting options in "Sorting" tab.

     

    You'd better edit the code to applicable to your scenario, then preview the report again.

      

    You can download my report mode from this link:

    http://cid-854fa6d2b7d88cc7.office.live.com/self.aspx/Work/RowsPerPageLimitation.rdl

     

    Thanks,

    Albert Ye