Crystal Reports Inventory On-Hand Qty

Technical Questions

Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions

Topic: Inventory On-Hand Qty

Author	Message
jman1018 Newbie Joined: 08 Jun 2007 Online Status: Offline Posts: 13	Topic: Inventory On-Hand Qty Posted: 08 Aug 2012 at 8:04am
	Hello, I'm creating a report for our shipping department to ship against. The report is grouped by customer then sorted by ShipDate. I want to have a Field (AvailableQty) to count down the quantity of parts we have on-hand(On-Hand) minus the quantity to ship (Qty). How do I create the calculated field (AvailableQty) by PartNum? ShipDate PartNum Qty On-Hand Shipped AvailableQty 08/08/12 A 256 512 0 256 08/09/12 B 1 3 0 3 08/10/12 A 128 512 0 128 08/13/12 B 2 3 0 0 08/14/12 A 128 512 0 0
	IP Logged


DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 08 Aug 2012 at 8:23am
	can you group (or sort) on the partnum and then shipdate? if so you can easily use a running total or variable formula. Otherwise you would have to create 3 seperate formula(s) or an RT for each partnum, (and create new ones each time a new partnum was added ot the inventory). Edited by DBlank - 08 Aug 2012 at 8:24am
	IP Logged

jman1018 Newbie Joined: 08 Jun 2007 Online Status: Offline Posts: 13	Posted: 08 Aug 2012 at 9:49am
	DBlank I first have them grouped by customer then sorted by shipdate. I really need to keep the sort by shipdate. Because the person shipping may be able to ship so many days ahead, depending on the customer. How would I create the formula or an RT for each partnum?
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 08 Aug 2012 at 10:01am
	a nightmare but right click on Running Total ans selct new name= "A_available" (or whatever) field to summarize=Quantity type=sum evaluate=use a formula table.partnum='A' reset=never (assuming this is incremental for the whole report) place it on your details and you would see it only add part a quantities per row. you can then use that in a formula to subtract it from the 'on hand' field table.onhand - #available_A place this in the available quantity column. conditionally suppress it where table.partnum<>'A' repeat for each partnum and lay them all on top of each other (all conditionally suppressed
	IP Logged

jman1018 Newbie Joined: 08 Jun 2007 Online Status: Offline Posts: 13	Posted: 08 Aug 2012 at 10:55am
	DBlank Your right "nightmare". This will not work for me, because we have over 1,000 differant part numbers. Any other idea's? Can a variable be created for each part number "WhilePrinting" and the Quantity be subtracted from each detail, or something like that? JMan1018
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 08 Aug 2012 at 11:10am
	I could be wrong but the variable still needs to reference each partnumber and keep track for each unique part so it is basically the same as the RT. You still need at least one per partnumber. I am not sure I see a solution at the moment but do you have rights to creating stored procedures to use as a source? it might open some options... Edited by DBlank - 08 Aug 2012 at 11:11am
	IP Logged

jman1018 Newbie Joined: 08 Jun 2007 Online Status: Offline Posts: 13	Posted: 09 Aug 2012 at 2:52am
	DBlank I really don't want to give up on making this work. I'm not sure what you mean by having rights to create stored procedures, but I'm sure I do. I really appreciate you help. JMan1018
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 09 Aug 2012 at 3:50am
	It seems that this report has a primary use and this is a secondary function/value that you are trying to achieve. What are all of the purposes of the report?
	IP Logged

jman1018 Newbie Joined: 08 Jun 2007 Online Status: Offline Posts: 13	Posted: 10 Aug 2012 at 7:30am
	Would there be a way to create a Global Variable for each of the PartNum as it is reading from the database? Something like: WhileReadingRecords; NumVar {PartNum}:= 0; I tried this but obviously it doesn't work..
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 15 Aug 2012 at 10:09am
	So i gave this more thought. If you can live with only displaying the result in the main report maybe this might work...I did not test it but I think the theory/logic is sound. he main issue is it will run a subreport on every detail line so your performance may go way south... The idea will be to make a subreport to only show (not return in any variable) one value which is your Available Quantity 'Field'. YOu will have to tweak this but ... Make the sub report uising the same raw data set link the main report to the subreport on partnum and shipdate (and maybe an inventory number?) in the sub report use the links to limit the full data set to only row swith the same partnum and the date is <= link date. This should give you limited data set that you can then use a formula to get the final 'Available Quantity ' fotr that partnum on that date sum(quantity)-maximum(onhand) hide everything except this final value which would be displayed in the main report. Basically the subreport has to recalculate from scratch the vfalue on each run but it avoids trying to match shared variables for all the number parts, which is handled by the subreport linking,. Edited by DBlank - 15 Aug 2012 at 10:12am
	IP Logged

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