SSRS report to print labels from a roll

Does anyone have any experience of creating a report that will print labels that are on a roll?

I can see plenty of examples for a sheet of labels, but nothing with any great detail regarding a roll.

Would the best method be to set the report page up to have 1 column and then adjust the size of the page to be the size of the label?

There will be different info on each label, and a varying number of labels per print.

So basically set up the report to mirror the label size and then repeat pages depending on how many labels need printing?

1 Answer

You should just be able to set your report to have the same page size as your label then design the report as normal.

As for producing multiple labels, you can either

1. have a single dataset containing all the info you need for all labels and group by whatever makes each label unique (e.g. a label number). Then add a page break on the row group property to put a break between each, or ..
2. you could create a subreport that just handles a single label and then have a master report that with a table control with a single 'cell' and the subreport inside that cell. You would then pass the parameters to the subreport from the main dataset. I think you've seen a similar answer I posted about printing sheets of labels, if you follow that then it should get you most of the way there.

Option 1 is probably the simplest method...Step-by-Step for option 1In this example I've used the Microsoft sample database WideWorldImporters, just so I could get some names and addresses.

Step 1: Write your query to get your data. In my example I used the following..

SELECT top 10 o.OrderID, c.CustomerName, c.DeliveryAddressLine1, c.DeliveryAddressLine2, cty.CityName, c.DeliveryPostalCode FROM [Sales].[Orders] o JOIN Sales.Customers c on o.CustomerID = c.CustomerID JOIN Application.Cities cty on c.DeliveryCityID = cty.CityID
ORDER BY OrderID

This just gives me the order number, company name and address.

Step 2: Create a new report. I'm using Visual Studio but the process is almost identical in Report Builder if you use that.

• Create a new, blank report
• Add a datasource and dataset containing your query from step 1
• Select the 'body' and set the size property to your label size. I used 100mm, 40mm
• Select the 'report' and set the PageSize property to the same values as above and the margins to 0

Step 3: Add a table to contain the data

• Add the new table
• Delete the header row
• Delete the last column (I have a two column label in my example but obviously up to you)
• Select the table (tablix) using the grey table handle (top left of the table) and then set the dataset property to the name of the dataset you created earlier
• Add enough rows to contain all your data, in my case 6 in total
• In the RowGroups panel under the main report design, right-click the details rowgroup and choose Add Group => Parent Group. Select OrderID as the field to group on and click OK
• Remove the newly added column and if prompted choose 'remove column only'
• Right-Click the (OrderID) Row group and go to Group properties, click Page Break and choose 'between'
• Set each row to contain your data until you end up with something like....

Clean up the formatting and that should be it. One label per page and each page set to your label size.

You may need to adjust the layout slightly to adjust for margins etc but this should give you a good start.