Skip to Content
avatar image
Former Member

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?

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

10 Answers

  • Best Answer
    avatar image
    Former Member
    Oct 06, 2010 at 07:46 PM

    On what label means??

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

    Thank You!!

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      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

  • avatar image
    Former Member
    Nov 03, 2010 at 10:35 PM

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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • avatar image
    Former Member
    Nov 03, 2010 at 10:37 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Nov 17, 2010 at 10:10 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • avatar image
    Former Member
    Nov 17, 2010 at 10:40 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • avatar image
    Former Member
    Nov 17, 2010 at 10:54 PM

    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!

    😊

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • avatar image
    Former Member
    Nov 17, 2010 at 11:43 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • avatar image
    Former Member
    Nov 18, 2010 at 12:27 AM

    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!! 😊

    Add comment
    10|10000 characters needed characters exceeded

    • 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#=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

  • avatar image
    Former Member
    Nov 18, 2010 at 04:54 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Nov 18, 2010 at 09:11 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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