SSRS Report with groups and page breaks

Question

I am trying to write an SSRS report where the output is grouped by team, with each team starting on a new page, and on a new tab when the report is output to Excel. I want the column headers to repeat each time there is a new team, and at the top of each page if one team's data spans more than one page.

My test data is generated by the following code:

WITH
team_list(team_id,team) AS
(
SELECT 1, 'red'
UNION ALL SELECT 2, 'orange'
UNION ALL SELECT 3, 'yellow'
UNION ALL SELECT 4, 'green'
),
results1(result_value1) AS
(
SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
),
results2(result_value2) AS
(
SELECT 7*result_value1
FROM results1
),
main_query(id, team, value1, value2) AS
(
SELECT
  tl.team_id
, tl.team
, r1.result_value1+20
, r2.result_value2
FROM
  team_list tl CROSS JOIN results1 r1 CROSS JOIN results2 r2
WHERE
  r1.result_value1 < tl.team_id+2
)
SELECT * FROM main_query

I want the report to look like this:

I inserted a Tablix with three columns: id, value1 and value2. I right-clicked on Details under "Row Groups", and did Add Group>Parent Group, Group by Team. I did not tick either "Add group header" or "Add group footer".

I clicked on Team under "Row Groups". Then in the Properties Window, which was showing "Tablix Member", I expanded Group and then Page Break, and set Page Break to Between. I also set PageName to Fields!team.Value.

To get the column headers to repeat, I clicked on the down arrow next to "Column Groups" and chose Advanced Mode. I clicked on the second occurrence of Static under "Row Groups" and in the Properties pane, set RepeatOnNewPage to True. I now had a report with a page break between teams, and it repeats the column headers on every page. When I exported it I got a tab for every team with the team name on it. Good.

Now, I do not want the first column of the table, Team. So I right-clicked the column and chose Delete Columns, then Delete Columns Only. I saw that under "Row Groups" the top "Static" had disappeared. My report no longer repeated the column headers on every page.

I tried hiding the two cells in the first column of the table, and this seemed to give the desired effect, apart from indenting the table to the right. I tried to set the width of this column to zero, which does not seem to be possible. When I exported the report I got a very narrow Column A. Is there any way of getting rid of this column altogether, while still having repeating column headers?

For my team heading, I inserted a row above the top row of the Tablix. I merged the three cells above my column headings. I right-clicked and added the expression ="Team " & Fields!team.Value. This displayed "Team red" for all the teams, instead of changing on each page. How can I make a heading row that will show the correct team name for each group?

Alan Schofield Alan Schofield · Accepted Answer · 2018-05-01T20:49:50

You need an extra row in the team group. The easiest way is to do this is to add a header when you added your team group.

So in the 1st paragraph of your step-by-step.. from Details to Add Group -> Parent Group, select Team and check the add header option. Double-click the new Team row group in the row group panel and set page breaks to between

Now you will have three rows in the table, ignore the fact that rows 2 and three are merged in column 1, we'll get rid of that once everything else is done. Right-Click on one of the cells in row 2 (e.g. column 2 row 2) and choose Insert Row -> Inside Group - Above.

the table will look something like this..

Next copy the column hedaers (id, value1 and value2) to hte row direclty above the cells with the actual data in (should be row 3). Then in row 2 select the three cells above your data column and merge them. Select team as the field for this merged cell (or your expression)

It should now look something like..

Now delete the first row and first column as we don't need these anymore.

Next click the advanced mode from the drop down next to the column groups as you did before.

Select the two static items in the ROW GROUPS and set the "RepeatOnNewPage" to true.

Finally, for testing, I set the interactive size height property of the report to 10cm (so I could force some page breaks before then end of each group).

After following this, the report worked as expected, below you can see the 2nd page for yellow has the correct header.

In case it's useful, I used Report Builder 2016 to build the sample RDL. You can get hold of it here, you'll just need to edit the connection properties to get it to run.

Link to GroupHeaderRepeat.rdl

https://1drv.ms/u/s!Al1Kq21dFT1ijb5Qmm2P4zyM1MV4pA

SSRS Report with groups and page breaks

1 Answers