SSRS - 4 US Postcards per page

A user asked a question on SQL Server Central about how to build a report to print four double-sided US postcards per page.

Question Posed

I have to create an SSRS Report that will print as a USPS postcard, on both sides of a sheet of paper

The rectangular postcard must follow the dimensions below:

4-1/4 inches high x 5-1/2 inches long

The dimensions of the paper they will print on are:

8-1/2 inches high x 11 inches long

Four postcards can be printed on each 8-1/2 x 11 sheet of paper.

Solution

Assumptions

  • familiarity with building reports for SSRS
  • one textbox on the front and one textbox on the back of the postcard

New Report

Create an empty RDL File.

Report Properties

  • Interactive Size
    • set Width to 11in
    • set Height to 8.5in
  • Margins
    • set Left to 0in
    • set Right to 0in
    • set Top to 0in
    • set Bottom to 0in
  • Page Size
    • set Width to 11in
    • set Height to 8.5in

Body Properties

  • Size
    • set Width to 11in
    • set Height to 17in

Add a datasource

The query we will be using is self contained so for proof of concept purposes the data source can point to any SQL Server database.

Add a dataset

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

select
  PageNum,
  max([1]) as Q1Front,
  max([2]) as Q2Front,
  max([3]) as Q3Front,
  max([4]) as Q4Front,
  max([5]) as Q1Back,
  max([6]) as Q2Back,
  max([7]) as Q3Back,
  max([8]) as Q4Back
from (
  select 
    (x.Id / 4) + 1 as PageNum,
    (x.Id % 4) + 1 as QuadrantFront,
    (x.Id % 4) + 5 as QuadrantBack,
    x.Front, 
    x.Back
  from 
    ( 
      select
        postcard.Id,
        postcard.Front,
        postcard.Back 
      from 
        (values 
            (0,'0F','0B'), (1,'1F','1B'), (2,'2F','2B'), (3,'3F','3B'),
            (4,'4F','4B'), (5,'5F','5B'), (6,'6F','6B'), (7,'7F','7B')
        ) postcard(Id, Front, Back)  -- data to display in textboxes
    ) as x
  ) as src
pivot ( max(Front) for QuadrantFront in ([1],[2],[3],[4]) ) as front
pivot ( max(Back) for QuadrantBack in ([5],[6],[7],[8]) ) as back
group by
  PageNum;

Insert a Table

  • delete the header row
  • delete one column which leaves two columns
  • add three more rows inside the Details group giving a total of four rows
  • Location
    • set Left to 0in
    • set Top to 0in
  • for each table cell
    • Size
      • set Width to 5.5in
      • set Height to 4.25in

We now have a table with two columns and four rows with the individual cells being postcard size.

Assign data to table

Set the DataSetName for the table to the dataset that was just created.

Assign the values to the table as follows:
Q1Front Q2Front
Q3Front Q4Front
Q2Back Q1Back
Q4Back Q3Back

If the postcard needs multiple fields in the layout, add a rectangle or a subreport per table cell to accomodate the additional fields.

Printing

When printing to a printer, print on two sides, flip on short edge.

Sample Report

Postcard sample report RDL PDF