SSRS - How can I fix the number of groups in each page

Problem:

I have a Report which displays 6-groups(5-Rows in each Group) of information. The report is having some filter which can be modified by user. When first time report get loads without any filter its page rendering in report viewer and Print page is perfect, But when filters been added in Report information its get unusual.

Could anyone please suggest anything to fix number of rows/groups in each page?


Solution:

I will provide two methods to solve the issue

(1) Try to prevent the text boxes from expanding

I will assume that you are showing the report parameters (filters) as mentioned in the following link:

• Include Report Parameter Selection Values in Report Output for SQL Server Reporting Services

Then, you should set CanGrow property to False from the Textbox properties pane as mentioned in folowwing article:

• Allow a Text Box to Grow or Shrink (Report Builder and SSRS)

Also make sure that Allow height to increase property is not checked in the Text Box Properties Dialog Box

Helpful links:

• How to prevent a textbox from expanding horizontally?
• Text Boxes (Report Builder and SSRS)

(2) Limit the number of rows per page

While searching on this issue, i found the following solution which can be used as a workaround to distribute rows per pages:

You can specify the limit number of rows per page with a conditional approach. If it is the first page then 10 (2 groups) as example, and for other pages 15 (3 groups).

You should follow these steps:

1. Go to Report >> Report Properties >> Code , in the Custom Code section, enter the following:

   Public Function PageNumber() as String
       Dim str as String
       str = Me.Report.Globals!PageNumber.ToString()
       Return str
   End Function
   
   Public Function TotalPages() as String
       Dim str as String
       str = Me.Report.Globals!TotalPages.ToString()
       Return str
   End Function

2. Now create your Group with Page Break as below SSRS expression:

   = IIf ( CInt(Code.PageNumber()) = 1, Ceiling((RowNumber(Nothing)) / 10), Ceiling((RowNumber(Nothing)) / 15) )

The result will be like the following image:

References and helpful links

• SSRS Limit Number of rows Per Page
• Display a fixed number of rows per page for an SSRS report
• Group Expression Examples (Report Builder and SSRS)
• Displaying Fixed Number of Rows per SSRS Report Page

(3) Another method

Another method to keep group rows on the same page is by setting the Keep together propery to true:

Row_Group >> Properties >> Keep together = True.

Original post: https://stackoverflow.com/questions/54511020/how-can-i-fix-the-number-of-groups-in-each-page-of-ssrs/54619768#54619768