SSRS reports Group doesn't display properly once limited rows per page

1
votes

I'm trying to make a SSRS Delivery Note report which details required to be grouped by customer and use customer address as report header. One data set contains multiple customers and each customer may has 10+ lines of item details. However detail rows are limited to 10 on each page as the delivery note paper are pre-printed.

So I group all details by customer information, use a big matrix nest with a smaller matrix to display both customer information(Header) and item detail information(Details).

In order to limit detail rows per page I use '=Ceiling(RowNumber(Nothing)/10)' command in Group.

However I notice a big side effect after doing this. For example, Customer A has 15 detail lines and Customer B has 8 Detail Lines, expected result is:

  • Page 1: Customer A Address & 10 item details
  • Page 2: Customer A Address & 5 item details
  • Page 3: Customer B Address & 8 item details

But the actual result is:

  • Page 1: Customer A Address & 10 item details
  • Page 2: Customer A Address & 5 item details
  • Page 3: Customer B Address & 5 item details
  • Page 4: Customer B Address & 3 item details

This is because the RowNumber of customer B details are start from 16, so only 5 lines are displayed on first Customer B page, which is unexpected. Is there any possible way to achieve expected result?

Pic:Big matrix nest with small matrix

Pic:limit rows per page

1
reporting-services

1 Answers

1
votes

The problem is your second condition on the grouping:

=Ceiling(Rownumber(Nothing)/10)

This groups and thus triggers a page break on every 10th line, regardless of the customer that the lines are for. In your example, Customer C would only have 7 lines on their first page because 23 have been printed so far in total, 15 for A and 8 for B.

Hopefully your dataset is based on Sql because we can calculate the group page number within the query result using some built-in features Row_Number, Over, and Partition:

SELECT Floor((Row_Number() OVER(partition by CustomerName ORDER BY CustomerName, ItemNumber)-1) / 10) AS GroupPageNumber, 
  CustomerName, ItemNumber
FROM Items 
ORDER BY CustomerName, ItemNumber

This calculates the group page number with a maximum number of 10 items per page, so now you just have your second grouping expression as simply =Fields!GroupPageNumber.Value.