Questions Printing Product Labels from Purchase Receipt
I have a report design that prints a product barcode, product code, expiry date, and lot number. This works fine for single items or items with unique batches/serials. But I would like it to print 10 labels if 10 units are receipted on the same batch.
Any idea on report configuration to achieve this?
It's a good question. I'm not sure how to do it with customization. The problem is that Report Designer will only generate multiple rows if there are multiple rows in the database table. But 10 units of one lot or not lots will all be one row in the table with a value of 10 in the quantity column. We need a way to turn that one row into 10 rows. I'm not sure how to do it without doing some kind of DAC customization.
I had a similar request from a client who wanted to print price labels for orders, but wanted to print a label for each item so we had to multiply the item by the quantity. I did this with a customization, as Tim has suggested. The basic idea is you need a SQL numbers table (google it) to multiply out your quantity and produce the number of lines needed. Here is a brief overview of what I did, but if it doesn't make sense I may be able to put together a customization.
1. Create a numbers table in SQL. This is a simple table that has a number column that counts up. The table below allows you to work with quantities up to 1000. If you have larger order quantities then increment the number
SELECT TOP 1000 IDENTITY(int,1,1) AS Number INTO NumbersTable FROM sys.objects s1 CROSS JOIN sys.objects s2 ALTER TABLE NumbersTable ADD CONSTRAINT PK_NumbersTable PRIMARY KEY CLUSTERED (Number)
2. Create a SQL view to pull together the data based on the SQL select below. I believe this will work, but it may need some tweaking. I also use CompanyID > 1 to limit the view to real companies and not snapshots.
SELECT CompanyID,Number,ShipmentNbr,LineNbr,SplitLineNbr,InventoryID,LotSerialNbr,ExpireDate FROM SOShipLineSplit INNER JOIN NumbersTable ON Number <= SOShipLineSplit.Qty WHERE CompanyID > 1
3. Create a new customization package and create your new DAC based off your view in Step 2.
4. Publish and then the DAC will be accessible to the report.
I just noticed this on GitHub which might be pretty useful for printing multiple product labels:
@lauraj46 just solved this without a Customization Project and solved it in style. It's a brilliantly creative solution that is simple and clean. I'm adding it to this Discussion Topic on her behalf: