SSRS Break after n occurrences of group

Question

I am trying to group an SSRS report so that each page breaks naturally around the 11 inch mark.

It looks as though you cannot dynamically set the Interactive Height property so I have set it to 0 and am applying page breaks to the dataset.

I am making the assumption that roughly every 3 users will take up about a full page. I want to break after every 3 EmployeeID groupings. I have only seen breaks which consider the RowCount, which is inconsistent per user in my dataset. A user can have 1 or Many rows.

How can I break after 3 occurences of an SSRS sub group?

Here is an image of the current grouping I have:

I need the Header (title, Period and blank line) to appear on every new page. On every page I want to take up as much of the 11 inches as possible, but I am willing to settle on simply stating that we will break after every 3 occurrences of the EmployeeID group.

The EmployeeID2 group may not be necessary, it was implemented when I was experimenting with RowNumber breaks.

There are two possible solutions to my issue as I see it:

Somebody tells me how to set a parameterized Page Break every 11 inches
Somebody tells me how to set a parameterized Page Break every n occurrences of an SSRS subgroup

Finally, additional clarification:

I need a parameter which toggles PageBreak. If the PageBreak Parameter is set to False, the Header will appear only once and all Employees will display as with InterActive Height = 0. If the PageBreak Parameter is set to True, the Header will appear at the top of every new page, and every page will contain 3 Employees.

I tried implementing a Grouping field in my Stored Procedure which assigns every 3 Employees an incrementing Group ID using the code below, however this query which runs in 3 seconds is now freezing SSRS, so I am hoping to achieve the grouping using SSRS functions.

TSQL Grouping Logic:

,EmpRank AS (
    SELECT DISTINCT
        EmployeeID
        ,DENSE_RANK() OVER(ORDER BY FormattedName) AS 'EmpCounter'
    FROM FilteredData
)

,EmpGrouping AS (
    SELECT
        EmployeeID
        ,((EmpCounter -1 ) / 3) AS 'EmpGroup'
    FROM EmpRank
    GROUP BY ((EmpCounter - 1) / 3)
        ,EmployeeID
)

SELECT
    FilteredData.*
    ,EmpGrouping.EmpGroup
FROM FilteredData
    LEFT OUTER JOIN EmpGrouping
        ON EmpGrouping.EmployeeID = FilteredData.EmployeeID
ORDER BY EmpGroup, EmployeeID

Thank you in advance

please change that image link to inline image. most viewer over here prefer that. thanks — Kashif Qureshi, Nov 29 '17 at 15:29
@KashifQureshi You can do that yourself, but there's no need now :) — Reinstate Monica Cellio, Nov 29 '17 at 15:32
@Archer lol, I know, usually i do. But Today I am not in mood — Kashif Qureshi, Nov 29 '17 at 15:35

score 1 · Answer 1 · answered Nov 30 '17 at 13:51

The TSQL grouping logic worked in the end. Visual Studio had been crashing when I tried that grouping yesterday, but today its not...

In any case, the solution was to create a new field which grouped the employees as I wanted, and then I grouped upon that within SSRS. I still do not know how to group based on page height but at least I was able to group based on occurrences of the sub group

SSRS Break after n occurrences of group

1 Answers1