Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2016 : Technical Questions
Message Icon Topic: Label Report Help Post Reply Post New Topic
Author Message
Butterfly_Joe
Newbie
Newbie


Joined: 04 Feb 2009
Online Status: Offline
Posts: 8
Quote Butterfly_Joe Replybullet Topic: Label Report Help
    Posted: 09 Jul 2009 at 8:49am
Hello,
 
I need some assistance with a Label report.  I need to create a report for inventory items, however, I need it to print duplicate entries for the number of parts there are in total.  For example: if we have 12 cogs on hand, I want it to display 12 labels showing the Part# and Description, so that they can be placed on each part.  The way our database is setup, I have Part#, Description, & Items on Hand at my disposal.  Now, I tried to simply put the Part# and Description in using the Label Wizard in Crystal Reports 10.  Unfortunately, it does not account for the total items on hand and just displays one entry for each part.  Would there be any way to tell Crystal Reports to multiply the Items on Hand by Part#/Description, so that it will generate what I want?
 
(Note: These database items are all located in one table / Using Avery 5160 Standard Labels)
 
Thank you!
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4285
Quote lockwelle Replybullet Posted: 10 Jul 2009 at 6:12am
No.  Crystal only reads the number of records that it has been given, so if there is 1 record that 'stands' for 100 items, Crystal only prints 1 record.
 
The best solution, is to write a stored procedure that can make 100 lines of identical information in a table for a record that has a quantity of 100, and then have the report generate off of this stored proc.
 
If you only needed a set number (and it is fairly low, like 2-4) for all items, there are other methods that you can do to have Crystal 'see' the multiple records, but for your purposes, they would be too cumbersome.
 
HTH
IP IP Logged
Butterfly_Joe
Newbie
Newbie


Joined: 04 Feb 2009
Online Status: Offline
Posts: 8
Quote Butterfly_Joe Replybullet Posted: 10 Jul 2009 at 6:21am
Not be be a complete bother, but how would one 'write a stored procedure' and go about using that with Crystal?  Also, if this method is used, would it have to be altered each time the quantity changes?  Or could that be linked to the quantity?  The only reason I ask, is because I would prefer to make this as automated as possible, so that anyone in the office can print the inventory labels (when we receive new items, etc.), rather than coming to me each and every time.
 
Thanks!
 
EDIT: Since I'm a complete novice in crystal and SQL Statements, I read the Crystal help file regarding Stored Procedures:
 
"If the stored procedure is designed to prompt a user for information to base its query on, Crystal Reports will prompt you for that information when you select the stored procedure for your report."
 
Would there be any way to have it prompt how many items I want for a specific product?  That would be the last resort, if I cannot link it to the actual database quantity field (since that would be auto-updated; less human error)


Edited by Butterfly_Joe - 10 Jul 2009 at 6:25am
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4285
Quote lockwelle Replybullet Posted: 13 Jul 2009 at 7:19am

I would write the stored proc to use the quantity in the database and thus be automatic.

for this issue, the hard part is creating the number of row that you need...
maybe...
 
select itemNumber, quantity into #list from someTable
 
select * into #label from #list where quantity > 0 --initialize the table
while (select SUM(COUNT(*) - Quantity from #labels group by itemNumber) > 0
 insert #label
 select *
 from #label l
  join (select itemNumber, COUNT(*) AS Current from #labels group by itemNumber) ss ON ss.itemNumber = l.itemNumber
 where ss.Current < l.quantity
 
select * from #label order by itemNumber
 
there are lots of sites to help guide you building a stored proc, which is a bit out of the realm for this forum.  The above, should, create a table with the itemNumber (and quantity) repeated as many times as Quantity says to.
 
I haven't tested it, but I think it would work.  you would want to replace the names that I used with the actual names from the database, and any other information that you would want displayed on the label, the above just shows how to generate the multiple rows.
 
As to the help in Crystal, what it is saying is that if the stored proc takes any parameters (and your may or may not, they are not required) then Crystal will automatically build a parameter and pass it to the stored proc when your report is run.  Your user will have to assign a value to the parameter (because your store proc needs it), so Crystal is just helping you out by building the parameter.
 
Hope this helps you out.  Knowing SQL, I think, really makes Crystal that much simpler, just because YOU can select/manipulate the data before Crystal ever sees it, which can make a complex report, just a bit easier, as much of the complexity might have been moved to the stored proc.
 
Good Luck
IP IP Logged
DrWho
Newbie
Newbie
Avatar

Joined: 06 Sep 2011
Location: Portugal
Online Status: Offline
Posts: 1
Quote DrWho Replybullet Posted: 06 Sep 2011 at 1:25am
Been around with this problem, nothing seemed to work... heres what i came up with.

Add this sp to your database and report.

Create PROCEDURE [dbo].[CreateLabelsByQT]
AS
begin
Create Table #TempNumID (QtID int)
declare @Qtid int=1

while @Qtid<=10000
begin
     INSERT INTO #TempNumID (QtID) Values (@Qtid)
     Set @Qtid=@Qtid+1
END

SELECT dbo.Inventory.ItemNumber, #TempNumID.QtID, dbo.Inventory.Quantity
FROM dbo.Inventory INNER JOIN
#TempNumID ON dbo.Inventory.Quantity >=#TempNumID.QtID
ORDER BY dbo.Inventory.ItemNumber
end


This simple example only works until 10000, but you may get the max number of quantity and use it on the while to create the temp table, with the max quantity.

Hope this helps,

HS
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.047 seconds.