Creating an Address Label Report

Owned by Former user (Deleted)
Last updated: Jun 22, 2010Version comment

It's easy to create address labels with Pentaho Report Designer.

1. Examine the labels you want to create

For our example, we'll use Avery 5160 Address Labels for Laser Printers. These labels are 2-5/8" wide x 1" high and configured as 3 columns of 10 labels in Standard Letter size (8.5" x 11") portrait orientation.

When converting inches to points, 1"= 72 points. Page size is 612 points wide x 792 points high and labels are 189 points wide x 72 points high.

Multiplying 3 three labels across by 189 points yields 567 points, and subtracting that from the 612 point width of the paper leaves 45 points to be divided between the left and right margins, and taking 10 x 72, or 720 points from 792 leaves 72 points to be divided between the top and bottom margins. These measurements will be the first cut and may require some fine tuning later.

2. Select your data.

For our sample, let's use the Customers file from the SampleData database that is distributed with Pentaho BI and this query:

SELECT    "CUSTOMERS"."CUSTOMERNAME"
,         "CUSTOMERS"."ADDRESSLINE1"
,         "CUSTOMERS"."CITY"
,         "CUSTOMERS"."STATE"
,         "CUSTOMERS"."POSTALCODE"
FROM      "CUSTOMERS"
WHERE     "CUSTOMERS"."COUNTRY" = 'USA'
ORDER BY  "CUSTOMERS"."POSTALCODE"
,         "CUSTOMERS"."CUSTOMERNAME"

3. Lay out the report.

Although intuitively we would just want to lay out 3 sets of name and address fields, there is no way that I know of to force the detail band to get 3 records at a time. This means we'll have to use sub-reports to represent the columns and somehow manipulate the data to print only what ought to be in the column the sub-report occupies.

Select sub-report from the design pallet and drag one and only one sub-report to a band that prints only once per report. For this example we'll drag the sub-report to the Report Header band. We'll take care of the other sub-reports later. The insert-sub-report dialog will ask whether you want to insert an inline or banded sub-report. Since we want the sub-reports to print side-by-side, we'll select inline. Go to Format->Size and Borders (the keyboard shortcut is Ctrl-1) and set width to 189 and height to 72 to match the size of the labels. While you're in the Format dialog you can also alter the font settings if you wish.

Double-click on the sub-report and it will take you to the sub-report design canvas. To make data available to the sub-report, Select Data->Data Sets->Inherited-Data-Factories, then traverse the tree to the query we defined earlier and right-click and acknowledge "select query".

Before we start dragging data to the canvas, let's define a function to put city, state, and postal code (zip) together in the USA format. Select Data->Add Function->Common->Open Formula and insert the formula:

 = [CITY] & ", "& [STATE] & " " & [POSTALCODE]

Set the function name to LASTLINE.

Now we can start to place data, so drag CUSTOMERNAME, ADDRESS1, and LASTLINE to the detail band. We want these fields to be the same size and aligned, so select all 3 of them by clicking on each while holding the shift key. Then, from the Structure tab, select Style->(size & position). Set their height to 14, their width to 185, and their x to 0.

Since we have 72 points of label height, and have 3 lines of size 14, we have 72-(3*14) or 30 points of space to use for margin, so let's set the y values at 15 for CUSTOMERNAME, 30 for ADDRESS1, and 45 for LASTLINE. Press the preview icon at the top left of the canvas and you can see that our label looks good, and that there are 10 labels printing per page. If you look closely, though, you'll see that every single record is printing in the first column, and we need this sub-report to print only the 1st, 4th, 7th and so on.

4. Create filtering function

If we know the position of the record in the result set, we can determine which column it belongs in by using the modulo function. We can use a report function to count records, so let's add a Running Count Function and name it R0.

With this information, we're able to set a formula for the Details band Style->(size & position)-> visible that says =mod([R0];3)=1. That would be sufficient for the current report and column, but by defining some functions we make the filters more visible and easy-to-modify for later.

Add 3 Open Formula Functions to the report, Columns with the formula =3 to indicate this is a 3 column report, MyColumn with the formula =1 to indicate this sub-report prints the first column, and PrintIt with the formula =mod([R0];[Columns])=[MyColumn].

Now, set the Details band Style->(size & position)-> visible property to =[PrintIt].

Preview your report, and you'll find that you will see that you are getting only the records that should be printing in column 1.

If you'd like to add a logo or other embellishment to your sub-report, now would be a good time to do it.

5. Replicate your sub-report and adjust MyColumn

Return to the main report canvas and copy your sub-report using control-c, then paste it into the space for column 2 (x=190) and column 3 (x=380). If you preview now, you'll see they all print the column 1 data. To fix that, you have to go in and modify the MyColumn field. MyColumn for 2 is =2, and MyColumn for 3 is =0 because that is what 3 mod 3 returns.

Now you're ready to test and fine tune your report.

That's all there is to it.