Printing SSRS report to label printer x times

Question

I am sorry if this is a duplicate question and please point me to the answer if it is.

Here is my situation:

I have an ASP.NET web forms site that uses SQL server database as its data source. The end user wants to print labels to a Zebra label printer. (Old printer Zebra 110XiIIIPlus-200dpi) I can install this printer on the end users system or it can run from the web server, doesn't matter it is a network printer.

I can retrieve the data from the database ok. My problem starts when I need to print. Lets say that I have four parts, p1 p2 p3 & p4. All the labels have the same format:

Job #, Mark #, Customer, Width(in), Length(in) (which all come from the SQL DB)

The only field that is pulled in query and not printed is the qty. Mark # is the part number (Don't know why it isn't just called part #). Now lets say that p1 has a qty of 12, p2 has a qty of 25, p3 has a qty 321, and p4 has a qty of 35.

When it prints I need to send 12 "copies" of the label for p1, 25 "copies" for p2, 321 "copies for p3, and 35 "copies" for p4.

How do I send 12 labels for p1 to be printed, then use the next record's data and send 24 labels etc. etc.?

I do not have any code for printing yet because i can not figure out how to do this!! Does anyone know of a way that I can do it.

I did find one article here on SO: Print a report Multiple times, (SSRS reporting services) but I am not sure how to make it work, if it even can) for what I need.

One last note I am using VB.Net in the code behind if it makes a difference.

Any help is very much appreciated!

score 0 · Answer 1 · answered Sep 27 '17 at 18:57

0

I had to do the exact same thing, the solution I came up with was to loop though the select and union the same select by the number of items in the Quantity. By doing this you should get 12 rows for the P1 since that that is the quantity of boxes, all the data should be the same except for Page# that should auto increase by 1 until the end of the quantity.

Results would be something like:

Job# | Mark# | Quantity | Page
------------------------------
1    |  P1   |   12     |   1
1    |  P1   |   12     |   2
1    |  P1   |   12     |   3
1    |  P1   |   12     |   4
.....
1    |  P1   |   12     |   12

Then you would group on Mark# and Page and Create a Page break between each instance of a group, this will make it so you get the number of pages based on the quantity.

answered Sep 27 '17 at 18:57

NewGuy

1,020
1
9
24

Did you use a while loop in the SQL statement? – mbcharney Sep 28 '17 at 11:58
Yes, The way I did it is by using dynamic sql to add a union of the same query times the amount of quantity for the product so if the Item had 12 items you would do your primary query and then loop through to add 11 unions so that you get 12 lines. Then add a page number column. – NewGuy Sep 28 '17 at 13:38
Is it possible for you to show me the code? I am just not getting. I understand what it is you are doing I just can't seem to work it. – mbcharney Sep 28 '17 at 14:14

score 0 · Accepted Answer · answered Sep 29 '17 at 13:59

Thanks to the help of SO user newGuy I was able to figure out how to do this. Here is the solution that I cam up with that works.

    --Drop Temp tables if exists
If OBJECT_ID('tempdb..#Labels') Is Not Null Drop Table #Labels
If OBJECT_ID('tempdb..#Pieces') Is Not Null Drop Table #Pieces

--Declare variables
Declare @MarkNumber varchar(10)
Declare @Qty int
Declare @RowCount int = 0

Create Table #Labels
(
    vjobnum varchar(12),
    marknumber varchar(25),
    customer varchar(25),
    pwidth decimal(18,4),
    plength decimal(18,4)
)
Create Table #Pieces
(
    marknum varchar(25),
    totqty int,
    customer varchar(50),
    jobnum varchar(12),
    plength decimal(18,4),
    pwidth decimal(18,4)
)

Insert Into #Pieces(marknum, totqty, customer, jobnum, plength, pwidth)
Select od.marknum, od.qty, oh.customer, oh.van_job_num, od.bbin, od.cbin From tbl_order_detail od Join tbl_order_head oh On oh.ordernum = od.ordernum Where od.ordernum = (Select Distinct ordernum From tbl_BearingBarRpt)

Set @RowCount = (Select COUNT(*) From #Pieces)
While @RowCount > 0 --Exists (Select marknum From #piecelabels)
Begin
    Select @MarkNumber = (Select a.marknum From (Select ROW_NUMBER() OVER (Order By marknum) as RowNumbers, *From #Pieces) a Where a.RowNumbers = @RowCount)
    Select @Qty = (Select totqty From #Pieces Where marknum = @MarkNumber)
    While @Qty > 0
        Begin
            Insert Into #Labels(vjobnum, marknumber, customer, pwidth, plength)
            Select pc.jobnum, pc.marknum, pc.customer, pwidth, plength
            From #Pieces pc
            Where pc.marknum = @MarkNumber

            --Decrement the Qty counter
            Set @Qty = @Qty - 1

        End

    Set @RowCount = @RowCount - 1
End

It may not be the best but it definitely works!

Printing SSRS report to label printer x times

2 Answers2