Crystal Reports - Mailing labels - Start printing on x,y label

[WaukMyWay]

Does anyone know if, in CR10, you can get two parameters for the x(row) and y(column) positions of labels to start printing at?
We all know we can print a bunch of labels on a sheet, but sometimes not all labels are used. Is it possible to start the next batch to print on the first sheet, but to start on a different label on the sheet?
If this is possible, where can I get a full example (especially if this requires arrays, with I have not worked with yet)?

 

[lbass]

Please see my suggestion in thread149-1421170.

-LB

 

[WaukMyWay]

It seems it will work, but I am not able to understand how it s all put together since I am still pretty much a beginner with some parts of Crystal. Therefore, I need a little more help with this. I am sorry, but the bits and pieces do not make much sense to me.

Is there anyway possible to get a complete example?

 

[lbass]

Since the thread gives you step by step instructions, you need to tell me what part you are having trouble with.

-LB

 

[WaukMyWay]

Sorry, but I cannot seem to understand how to put it all together. The thread does not really show step-by-step. This is a new thing for me to learn and the thread is confusing. Please help.

 

[lbass]

Okay, please copy your current SQL query into this thread (database->show SQL query).

-LB

 

[WaukMyWay]

Is this what you are looking for?

SELECT `Customer`.`Customer Name`, `Customer`.`Address1`, `Customer`.`Address2`, `Customer`.`City`, `Customer`.`Region`, `Customer`.`Postal Code`, `Customer`.`Country`
FROM `Customer` `Customer`
WHERE `Customer`.`Country`='USA'
ORDER BY `Customer`.`Customer Name`

 

[lbass]

Open a new label report->your datasource->above the tables, you will see "Add command". Click on this, and copy the following directly into the blank area.

SELECT `Customer`.`Customer Name`,
`Customer`.`Address1`,
`Customer`.`Address2`,
`Customer`.`City`,
`Customer`.`Region`,
`Customer`.`Postal Code`,
`Customer`.`Country`
FROM `Customer` `Customer`
WHERE `Customer`.`Country`='USA'
Union All
Select Null,Null,Null,Null,Null,Null,Null
From `Customer` `Customer`
Where {?LabelNo} >= 0
Order by 1

On the right hand side is where you will create the parameter {?LabelNo} by clicking on "Create", entering the name 'LabelNo' (no quotes), selecting number as the value type, and adding prompt text which says:

"Leave how many labels blank at the beginning?"

and then clicking OK.

Then click OK to close the command. Then proceed with creating the report.

After completing the label expert, go to report->sort records, and choose {Customer.Customer Name} as your sort field to force the nulls to sort first. Then go into the section expert and select each detail section->suppress blank section->x+2 and enter:

recordnumber > {?LabelNo}

-LB

 

[WaukMyWay]

I followed the instructions, but it does not work. All labels are printing when I put the number "4" in for the parameter.
What am I doing wrong?

 

[lbass]

You would have to recount exactly what you did. Let me guess though. The last step is using a conditional formula in the section expert->detail_a,_b,_c,_d,,_e, etc.->SUPPRESS BLANK SECTION->x+2. I wonder whether you entered the formula in the wrong formula area.

-LB

 

[WaukMyWay]

I really appreciate your help and am sorry for being such a bother. It still doesn't make any sense to me. Anyways, here is what I did:

I changed the test on the details a, b and c sections in the Suppress Blank Section to each read, "recordnumber >= (?LabelNo). The general Details section, in the New Page After, reads, "Remainder(RecordNumber,cdbl({?LabelNo})) <> 0:. The parameter in the report is set to 5. The labels are set to print across then down, 4 across and 20 down.

When I Preview, I get one label on each page numbers 1 through 4, then, page five is a littlt different...it contains two labels (record numbers 5 and 6) on labels one and two. This pattern repeats throughout.

What is the purpose of the parameter in the command? Would the command's parameter have a strange effect on the report's parameter if they were different? Is the paramater in the command really necessary?

 

[WaukMyWay]

I really appreciate your help and am sorry for being such a bother. It still doesn't make any sense to me. Anyways, here is what I did:

I changed the test on the details a, b and c sections in the Suppress Blank Section to each read, "recordnumber >= (?LabelNo). The general Details section, in the New Page After, reads, "Remainder(RecordNumber,cdbl({?LabelNo})) <> 0:. The parameter in the report is set to 5. The labels are set to print across then down, 4 across and 20 down.

When I Preview, I get one label on each page numbers 1 through 4, then, page five is a littlt different...it contains two labels (record numbers 5 and 6) on labels one and two. This pattern repeats throughout.

What is the purpose of the parameter in the command? Would the command's parameter have a strange effect on the report's parameter if they were different? Is the paramater in the command really necessary?

I need to be able to print a partial page of labels starting at a specific label number on the sheet, filling the rest of the sheet with other customer labels. If there are more records, it should print from the first label on the next sheet, so on and so forth.

 

[lbass]

First of all, there should BE no other parameters. Where would they come from? You were supposed to start a new report, using the command as your datasource--with no other tables. Also, you should not be using 'new page after' at all.

-LB

 

[WaukMyWay]

If I cannot have another parameter, than this will not help.

What I amt trying to really accomplish is printing inventory labels. The user is to select the part number range and also what label number to start printing at on the first page of labels.

Is this possible in Crystal Reports 10?

 

[lbass]

You need to build that parameter into the where clause of the command as well. You would create the parameter in the command and then reference it in the command. If the part number is a string, then the where clause would then be:

WHERE `Customer`.`Country`='USA' and
`table`.`partno` = '{?PartNo}'

I don't think you showed me your actual SQL query or we would have seen a part number in the query. Please display the actual query, if you would like more help.

-LB

 

[lbass]

PS. If you need to use a part number range, then you will need to add two parameters {?startrange} and {?endrange} and change the where clause to:

WHERE `Customer`.`Country`='USA' and
`table`.`partno` >= '{?startrange}' and
`table`.`partno` <= '{?endrange}'

...and yes, you can do this in CR10.

-LB

 

[WaukMyWay]

I started all over again and cannot make it work.
I followed your instructions above with the Command, sort, and suppressing blank sections x+2, yet it does not work.

Since I am unable to get this work, is there somewhere I can get a copy of a working report?

 

[lbass]

Not really. Why don't you post your actual SQL query and I will walk you through this. This method does work.

-LB

 

[lbass]

I just created a new mailing label report using this method. Note that the mailing label expert automatically checks "suppress blank section". You need to uncheck each of these to allow the nulls to display and then add the conditional formula to the formula area in order to suppress unwanted blank labels:

recordnumber >= {?LabelNo}

Be sure you are adding the formula to the suppress blank section area in the section expert and NOT the suppress area.

-LB

 

[WaukMyWay]

Here is a copy of the SQL Query:
SELECT `Customer`.`Customer Name`,
`Customer`.`Address1`,
`Customer`.`City`,
`Customer`.`Region`,
`Customer`.`Postal Code`,
`Customer`.`Country`
FROM `Customer` `Customer`
WHERE `Customer`.`Country`='USA'
Union All
Select Null,Null,Null,Null,Null,Null
From `Customer` `Customer`
Where {?LabelNo} >= 0
Order by 1



SELECT `Customer`.`Customer Name`
FROM `Customer` `Customer`
ORDER BY `Customer`.`Customer Name`

(It included this last section without my help)
This is what I have for Details a - e in the Suppress Blank Section:
RecordNumber >= {?LabelNo}