SSRS how to limit the number of group rows per page

Question

I have a SSRS report with dataset as follows:

Record 1 Item 001 Qty 10 Category A
Record 2 Item 002 Qty 10 Category A
Record 3 Item 003 Qty 10 Category B
Record 4 Item 004 Qty 10 Category B
Record 5 Item 005 Qty 10 Category B

If no grouping by Category, I can limit the number of rows per page by Add group example group G1 which contains expression : Int((RowNumber(Nothing)-1)/10). This will limit maximum 10 rows per page.

But then I Add Group by Category and hide rows of Item, so the report displayed 2 rows as below and I also add running number for Category count using "=RunningValue(Fields!Category.Value,CountDistinct, "T1")".

Category A Qty 20 Category row 1
Category B Qty 30 Category row 2

And for controlling the number of rows per page, I create group G1 (which is parent group of Group Category) with expression =Int((RunningValue(Fields!Category.Value,CountDistinct, "T1")-1)/10).

I thought that I could use Category count for group expression for controlling the number of page, but it gave me error "Unable to issue because there is no layout file" when running the report.
Please advise, so that I can limit rows per one page, if the visible rows are the group row.
Thank you.

Answer 1

Hi @Thianto Andayana ,

=Int((RunningValue(Fields!Category.Value,CountDistinct, "T1")-1)/10)

Where is this expression used? Was it when the parent group G1 of the Group Category was created? Do you apply it to grouping expressions?

As far as I know, RunningValue cannot be used in group expressions. To calculate the running value of the number of rows, use RowNumber. For more information, see RowNumber Function (Report Builder and SSRS).
If I mis understand what you mean, please feel free to let me know.

Best Regards,
Joy

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi Joy
Thank you for your feed back.
Yes, I used the expression "=Int((RunningValue(Fields!Category.Value,CountDistinct, "T1")-1)/10)" in the parent group of Category group. I created group G1 for Category grouping (and set visible row only Category row example Category A, Category B - and hide Item rows example Item 001, Item 002, and so on). And then I created parent group G2 containing the expression "=Int((RunningValue(Fields!Category.Value,CountDistinct, "T1")-1)/10)" to limit one page should contain 10 category rows maximum.

In first page number, I want maximum 10 rows printed (Category A until Category J) - Not 10 rows of Item (because Item is hidden already).
Category A Qty 20 Category row 1
Category B Qty 30 Category row 2
Category C Qty 20 Category row 3
Category D Qty 30 Category row 4
Category E Qty 20 Category row 5
Category F Qty 30 Category row 6
Category G Qty 20 Category row 7
Category H Qty 30 Category row 8
Category I Qty 20 Category row 9
Category J Qty 30 Category row 10

In second page number and next page number, I want other category printed with maximum 10 Category rows (not 10 Item rows).
Category K Qty 20 Category row 11
Category L Qty 30 Category row 12
Category M Qty 20 Category row 13
Category N Qty 30 Category row 14
Category O Qty 20 Category row 15
Category P Qty 30 Category row 16

If I use "RowNumber" function in G2 group expression, I cannot control 10 rows of Category rows as above. Because RowNumber count by Record 1, Record 2 and so on - or as my previous post example (Item 001, Item 002, and so on). Row number does not count by Category (Group G1).
Record 1 Item 001 Qty 10 Category A ==>> RowNumber function return 1 ==>> I want for Group G2 expression to return 1
Record 2 Item 002 Qty 10 Category A ==>> RowNumber function return 2 ==>> I want for Group G2 expression to return 1
Record 3 Item 003 Qty 10 Category B ==>> RowNumber function return 3 ==>> I want for Group G2 expression to return 2
Record 4 Item 004 Qty 10 Category B ==>> Row Number function return 4 ==>> I want for Group G2 expression to return 2
Record 5 Item 005 Qty 10 Category B ==>> Row Number function return 5 ==>> I want for Group G2 expression to return 2
And so on...

Because my final report hides Item 001, Item 002 and only display visible row of Category A (line 1), Category B (line 2), and so on. So I need to control maximum rows per page by Category, not by Item. And I cannot use RowNumber function in Group G2.

I hope you can get what I expected.
Thank you.

Answer 3

Hi Joy
Thank you for your advice.
But we use application package system, which we do not have access to change the query (dataset). We only can use the existing dataset in the rdlc file.
Thank you.