Question on Labels - Can A Starting Row and Column Be Specified?

[Former Member]

Hi. I have bar code labels on Avery 5262 stock. It has 2 columns and 6 rows. The default is to print in column 1, row 1.

We have a lot of wasted labels because once a partial sheet is used, it cannot be re-used for a new batch of labels. I know in Microsoft Word I can indicate that I want my labels to start in a different row or a different column. I have Crystal 11.5. What I want is for the user to specifiy a row and column so that the printing begins on that label. Can anyone please tell me how I might do this?

Thanks!

Debbie Erickson

Fastrak SoftWorks

[Former Member · Accepted solution]

Debbie,

Here are two sample reports I did awhile back regarding this exact same issue. Both sample reports use the SQL Server 2005 Adventureworks database as a back end.

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

&

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

Hope this helps,

Jason

[Former Member · Accepted solution]

Thank you Jason, This really helped. I downloaded your first project and looked at the code. Very clever! I never would have thought to add blank records at the top of the result set. You prompt a user for the starting label and compute the number of blank records you need. By using a crystal prompt, I can set what my invalid user responses are, too, so my computations don't go wacky. Me likeeee very much! LOL!!

Again, I really appreciate your time.

[Former Member · Accepted solution]

Haha! Glad you liked it... and were able to follow how it works. Not a lot of people would have been able to figure out how the SQL Command works without any additional explanation. Good job!

Best of Luck,

Jason

[Former Member]

Jason, I must be missing something from your example. Can you please explain how you initialized the parameter field? I tried using my new parameter fields in my commandtext.

My supervisor wants the user to be asked for row and column, so I must compute the actual starting position.

I see an error that states "no value given for one or more required parameters" when I click OK to close the database command.

I basically used your code, with a twist:

-

---

-- This 1st section is used to create a temp table that will

-- hold dummy values that will be needed to push the real

-- values down to the desired position

-

---

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL DROP TABLE #Temp

CREATE TABLE #Temp (Num INT)

DECLARE @x INT,

@STARTINGLABEL INT

SET @x = -1

if {?Start Row Label Position} = 1 then

SET @STARTINGLABEL = {?Start Column Label Position}

else

SET @STARTINGLABEL =

*(({?Start Row Label Position} - 1) * 2) +*

({?Start Column Label Position} - 1)

WHILE @x > (-1 * @startinglabel)

BEGIN

INSERT INTO #Temp(Num) Values(@x)

SET @x = @x - 1

END

SELECT NULL AS PartNo, NULL AS PartName, NULL AS Location, NULL AS ShelfBin, NULL AS StockRoom FROM #Temp

UNION ALL

I definitely have a default value defined for the parameters. How did you initialize yours? I don't see it anywhere on your report.

Thanks in advance for the additional support.

[Former Member]

Debbie,

#1)

To make a parameter available to a command, it must be added to the command's parameter list.

Open the sample report and open the command in edit mode. The pane, to right of the sql, is the parameter list...

#2)

That report was built using the Mailing Label Report Wizard which utilizes "Format with Multiple Column" property and is set to "Across then Down".

(From the Design view, right click the the Details section and choose "Section Expert" > You'll see the Format with Multiple Columns checked and grayed out. > You'll also see that a "Layout" tab has been added. This where you'll see the "Across then Down" property)

So basically there is no need to set both the column and row positions. It's done automatically. If 5 labels have been used on a sheet, just enter "6" to start in the 6th position.

If your boss truly is insisting that you make things harder than they need to be, loose the {?Start Label Position} parameter and add {?ColumnNumber} & {?RowNumber} parameters and rewrite the SQL to look like this...

---------------------------------------------------------------
-- This 1st section is used to create a temp table that will 
-- hold dummy values that will be needed to push the real    
-- values down to the desired position                       
---------------------------------------------------------------
IF OBJECT_ID('tempdb..#Temp') IS NOT NULL DROP TABLE #Temp
CREATE TABLE #Temp (Num INT)
DECLARE @col INT, @row INT @x INT
SET @col = {?ColumnNumber}
SET @row = {?RowNumber}
SET @x = -1
WHILE @x > (-1 * CASE WHEN @col = 1 THEN (2 * @row) -1 ELSE 2 * @row END)
BEGIN
	INSERT INTO #Temp(Num) Values(@x)
	SET @x = @x - 1
END
SELECT Num AS EmployeeID, NULL AS Name, NULL AS AddressLine1, NULL AS AddressLine2, 
NULL AS City, NULL AS State, NULL AS PostalCode FROM #Temp
UNION ALL

Just an FYI... the "*" isn't a wildcard in SQL Server. The "%" is the "any number of characters" wildcard in SQL.

Also... There is no IF THEN ELSE syntax in SQL Server. It's just IF ELSE and it's used mainly to control blocks of code. CASE expressions are much easier to use and are more flexible.

#3) As for default values. They can be set when defining the parameter in the design environment.

HTH,

Jason

[Former Member]

Jason,

This is exactly what I was looking to do.  I was able to download your Label Printing.rpt report but not the Label Shell 3.rpt.  Do you have that one available so I can get the whole picture?

Thanks,

Laura