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!

 

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

 

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)

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