How to repeat tablix groups on a new page

Good Evening Fellow Readers,

The other day I learned a neat trick in SSRS (Sql Server Reporting Services) that I thought everyone should know.  I need to report group names on each page.  To better understand this I create a before and after screen shot.  Please keep in mind I quickly made this only to demo functionality not design of SSRS.

	Page 1	Page 2
Before
After

As you can see Il, Chicago, and Chicago White Soxes was not being populated on the next page.

Before I show you how I did this let's quickly create some data.

I create a simple store procedure to output a bunch of data.



CREATE PROCEDURE [dbo].[rpt1]
   -- Add the parameters for the stored procedure here AS
BEGIN
   -- SET NOCOUNT ON added to prevent extra result sets from
   -- interfering with SELECT statements.
   SET NOCOUNT ON;
   WITH temp1 (num,PlayerName, Team, City, State)
   AS(
   --Milwaukee Brewers
   SELECT 8,'Ryan Braun','Milwaukee Brewers','Milwaukee','WI'           UNION ALL
   SELECT 27,'Carlos Gomez','Milwaukee Brewers','Milwaukee','WI'        UNION ALL
   SELECT 9,'Jean Segura','Milwaukee Brewers','Milwaukee','WI'          UNION ALL
   SELECT 22,'Matt Garza','Milwaukee Brewers','Milwaukee','WI'          UNION ALL
   SELECT 7,'Mark Reynolds','Milwaukee Brewers','Milwaukee','WI'        UNION ALL
   SELECT 24,'Lyle Overbay','Milwaukee Brewers','Milwaukee','WI'        UNION ALL
   SELECT 23,'Rickie Weeks','Milwaukee Brewers','Milwaukee','WI'        UNION ALL
   SELECT 49,'Yovani Gallardo','Milwaukee Brewers','Milwaukee','WI' UNION ALL
   SELECT 12,'Martin Maldonado','Milwaukee Brewers','Milwaukee','WI'    UNION ALL
   --Atlanta Braves
   SELECT 5,'Freddie FreeMan','Atlanta Braves','Atlanta','GA'           UNION ALL
   SELECT 30,'Ervin Santna','Atlanta Braves','Atlanta','GA'         UNION ALL
   SELECT 24,'Evan Gattis','Atlanta Braves','Atlanta','GA'              UNION ALL
   SELECT 26,'Gan Uggla','Atlanta Braves','Atlanta','GA'                UNION ALL
   SELECT 22,'Jason Heyward','Atlanta Braves','Atlanta','GA'            UNION ALL
   SELECT 2,'B.J. Upton','Atlanta Braves','Atlanta','GA'                UNION ALL
   SELECT 46,'Craig Kimbrel','Atlanta Braves','Atlanta','GA'            UNION ALL
   SELECT 8,'Justin Upton','Atlanta Braves','Atlanta','GA'              UNION ALL
   --Chicago Cubs
   SELECT 29,'Jeff Samardzija','Chicago Cubs','Chicago','Il'            UNION ALL
   SELECT 13,'Starlin Castro','Chicago Cubs','Chicago','Il'         UNION ALL
   SELECT 44,'Anthony Rizzo','Chicago Cubs','Chicago','Il'              UNION ALL
   SELECT 64,'Emilio Bonifacio','Chicago Cubs','Chicago','Il'           UNION ALL
   SELECT 51,'Ryan Kalish','Chicago Cubs','Chicago','Il'                UNION ALL
   SELECT 11,'Kyuji Fujikawa','Chicago Cubs','Chicago','Il'         UNION ALL
   SELECT 15,'Darwin Barney','Chicago Cubs','Chicago','Il'              UNION ALL
   SELECT 68,'Jorge Solar','Chicago Cubs','Chicago','Il'                UNION ALL
   --Chicago White Soxes
   SELECT 79,'Jose Abreu','Chicago White Soxes','Chicago','Il'          UNION ALL
   SELECT 14,'Paul Konerko','Chicago White Soxes','Chicago','Il'        UNION ALL
   SELECT 49,'Chris Sale','Chicago White Soxes','Chicago','Il'          UNION ALL
   SELECT 32,'Adam Dunn','Chicago White Soxes','Chicago','Il'           UNION ALL
   SELECT 26,'Avisail Garcia','Chicago White Soxes','Chicago','Il'      UNION ALL
   SELECT 15,'Gordon Beckham','Chicago White Soxes','Chicago','Il'      UNION ALL
   SELECT 10,'Alexei Ramirez','Chicago White Soxes','Chicago','Il'      UNION ALL
   SELECT 1,'Adam Eaton','Chicago White Soxes','Chicago','Il'           --union all
  
   )SELECT  *  FROM temp1;
END
GO

Now the real fun comes into play.  You need to make sure you have all your group by rows created in SSRS.

First we have to enable advanced mode under column groups.  This was a hidden feature I had no idea was there at first.




After you have enabled advanced mode a few boxes show up under column groups as well as row groups.





Next you need to select each (static) row group and change the setting for 'ReapOnNewPage' to be true.

That is it!  Pretty simple, but it was confusing if you did not know about the advanced mode.


Let's see the Crew tie the series tonight with the Braves!

-Robert