|
ISTech Support Forum
http://www.istechforum.com/YaBB.pl Crystal Reports, ODBC & Access >> Crystal Reports General Issues >> Associate record with BOM Component, not Parent http://www.istechforum.com/YaBB.pl?num=1251806791 Message started by David Waldmann on 09/01/09 at 05:06:31 |
|
Title: Associate record with BOM Component, not Parent Post by David Waldmann on 09/01/09 at 05:06:31 I am trying to write a report on completed WOs. I have grouped by WO Pre/Suffix, and want to calculate the cost of the actual issued components at the current Average Cost. However, when I put the Average Cost field in the detail section, it is pulling the Average Cost of the Parent, not the Component. How do I specify which one it is looking at? I am using the following tables: WORKORD WOMAT BKISMSTR With the following links: WORKORD.MTWO_WIP_CODE-->BKISMSTR.BKIC_PROD_CODE WORKORD.MTWO_WIP_WOPRE-->WOMAT.WOMAT_WOPRE WORKORD.MTWO_WIP_WOSUF-->WOMAT.WOMAT_WOSUF I've tried adding WOMAT.WOMAT_PCODE-->BKISMSTR.BKIC_PROD_CODE (and vice versa) but then I get nothing in the report. |
|
Title: Re: Associate record with BOM Component, not Paren Post by GasGiant on 09/01/09 at 06:09:59 I assume you are purposely not using the costs in WOMAT. For what you are seeking, I'm not sure why you need WORKORD at all. You do need to connect womat_pcode to bkic_prod_code in order to get the average cost of the component, but you do not need any connection with the parent. I'm unsure what you are trying to report on exactly, or I could be more specific. |
|
Title: Re: Associate record with BOM Component, not Paren Post by David Waldmann on 09/01/09 at 06:19:03 I am trying to calculate what it would cost to make the same item today, using the same Quantity it took at the time it was made at some point in the past. The reason is that our material usage varies according to yield. We have changed our process and want to try and figure out the net effect. We know our labor cost has gone up because the new process is more labor intensive. We also know our material usage has gone down. However, we are having a hard time getting a handle on whether it is actually worth it. Especially since material costs have changed significantly (in some cases by 30%), I can't look at the actual costs on the WO. So I want to take the Quantity Issued (of component) x Current Average Cost / Qty Completed (of parent) and compare those over time. |
|
Title: Re: Associate record with BOM Component, not Paren Post by GasGiant on 09/01/09 at 07:13:06 Then yes, only join codes bkic_prod_code to womat_pcode, plus the wopre and wosuf joins you have. Something like Code:
I tried this and it gave me three lines with all of the data needed to do the calculations. It might be quicker to use Excel for reports like this than CR, unless you really need pretty reports. |
|
Title: Re: Associate record with BOM Component, not Paren Post by David Waldmann on 09/01/09 at 07:17:41 Colin - thanks. I'll give that a try. And I guess I should try using Excel. It's just that I've only started using it instead of Quattro Pro so I am not very familiar with it, and not at all with the database connectivity. |
|
Title: Re: Associate record with BOM Component, not Paren Post by GasGiant on 09/01/09 at 07:33:06 Then stick with what works for you. I don't bother much with CR because I am more comfortable with other things, including Excel for speed and number crunching or PHP for flexibility and accessibility across the organization. Personal preference :-) |
|
Title: Re: Associate record with BOM Component, not Paren Post by kevind on 09/01/09 at 08:49:59 Here's (http://incolor.inetnebr.com/kcd/dba/Closed%20WO%20at%20Material%20Average%20cost.zip) a CR I threw together this morning that I think has the data and Grouping that you want. This report uses the Work Order History Files. |
|
Title: Re: Associate record with BOM Component, not Paren Post by David Waldmann on 09/01/09 at 09:38:09 Kevin, I am unable to download the file. Is it in a public location? thanks! -d |
|
Title: Re: Associate record with BOM Component, not Paren Post by kevind on 09/01/09 at 11:27:11 Oops. Our 'PCI' Compliance Scan forced us to close that link :( I moved the files and corrected the post above. |
|
Title: Re: Associate record with BOM Component, not Paren Post by David Waldmann on 09/01/09 at 12:33:48 Thanks, Kevin. I can get it now. By looking at how the tables and links are set up it looks like you added BKICMSTR twice, once linked to WORKHORD and once to WOHMAT? And then I see you have three Groups - is it that third level (group by WOHMAT.WOMAT.PCODE) that tells it to get the cost for .PCODE instead of .PRODCODE? I haven't re-written it for everything I need but it looks like it will do the job. I'd just like to have a better understanding so (hopefully!) I can do it myself next time. Always learning... |
|
Title: Re: Associate record with BOM Component, not Paren Post by kevind on 09/01/09 at 14:56:26 With Linking, you can link a given table as many times as you need too. All that is required is that you give each instance a different Alias. You can link a table to itself if you needed too (like in an exploded BOM). The reason I have it linked in twice is because in one of my iterations, I was showing the Average Cost of the parent item for comparison with the calculated cost of the material. Later, I decided that was of no use because the parent average includes labor. I just forgot to remove the link. So, all the fields in BKICMSTR.xxx are the Item information for the WO PARENT item. WOHMAT.PRODCODE is the product code of the PARENT item. WOHMAT.PCODE is the product code of the item being issued. You link WOHMAT.PCODE to BKICMSTR_1.PCODE so that you can get the average cost of the item issued. Also Always learning ... |
|
Title: Re: Associate record with BOM Component, not Paren Post by David Waldmann on 09/03/09 at 04:25:33 Just wanted to let you guys know I had success. I've said it before and I'll say it again: without this forum DBA would have died long ago. [smiley=thumbsup.gif] |
|
ISTech Support Forum » Powered by YaBB 2.1! YaBB © 2000-2005. All Rights Reserved. |