Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Printing Labels

I want to be able to tell Crystal Reports on what label it should print name & address information from a MS SQL database. I am just pulling vendor name & address fields onto the Avery Label 5162, which is one of the default Crystal Report label formats. Any ideas?

Former Member
Former Member replied

Susan,

I don't know if you have any experience working with Commands in CR so I'll step you through it. If you already are familiar with their use, just grab the code below and go to town...

1) Start a new report file. The changes you're making are radical enough to warrant a fresh start.

2) Open your data connection. You'll notice that it will say Add Command above the table names. Double click it. This will launch the Add Command To Report dialog box.

3) Copy the code I placed below and paste it in the larger window to the left.

4) Then, in the window to the right, click the Create... button to add a parameter.

4a) Create a parameter called Start Label Position and make the data type Number

4b) Create a parameter called Vendor and make the data type Number

    • Don't worry, you'll be able to customize the parameters to your liking from the Field Explorer once your through with this part.

5) At this point the command should be good to go. Click OK. CR will prompt you to enter values for the 2 parameters you built. If all goes well the window will close and it will show your data source name and the word Command under Selected Tables: in the Database Expert.

FYI: You didn't say what type of database you are using so this is written for SQL Server...

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL DROP TABLE #Temp
CREATE TABLE #Temp (Num INT)
DECLARE @x INT
SET @x = -1
WHILE @x > (-1 * {?Start Label Position})
BEGIN
	INSERT INTO #Temp(Num) Values(@x)
	SET @x = @x - 1
END
SELECT Num AS VENDORID, NULL AS VENDNAME, NULL AS TEXTSTRE1, NULL AS TEXTSTRE2, 
NULL AS NAMECITY, NULL AS CODESTTE, NULL AS CODEPSTL FROM #Temp
UNION ALL
SELECT 
"APVEN"."VENDORID",
"APVEN"."VENDNAME", 
"APVEN"."TEXTSTRE1", 
"APVEN"."TEXTSTRE2", 
"APVEN"."NAMECITY", 
"APVEN"."CODESTTE", 
"APVEN"."CODEPSTL"
FROM "AGDATA"."dbo"."APVEN" "APVEN"
WHERE "APVEN"."VENDORID" = '{?Vendor}'

Also... If you want to make the {?Vendor} parameter into a dynamic list of values...

1) Create a new Command object.

2) Paste in this code

SELECT "APVEN"."VENDORID", "APVEN"."VENDNAME" FROM "AGDATA"."dbo"."APVEN" "APVEN"

no need to worry about the parameter list this time..

3) Open the Vendor parameter from the field explorer and change the List of Values to Dynamic

4) Under Value, where is says, "Click here to add item"... Click it and look for VENDORID under Command_1

5) Then for the description, choose VENDNAME

I'm assuming that you already know how to get your data into 2 columns, having completed your 1st report... If not, you can look at my example report...

Right click the Details section > Choose Selection Expert > Place a check in box for Format multiple columns > Go to the Layout tab and enter the necessary settings.

If you get hung up on any of the steps just let me know,

Jason

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question