none
How to limit the number of rows in a table nested in list? RRS feed

  • Question

  • How to limit the number of rows in a table nested in list?


    Hi everyone,
    The thing is, I have a list with tables inside in another list and when I'm trying to sort and filter data in a table(top 20 customer purchases for each seller) from nested list I get wrong entries.

    Please open the attached screenshot to see what I mean.

    last table is sorted by PurchaseSum desc and everything is working as expected.

    After this, to limit the number of rows I've added a filter with Top N operator on unique PurchaseID(on screenshot this field was accidentally deleted), but some rows are missing in the table after adding this filter.

    Please tell me what I'm doing wrong and maybe there are other ways to limit the number of rows to a table using SSRS features?

    https        ://social.msdn.microsoft.com/Forums/getfile/1408886

    Thanks in advance,

    Alexandr
    Thursday, February 28, 2019 11:16 PM

Answers

  • Since RowNumber(Nothing) returns absolute values for all tables(it's not reset for each nested table), another expression solved my issue:

    =IIF(RunningValue(Fields!ID.Value, CountDistinct, "ParentDatasetName")<=20, false, true)

    Monday, March 4, 2019 10:24 AM

All replies

  • Hi Alexandr Todorov

    According to your description , you  want to set the row number limit in table ,right ?

    If so you need to add one more group under the list as the parent group of the detail row in table.

    And then set the group on with the expression like : =CEILING(RowNumber(Nothing)/25)   

    25 means each group instance would display 25 records.

    See more detailed information at :Display a fixed number of rows per page for an SSRS report

    Hope it can help you.

    Best Regards,

    Eric Liu


    Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.

    Friday, March 1, 2019 1:41 AM
  • Hi Eric Liu,

    thanks for your reply.

    Yeah, I want to limit the number of rows and to display a maximum of 20 rows in each table.

    I did everything according to the mssqltips article, but it trows and error that "the value of the scope parameter of RowNumber must equal the name of the group directly containing the current group".

    Screenshots are attached below(I still can't add images, so remove spaces from the string):

    https://          social.msdn.microsoft.com/Forums/getfile/1409327

    ---------------------------------------------

    https://           social.msdn.microsoft.com/Forums/getfile/1409329

    Best regards,

    Alexandr

    Friday, March 1, 2019 12:45 PM

  • and, of course, I tried to set parentGroup name as a scope parameter of RowNumber:

    =Ceiling(RowNumber("CompanyGroup")/20)

    - the error disappeared, but I get all the rows again, not just the first 20.

    Sunday, March 3, 2019 11:41 AM
  • Cannot you limit in SQL query?

    SELECT * FROM 

    (

    SELECT <columns>,ROW_NUMBER () OVER (PARTTION  By seller ORDER by sale desc) rn

    FROM tbl

    ) AS Der WHERE rn<=20


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, March 3, 2019 2:23 PM
  • No, as far as I know, I can't.

    As I've mentioned before, the report contains a list of tables nested in another list.

    Today I have displayed the required number of rows by setting the following expression in "Visibility" menu in row details group:

    =IIF(Ceiling(RowNumber(Nothing)/20)<=1, false, true)

    and everything is working as expected.

    So thanks for all for help.

    Best regards,

    Alexandr

    • Proposed as answer by Mitarai Queen Monday, March 4, 2019 8:10 AM
    Sunday, March 3, 2019 7:15 PM
  • Hi

    Seems you have solved your issue , if possible you could mark your reply as answer , so that other members in this forum would get benefited from it.

    Best Regards,

    Eric Liu


    Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.

    Monday, March 4, 2019 8:11 AM
  • Since RowNumber(Nothing) returns absolute values for all tables(it's not reset for each nested table), another expression solved my issue:

    =IIF(RunningValue(Fields!ID.Value, CountDistinct, "ParentDatasetName")<=20, false, true)

    Monday, March 4, 2019 10:24 AM