Printing Labels

[Former Member]

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 · Accepted solution]

On what label means??

You can see data for vendor Name & Address at CR report, right??

Thank You!!

[Former Member · Accepted solution]

I wrote a Crystal Report for printing labels using the Mailing Label Report Wizard & I selected the label template Avery Label 5162. I see the data no problem, but I can't find a way to tell Crystal Reports what label number to print on.

Edited by: Susan Cardoza on Oct 6, 2010 9:57 PM

[Former Member · Accepted solution]

Susan,

This is one of those situations where its easier to show you than to tell you. So take a look at this... [Label Printing.rpt|https://docs.google.com/leaf?id=0B_0KY03Gs2knMWUwZTc2YTEtZDU3ZS00MTZlLWJhYTUtNjEyY2QyZGFlOTNj&hl=en].

It's based off of the sample AdventureWorks database that comes with SQL Server 2005 and it relies on a SQL Command to create dummy records and to pull in your data. The idea is that the user will use a parameter to choose the starting label position.

If you're not familiar with SQL or the use of Commands, and you have no idea how to reverse engineer the sample just let me know. I'll need to know why type of database you're using and I'd need you to post the SQL query the CR is using to pull your data currently. Just go to Database > Show SQL Query and copy the contents of that window.

HTH,

Jason

[Former Member · Accepted solution]

Jason,

That is exactly what I want. Here's my SQL statement.

SELECT "APVEN"."VENDNAME", "APVEN"."TEXTSTRE1", "APVEN"."TEXTSTRE2", "APVEN"."NAMECITY", "APVEN"."CODESTTE", "APVEN"."CODEPSTL", "APVEN"."VENDORID"

FROM "AGDATA"."dbo"."APVEN" "APVEN"

WHERE "APVEN"."VENDORID"='1041'

I have a parameter set up to select which vendor I need the label for, which is why in the SQL statement you see a specific vendor. I don't know if that is relevent or not.

[Former Member · Accepted solution]

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

[Former Member]

Hi Jason,

Sorry, you misunderstood my previous post. My confusion was where in the SQL code to add the Begin and End parameter information.

Thanks for the additional code. Copied it to my Command and added the BeginID and EndID parameters to the Parameter List within the Command. However, after I accept the data and view the SQL code again, my BeginID and EndID parameters are not saved. I can re-add them without error, but I cannot get them to save.

Also, I cannot get any vendor address information to display. The report does not return any errors, just no data. I've tried using just one vendor code in the Begin and End fields, but again, nothing.

Thank you,

Denice

[Former Member]

You need to make sure all of the parameters are added to the parameter list at the right of the Add Command window.

As far as no addresses showing up... If there are problems with the vernor ID parameters, there's a good chance that the query isn't pulling any data. Check the number of records being returned. I'm guessing that it's 0.

Check my latest sample report as a reference.

HTH,

Jason

[Former Member]

Hi Jason,

I think we are close... Below is my SQL text:

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 CAST(Num AS VarChar(12)) BETWEEN '' AND '', NULL AS VENDNAME, NULL AS TEXTSTRE1, NULL AS TEXTSTRE2, NULL AS NAMECITY, NULL AS CODESTTE, NULL AS CODEPSTL, NULL AS CODECTRY FROM #Temp UNION ALL SELECT "APVEN"."VENDORID", "APVEN"."VENDNAME", "APVEN"."TEXTSTRE1", "APVEN"."TEXTSTRE2", "APVEN"."NAMECITY", "APVEN"."CODESTTE", "APVEN"."CODEPSTL", "APVEN"."CODECTRY" FROM "AGDATA"."dbo"."APVEN" "APVEN" WHERE "APVEN"."VENDORID" BETWEEN '' AND ''

I'm getting a syntax error around the BETWEEN command. (Failed to retrieve data from the database. Details 42000: Incorrect syntax near the keyword 'BETWEEN'. Database Vendor Code 156.)

Not sure if the spaces made a difference, so I changed the formulas to match your example. The BeginID and EndID parameters are both identified Strings.

Thank you,

Denice

[Former Member]

Hi Jason,

Pretty clear, but a couple of questions....

Step 1) Sorry, I'm not sure how (or where) to add them to my SQL Code. Do I replace the Vendor parameter with Beginning Vendor Code and Ending Vendor Code? If so, not sure of the syntax for "vendor code between these 2 values".

The rest of the steps are okay. However, when I refresh the report, I'm prompted with the RangeParameter field as well as the Beginning Vendor and Ending Vendor fields. So, in total, I have Start Label Position, Vendor, Beginning Vendor Number, Ending Vendor Number and Range Parameter prompts.

Not sure how to remove Vendor and Range Parameter prompts and replace them with the Beginning/Ending Vendor Number.

