SSRS: how to display 20 groups per page on SSRS report

Question

Requirement: I have used grouping on SSRS report 1 parent group and 2 child groups and I Need to display 20 parent level groups per page in report, irrespective of number of rows in child groups rows or parent group rows.

"SSRS Limiting groups per page on SSRS report"

Below how the Dataset and Grouping looks:

Now I need to Display 20 Student records in each page of Report (group StudentID).

Please let me know if anybody aware of solution/faced of this similar type of issue and found solution.

The Dataset's also includes the fields which are in the Row Groups.

The easiest way is to assign a sequential number to each parent group in your dataset query. Then you can add an outer group that groups by =CINT(Fields!myParentGroupNumber.Value/20). Then set a page break between each group. If this does not help, show a sample or your data as it is in your dataset query (not how it appears in your report). Then I will try to add a full answer. — Alan Schofield
Hi @AlanSchofield now I have Updated the Question with bit extra details. — Rudresh Kundur

Alan Schofield Alan Schofield · Accepted Answer · 2021-08-24T22:39:39

This is probably not the most optimised way of doing this, but without seeing your dataset query or stored proc this is the best I can do.

Assuming you dataset query is a simple SELECT and not a stored proc then you can do the steps below. If it is a stored proc, then you can either modify the proc to follow the steps below or dump the results of the stored proc into a temp table in the same way the existing query is shown below.

Taking a simplified version of your data, if you original query was SELECT StudentID, SubjectID, ClientName FROM myTable then we can change this to something like ....

DROP TABLE IF EXISTS #t 
CREATE TABLE #t (StudentID INT, SubjectID int, ClientName varchar(50))
INSERT INTO #t
    SELECT StudentID, SubjectID, ClientName FROM myTable -- <== YOUR ORIGNAL QUERY OR STORED PROC HERE

SELECT 
    , ((DENSE_RANK() OVER(ORDER BY StudentID)-1)/20) as myPageNumber
    , StudentID, SubjectID, ClientName 
    FROM #t

The result will give you your original data plus a column that calculates the correct page number (change the /20 if you want to change the number of unique StudentIDs on the page).

Now in the report, right click your outer row group (StudentID) and choose "Add Group => Parent Group", then just choose the myPageNumber field as the field to group by.

This will probably add an extra column to your report, you can delete this , but NOT the group , if prompted.

Finally, right-click your new group in the row group panel, go to "Properties", the "Page Breaks" and set "Between each instance of a group".

That should be it. This is done mainly from memory so it might not be perfect but hopefully should help you get what you want.

SSRS: how to display 20 groups per page on SSRS report

1 Answers