Crystal Reports: Create blank labels on a sheet label report

	Resources
	Best sellers: ● cView ● Report Analyzer ● cViewSERVER ● ReCrystallize
	Crystal Reports: Free trial
	Articles: ● Administration ● Advanced ● Basic ● Crystal eNL ● Database ● Financial ● Problems Solved Books: ● CR Books ● Database Books ● Developer Books
	Tools: ● All CR Tools ● CR Analyzers ● CR Bestsellers ● DataBase Tools ● CR Graphics ● International ● CR Mail UFLs ● ReCrystallizePro ● CR Schedulers ● CR UFLs ● CR Viewers
	Add'l: ● About us ● Contact Us ● cViewSUITE Ppt ● Support
	Crystal Reports on Steroids

Crystal Reports: Create blank labels on a sheet label report

Crystal Reports can be a useful way to create mailing label reports. We have covered several techniques to make your label reports easier to design. But an interesting question arose at a client site.

They had been using Crystal Reports to print onto a sheet of labels. Being a thrifty client, they wanted to use the incomplete sheet the next time they printed their report.

Just a simple matter of generating some blank labels at the start of the report, we suggested. The next question was how to do this.

Stored procedures and a temporary working table were considered as solutions, but a simple report with a SQL command was the preferred solution.

In the screen shot below, we have used a border around the text box to help show the 4 blank labels at the start of the report.

To use this solution you need a Counter table with an integer field that we have called RecID.

In that table, have values from 1 to thenumber of labels on your sheet.

This table is used to generate the blank labels.

Click to Enlarge

Use a SQL command as your data source. Start by selecting the name and address fields from your address table. Then do a UNION all with the counter table to add your blank records.

A {?Blanks} number parameter can be used to select how many blank records you want at the top of the page.

The SQL Command needed for the report now looks like

Select [Customer Name] as Name,

Address1, Address2, City, 9999 as SEQ

FROM Customer

UNION ALL

SELECT NULL as Name, NULL as Address1, NULL as Address2,

NULL as City, RecID as SEQ

FROM Counter

WHERE RecID <= {?Blanks}

In the report, set the sort sequence to the SEQ field, and then whatever fields you want to use to control the sequence in which the labels print. This sorts the blank records to the top of the first page.

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.