locked
Assigning row number for each group and returning 5 groups in one page RRS feed

  • Question

  • Hi,

    When I am rendering SSRS report to Excel, I faced an error:

    Excel Rendering Extension: Number of rows exceeds the maximum possible rows per sheet in this format; Rows Requested: 96619, Max Rows: 65536

    I came across solution to this here:

    http://www.bi-rootdata.com/2012/09/how-to-restrictset-number-of-records.html

    It works fine but can I do the row count for groups instead of rows i.e. my report is already grouped by State. I wanted to show records for first 5 states on one page and the next 5 on another instead of showing 10000 records on each page.

    Thanks.

    Tuesday, August 27, 2013 6:27 PM

Answers

  • Hello,

    According to your description, you want to add page break based on the number of instance in a group.

    If you use SSRS 2008 R2 or later version, we can disable page break with expression on group level to meet your requirement.

    I created a simple report in my test environment. Suppose the group was named “Group1” in your report. You can refer to the steps below:
    1. Click the “Group1” in the Row Groups dialog box.
    2. In the Properties dialog box, expand PageBreak properties below Group.
    3. Below PageBreak, select “End” with BreakLocation property.
    4. Click Disable, select  “<Expression>”. You can refer to the expression below:
    =IIF((RunningValue(Fields!Group1.Value, CountDistinct, Nothing)) Mod 5=0,False,True)
    You can refer to the screenshot below:
     
     
    Hope this help. If there are any misunderstanding, please feel free to let me know.

    Regards,
    Alisa Tang

    • Marked as answer by sk12346 Wednesday, August 28, 2013 1:53 PM
    Wednesday, August 28, 2013 9:13 AM

All replies

  • Hi,

    In SSRS ,you can restrict the number of rows by using the RowNumber function  and the number of rows displayed on a page is also dependent on the page size assigned to that report.As far as your question is concerned you want to restrict the no of rows by the grouping column(i.e state) is not possible as we do not know the number of rows displayed under a particular state.

    If you can share more details about the report &  data to be shown in the report, we can think of a workaround  at the data set level.

    Thanks & Regards,

    Sharing knowledge is fun!!


    Mark as answered if my post solved your problem and Vote as helpful if my post was useful.
    • Edited by Milan Das Tuesday, August 27, 2013 8:07 PM
    Tuesday, August 27, 2013 8:00 PM
  • Hello,

    According to your description, you want to add page break based on the number of instance in a group.

    If you use SSRS 2008 R2 or later version, we can disable page break with expression on group level to meet your requirement.

    I created a simple report in my test environment. Suppose the group was named “Group1” in your report. You can refer to the steps below:
    1. Click the “Group1” in the Row Groups dialog box.
    2. In the Properties dialog box, expand PageBreak properties below Group.
    3. Below PageBreak, select “End” with BreakLocation property.
    4. Click Disable, select  “<Expression>”. You can refer to the expression below:
    =IIF((RunningValue(Fields!Group1.Value, CountDistinct, Nothing)) Mod 5=0,False,True)
    You can refer to the screenshot below:
     
     
    Hope this help. If there are any misunderstanding, please feel free to let me know.

    Regards,
    Alisa Tang

    • Marked as answer by sk12346 Wednesday, August 28, 2013 1:53 PM
    Wednesday, August 28, 2013 9:13 AM
  • @Alisa:  Thanks for the lucid explanation. It worked.

    Wednesday, August 28, 2013 1:53 PM