Working on an inventory report. Created a SQL command statement to list all parts and if there are an quantities on hand. Since parts could be in multiple warehouse, I have grouped by part number, hidden the details and show the on hand summary, part number and other data,(min/max qty) in the group footer. Created a prompt in crystal so the user can select parts by customer.

Sql Command:
Select P.PartNum, P.PartDescription,
     PC.ClassID, PC.Description as 'Class Description',
     PP.MinimumQty, PP.MaximumQty, PP.LeadTime, PP.MinOrderQty,
     C.CustID, C.Name as 'Customer Name',
               PB.WarehouseCode, PB.BinNum, PB.OnHandQty, PB.LotNum
From Part P
     Inner Join PartClass PC on P.Company = PC.Company
          and P.ClassID = PC.ClassID
     Inner Join PartPlant PP on P.Company = PP.Company
          and P.PartNum = PP.PartNum     
     Inner Join Customer C on P.Company = C.Company
          and Left(P.ClassID,3) = C.CustID
               Left Outer Join PartBin PB on P.Company = P.Company
          and P.PartNum = PB.PartNum
Where P.TypeCode = 'P'     
Order By P.PartNum

So far so good. I get the expected results of all parts and any on hand quantities. The result display quickly at this point.

Now for each part returned in the above query, I need to know if we have purchased and/or used any of those parts in the last year. So I created another command query that looks for the all possible purchase and use types that happened in the last year.

Select PartNum, LotNum, TranQty, TranType, TranDate
From PartTran
Where TranType in ('Pur-Ins', 'Pur-Stk', 'Pur-Mtl', 'Pur-Ukn', 'Stk-Mtl', 'Stk-Cus')
     and TranDate > DATEADD(YEAR,-1,GETDATE())
Order By PartNum

I created a subreport using the above query and linked the PartNum from the footer to the PartNum in the above query. The subreport has two running totals that total the TranTypes that start with Pur and the TranTypes that start with Stk to get my purchased and used quantities. I placed the subreport in the group footer and I get the desired results but it takes minutes to run and when I go to the next page it takes time to generate each page.

The report as is, is poorly designed because it is too slow to be usable. The separate queries run very fast in SQL Management Studio, but my implementation in Crystal need some help.

Any ideas on how to speed up the report?

Thanks.

Thanks so much! I had tried to make a single query multiple time and found a lot of ways that didn't work. Your query works perfectly.

Thanks for taking the time to help!
Joel