Duplicate Qty From WO adding to Total

[bstafford21]

Using CR 9.2
I have a report that displays in Columns all Daily Work Orders the Part No., Material Issued, the WO Qty, the Qty Stored, Qty Scrapped.

I need to have a daily summary of Parts Stored and Material used for each type of material, some materials used numerous times during the shift.

Date (Ops.Date_of_Completion)
WO (WO.WO_No)
Part (WO.Part_No)
Material (Materials.Part_No)
Mat Iss (Materials.Qty_Issued)
WO Qty (WO.Qty)
Qty Stored(WO.Qty_Stored)
Qty Scrap (WO.Qty_Scrap)
Price (Part.NormSellingPrice)
Scrap Cost(@Scrap Cost) using {Part.Norm Selling Price}*{WO.Qty_Scrapped}
Produced Cost(@Produced Cost) using {Part.Norm Selling Price}*{WO.Qty_Stored}
Total Scrap % (@IndividualScrap%) using if {WO.Qty_Scrapped} = 0 then 0 else 100-(({WO.Qty_Stored}-{WO.Qty_Scrapped})/({WO.Qty_Stored})*100)



I have created a Group for each day using Ops.Date_of_Completion, so now all my Production is grouped for each day showing me the totals using the Insert Summary for Qty Stored and Qty Scrapped. This is in the Group Footer

I also did a Sum of @Produced Cost which has duplicated Sales figures

My problem is there are some WO.WO_No listed 2 or 3 times for some parts. This is due to the material used, they have sometimes 2 or 3 different materials to make a part. These are the WO.WO_No's that are duplicating in the Summary of Qty Stored. I need to not count these again, only once.

My second area I need to do is how much material for each type of material was Issued. I need a summary of each type, because they are not all the same. How do I do this?

Thanks for any help and I hope I have given enough information to solve this. I appreciate anyone's time.

Thanks

Bill

 

[IdoMillet]

The solution for the Material by Type question is to insert a CrossTab.

The first question can be solved by using one of two approaches. If you can group by WO_No, add that group level and use a Running Total with an accumulation frequency of "Once for Each..." and select that group level.

Otherwise, create a VIEW in your database, a correlated subquery in your Command data source, a linked subreport, or an SQL Expression (SQL expressions can be a SELECT statement, provided it returns a single value) that provides the desired amount. You can also do this with an array variable (to identify if a value was already included and if so, not increment another variable).

hth,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[bstafford21]

Hi IDO,

Thanks for responding. However I have never done a Cross Tab before and not sure what you mean by an accumulation freq. How do I set this up?

I am new to formulas, and have taken some time to just get where I am now. Took alot of searching to get the formulas I have now.

Any further guidance would be much appreciated.

Thanks
Bill

 

[IdoMillet]

See online help on "Crosstab"

See online help on "Running Totals"

Cheers,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[bstafford21]

Hi IdoMillet,

After reading the subject on Running Totals I have figured it out and it works the way I need.

Thanks for helping and pointing me in the right direction.

Regards

Bill