Address Label Quantity

[adam_7]

Hello all, I have seen this question several times in the forum in the past with no answers but decided to post myself in case anything has changed and the old posts were never answered.

I would like to print barcode labels through Jasper so that I can generate label information from my database. I have the "template" for my labels all set up and the correct fields entered into my label.

I can successfully generate the labels and print them on my sheets but i am having trouble printing more than one of the same label.

 

Example: (using sql database)

My report currently has 1 input control that specifices the UPC number that will be on the barcode.

Example Query

 

Select UPC,UPC_Description from UPCList where UPC = $V{UPC}

 

The input control works great and generates one label for the item. I would like now print multiple quantity of this same label. The only option i could find for attempting this was to alter my query so that it would do a loop while type of function. This option does generate the data multiple times, but not in the same results tables so that Jasper can see it as the same item multiple times.

 

Bascially the results look like

1. result

1. result

1. result

instead of

1.result

2.result

3.result

 

 DECLARE @copies INT

SET @copies = 1

While @Copies <= $P{Number_of_Labels}

Begin

Select UPC,UPC_Description from UPCList

where UPC = ' $P{UPC} '

Set @copies = @copies + 1

End

[hozawa]

Following blog seems to be talking about what you're trying to do.

http://www.manjuke.com/2011/01/repeating-sql-row-based-on-value-in.html

[adam_7]

I found some information on using temp tables to generate the data how i want in SQL, but JasperStudio/Jasperserver doesnt appear to like the query. I am assumin its too complex for the report to handle.  I decided that maybe it didnt like creating and dropping a temp table so i opted to create a permanent table but have the script truncate the table after run.  Here is my final script, it works great in SQL, but doesnt work in JasperStudio/JasperServer

 

DECLARE @copies INT

SET @copies = 1

While @Copies <= $P{Number_of_Labels}

Begin

Insert into LabelQuantity (UPC,UPC_Description)

Select UPC,UPC_Description from UPCLIST

where UPC = $P{UPC}

Set @copies = @copies + 1

End

select * from LabelQuantity

truncate table LabelQuantity

[adam_7]

I ended up abandoning the idea of doing the address labels through SQL and started doing them through Postgresql.

I created a database funcation that duplicated the item (for the quanity) then refrenced the function inside my dataset query along with the input paramaters.

I was successfully able to create a report that would give me XX quantity of a specific label.