How can I use grouping and page breaks to create a 2-sided SSRS badge report? (Microsoft SQL Server 2012 Report Builder)

Question

I'm designing an SSRS badge report for Avery 5392 badge stock (6 per page) that will also print each wearer's record ID on the back of their badge.

I've set up RowNumber & RowMod columns in my SELECT statement so that I can filter into my design elements only 3 rows of data per column and control which rows go on the left or right-side report elements, respectively. I've also set up PageNumber as a possible grouping option for page breaks.

Please consider this dummy table as a proxy for the data I'm actually using:

CREATE TABLE #Badges (ID INT PRIMARY KEY, Name VARCHAR(50))

INSERT INTO #Badges VALUES
  (100001, 'Anna')
, (100002, 'Bart')
, (100003, 'Cathy')
, (100004, 'Daniel')
, (100005, 'Ericka')
, (100006, 'Fred')
, (100007, 'Gwen')
, (100008, 'Harry')
, (100009, 'Idita')
, (100010, 'Joshi')
, (100011, 'Katie')
, (100012, 'Leo')
, (100013, 'Manuela')
, (100014, 'Nando')
, (100015, 'Olga')
, (100016, 'Park')
, (100017, 'Quang')
, (100018, 'Rhys')
, (100019, 'Sarina')
, (100020, 'Theo')
, (100021, 'Udeyume')
, (100022, 'Victor')
, (100023, 'Wynona')
, (100024, 'Xavier');

SELECT ((ROW_NUMBER() OVER (ORDER BY a.RowNumber) + 5) / 6) AS PageNumber
, a.*

FROM (SELECT ROW_NUMBER() OVER (ORDER BY b.Name) AS RowNumber
, ROW_NUMBER() OVER (ORDER BY b.Name) % 6 AS RowMod
, b.*

FROM #Badges AS b) AS a

DROP TABLE #Badges

I have a 4-square of separate report elements (using "Lists" at the moment) that looks a bit like this...

| List 1 | List 2 |

| List 4 | List 3 |

I need my report elements to display the first 3 rows of Name in the left column (List 1), then the next 3 rows of Name in the right column (List 2). Then, on the following page, I need to flip the Mod orientation using the same data to display the first 3 rows of ID in the right column (List 3), then the next 3 rows of ID in the left column (List 4).

Page 1 would look like this...

Col 1 (List 1)	Col 2 (List 2)
Anna	Daniel
Bart	Ericka
Cathy	Fred

... and page 2 would look like this...

Col 1 (List 4)	Col 2 (List 3)
100004	100001
100005	100002
100006	100003

Then I want to repeat that process on page 3 and so on.

user_1775389 · Answer

I've taken your sample data and reworked the query to give the data I would use if I need to do this. It may not suit your setup exactly but it should be close enough to revise.

I basically output page numbers and row and column numbers from the dataset query that we can then use in a Matrix control.

Here's the modified query..

DECLARE @Badges TABLE(ID INT PRIMARY KEY, Name VARCHAR(50))

INSERT INTO @Badges VALUES
  (100001, 'Anna')
, (100002, 'Bart')
, (100003, 'Cathy')
, (100004, 'Daniel')
, (100005, 'Ericka')
, (100006, 'Fred')
, (100007, 'Gwen')
, (100008, 'Harry')
, (100009, 'Idita')
, (100010, 'Joshi')
, (100011, 'Katie')
, (100012, 'Leo')
, (100013, 'Manuela')
, (100014, 'Nando')
, (100015, 'Olga')
, (100016, 'Park')
, (100017, 'Quang')
, (100018, 'Rhys')
, (100019, 'Sarina')
, (100020, 'Theo')
, (100021, 'Udeyume')
, (100022, 'Victor')
, (100023, 'Wynona')
, (100024, 'Xavier');

WITH t (rN, PageN, RowN, ColN, ID, Name)
AS (
SELECT 
          ROW_NUMBER() OVER (ORDER BY b.Name) AS rN
        , ((ROW_NUMBER() OVER (ORDER BY b.Name) - 1) / 6) +1 AS PageN
        , ((ROW_NUMBER() OVER (ORDER BY b.Name) - 1) % 3) + 1 AS RowN
        , ((ROW_NUMBER() OVER (ORDER BY b.Name) - 1) /3) % 2 AS ColN
        , b.*
    FROM @Badges AS b
) 

SELECT PageN, 1 AS OddEven, RowN, ColN, [Name] as  Caption from t 
UNION 
SELECT PageN, 2, RowN, CASE ColN WHEN 1 THEN 0 ELSE 1 END, CAST([ID] as varchar(50)) as  Caption from t ;

This give the following results (not all shown)

I created a new report and added a Matrix. I dragged the RowN field to the rows placeholder, ColN to col placeholder and Caption to the data placeholder. I then added a parent group grouped by PageN and OddEven. In the group properties for the "PageN" Row Group I set the sort to both PageN and OddEven and set a pagebreak between each instance.

For illustration I've left a few rows and column in that are not required, I also added a column inside the column group (the grey cell) to act as padding which might be useful for aligning things.

The report design looks like this

The final output looks like this

Page 1:

Page 2:

Page 3:

Page 4:

and so on...

Once the redundant rows and columns are removed, all pages should appear in the same position on each page.

How can I use grouping and page breaks to create a 2-sided SSRS badge report? (Microsoft SQL Server 2012 Report Builder)

1 Answers