Your help is greatly appreciated!!

[Former Member]

I'm headed to dinner. If I can make it back on tonight I'll try to fill in the gaps. If not I'll be back tomorrow.

Jason

[Former Member]

If you read through this post, you'll see that Susan's has built this report using a Command. It's the SQL in the Command that pulls in the data. To edit it, simply open the Database Expert > find the command > right click it and choose edit.

I did do a little testing and the BETWEEN function does work with text fields (at least in SQL Server) So I should work just fine.

I went ahead and knocked out another sample/demo report that you can use to reference.

I had a little trouble getting a main report, built with the label wizard, to work correctly as a sub-report. So I did have to tweak the label dimensions in the lay out. I don't have those specific labels to test on, but you should be able to get things to fit.

This label report is based on the AdventureWorks database that comes with SQL Server 2005...

[Label Shell 3.rpt|https://docs.google.com/leaf?id=0B_0KY03Gs2knYTRlNzdkOGQtOTI2Ny00N2EyLTg5ODItNTA2NDNlY2NmODM0&sort=name&layout=list&num=50]

As for the error your getting... You butchered the lines above "UNION ALL" try using this...

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 CAST(Num AS VarChar(12)) AS VENDORID, NULL AS VENDNAME, NULL AS TEXTSTRE1, 
NULL AS TEXTSTRE2, NULL AS NAMECITY, NULL AS CODESTTE, NULL AS CODEPSTL, 
NULL AS CODECTRY FROM #Temp
UNION ALL
SELECT
"APVEN"."VENDORID",
"APVEN"."VENDNAME",
"APVEN"."TEXTSTRE1",
"APVEN"."TEXTSTRE2",
"APVEN"."NAMECITY",
"APVEN"."CODESTTE",
"APVEN"."CODEPSTL",
"APVEN"."CODECTRY"
FROM "AGDATA"."dbo"."APVEN" "APVEN"
WHERE "APVEN"."VENDORID" BETWEEN '{BeginID}' AND '{EndID}'

HTH,

Jason

[Former Member]

Not sure how (or where) to add the new Parameter fields to the SQL code. Sorry... I'll need a little more direction on this part.

I added a RangeParameter to the new report, and the formulas. Added the sub-report, linked to parameters in the main report. However when I refresh, I'm prompted with the original "Vendor" field, the new Beginning and End parameter fields, and the RangeParameter field. Ideally, I'd like to be prompted with 3 fields: Start Label Position, Beginning Vendor Number and Ending Vendor Number.

I appreciate your help....

Denice

[Former Member]

Ok... Now I see what you're trying to do...

1st thing... The VendorID is a text data type which leads me to believe that there may be alpha characters in the ids. If that's the case, I'd caution you about trying to use range values at all for a variety of reasons.

But for the sake of keeping things moving I'll assume that it's either all numeric data stored in a text field or you've already figured out how to work around the alpha characters in your SQL.

So...

First thing first. Deal with the original Vendor parameter. Aka get rid of it. It was there to select a single VendorID... Not a range.

Next check your SQL and make sure it's coded properly to handle the begin and end parameter values.

Something like

WHERE CAST(VendorID AS INT) BETWEEN {?BeginID} AND {?EndID}

Now get back in and check your sub-report links. I'd be willing to bet you currently have the formulas linked to those goofy parameter looking things that CR generates automatically and offers as a default... Make sure the correct formulas are linked to the correct parameters.

Check your parameter types. In the sample where clause above, I cast VendorID as a number. If you do the same, be sure to update your parameter types accordingly.

Also... If you're going to keep everything as text, make sure to wrap the parameters in single quotes in the SQL

WHERE VendorID BETWEEN '{?BeginID}' AND '{?EndID}'

(I've never actually tried to use BETWEEN on a text field so I don't even know if it works...)

If all is done correctly, you should be left with the 1 Start Label Position parameter and 1 range parameter (with two input fields).

HTH,

Jason

[Former Member]

Hi Jason,

We, too, are still using CR XI R2. Yes, a start and end value would be fine. That's how we resolve many of our range of values. I'm up for the task!

[Former Member]

In that case here's what you want to do...

#1) Start by adding the 2 begin and end parameters to your report, same as you would normally and be sure to add them to your SQL code as well and save the report.

#2) Open a new blank report with no data source... It's just going to act as an "outer shell" for your current report.

#3) Add a single range parameter to this report.

#4) Create 2 formulas...

Minimum({?RangeParameter})

and

Maximum({?RangeParameter})

#5) Add your main report, the one you saved in step #1, as a sub-report to the outer shell report.

#6) Right click the sub-report and choose "Change Subreport Links..."

#7) In the "Available Fields:" window, find the 1st formula you created and move it over to the "Field(s) to link to:" window.

#8) Step #7 will activate a new area below the original windows... Find the 1st corresponding parameter that you created in step #1 and select it.

#9) Repeat steps 7 & 8 for the second formula/parameter combo

If you've done everything correctly, the outer shell report will accept a set of range parameter values and pass them to the 2 parameters in the sub-report. The end user will never see the 2 parameters in the sub-report.

Good luck let me know if you have any problems,

Jason

[Former Member]

Hi Jason,

Worked perfectly!! I neglected to change my variable type to String.

Next issue I have is allowing a range of values. Would this be possible within the SQL text?

Thanks for your help,

Denice

[Former Member]

I want to say no... at least not directly... I do know that CR 2008 introduced the ability to add a multi-valued parameter directly into the SQL of a Command, but I don't think it did anything for range parameters. (I'm still using CR XI R2)

That's not to say it CAN'T be done, it's just not all that easy... Unless you really have a need to use a range parameter or you just feel a burning need to take on the challenge, you're better off just using two single value parameters (one for the beginning and another for the end).

If you're still up for the challenge, I can tell you how to do it. Just let me know exactly what it is your trying to do.

Jason

[Former Member]

Hi Jason,

I'm working with Susan on this issue and I'm running into a problem with the conversion of the Numeric field to a String. When I change the text to "CAST (Num AS VarChar(12))" and execute the command, I receive the following error:

"This parameter is of type "Number" and can only contain a negative sign symbol, digits ("0-9"), digit grouping symbols or a decimal symbol. Please correct the entered parameter value."

Also, can we change the code to be a range of values for the vendor number?? For example, print the first label for vendor ABC123 and print a label for every vendor through and including MAK001.

Here's the current code:

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, NULL AS CODECTRY FROM #Temp

UNION ALL

SELECT

"APVEN"."VENDORID",

"APVEN"."VENDNAME",

"APVEN"."TEXTSTRE1",

"APVEN"."TEXTSTRE2",

"APVEN"."NAMECITY",

"APVEN"."CODESTTE",

"APVEN"."CODEPSTL",

"APVEN"."CODECTRY"

FROM "AGDATA"."dbo"."APVEN" "APVEN"

WHERE "APVEN"."VENDORID" = '{?Vendor}'

Thank you for your help....

Denice

[Former Member]

Try it like this...

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 CAST(Num AS VarChar(12)) AS VENDORID, NULL AS VENDNAME, NULL AS TEXTSTRE1, NULL AS TEXTSTRE2,
NULL AS NAMECITY, NULL AS CODESTTE, NULL AS CODEPSTL, NULL AS CODECTRY FROM #Temp
UNION ALL
SELECT
"APVEN"."VENDORID",
"APVEN"."VENDNAME",
"APVEN"."TEXTSTRE1",
"APVEN"."TEXTSTRE2",
"APVEN"."NAMECITY",
"APVEN"."CODESTTE",
"APVEN"."CODEPSTL",
"APVEN"."CODECTRY"
FROM "AGDATA"."dbo"."APVEN" "APVEN"
WHERE "APVEN"."VENDORID" = '{?Vendor}'

Make sure the Start Label Position parameter is set up as a number type and make sure that the Vendor parameter is text.

The error you're getting ("This parameter is of type "Number" and can only contain a negative sign symbol, digits ("0-9"), digit grouping symbols or a decimal symbol. Please correct the entered parameter value.")... is telling you that you've tried to enter non-numeric values into a numeric parameter...

In short, switch "Vendor" to a text type parameter and update the SQL to what I provided above and you should be good to go.

HTH,

Jason

[Former Member]

One last note, the vendor ID field is a string, not a number, I can't imagine that would cause this but it's the only thing different than what you advised me to do.

[Former Member]

Jason,

Here is our SQL command, but the label will only print in the upper left hand corner. Any idea as to what we are doing incorrectly? In answer to your previous question, yes the database is MS SQL using the accounting program Sage Accpac.

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}'

[Former Member]

Susan,

I'm not sure what you mean by "the label will only print in the upper left hand corner."

Some more detail about what's happening is going to be necessary.

As far as the VendorID being a text field and not a number... Make the following modification...

SELECT CAST(Num AS VarChar (20)) AS VENDORID, NULL AS VENDNAME, NULL AS TEXTSTRE1, NULL AS TEXTSTRE2, 
NULL AS NAMECITY, NULL AS CODESTTE, NULL AS CODEPSTL FROM #Temp

FYI... The VarChar(20) is just a guess, you should use the same field type that "APVEN"."VENDORID" is.

Just let me know about the details on that 1st part.

Jason