Indented Costed BOM Crystal Report

[jdavissrusa]

I know this is not really a help thyself question, but doesn't anyone have a Indented Costed BOM report out there writen in 8.5 for SQL? I need it to show last and average costs.

Thanks

Joe

 

[MacolaHelp]

Suggestion: use the bm\reports\indented bill of material in pwe bm06p1 & add the cost fields from iminvloc. I would also save it as 8.5 w/a new name & a decent driver for pervasive or sql rather than those *.dta drivers. You'd need one for each company & you can compile or distribute, attach to menus if need be. You'll also have to build your own select statements. Sometimes it is easier to start fresh, but at least you can see what tables and fields are on the macola version. No costs on theirs, but not difficult to add since the table is already in the report.

 

[dgillz]

This is not an easy report.

The problem with an indented costed BOM is that it requires you to self join the BMPRDSTR to itself a number of times -- how many depends upon how many levels deep your products structures go -- from component item to parent item.

On top of this, you must also have several aliases of the IMINVLOC -- one for every level -- to pull out the std and avg costs. Add the same number of aliases for the IMITMIDX if you want item descriptions.

Place each level of the report in a separate details section, and conditionally suppress a lot of ugly details that you don't want to see, and this report is doable. The formulas to sum the cost will require use of variables.

I have done this report for std cost only, up to 4 levels only, and it took me about 30 hours. I am sure I could cut that time in half now, as some of what I was doing was learning curve. However if you need an extra level, figure on adding a couple of extra hours.

One last comment, and if anyone knows how to do this (Peter ?) I would be grateful. Using heirarchical grouping, I think this would be possible AND fairly easy, however I have been unable to produce this report, I have only been able to produce a where-used report. Perhaps someone knows hierarchical grouping and can tackle this?


Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[kscheeler]

I did this in Btrieve (11 levels), though not with costs and it run's great. Comes up in a couple of seconds. Dgillz is right that you need to chain link and bring in all of the the tables 11 times to go that far down and for BMPRDSTR_SQL, you need to link the parent of the 2nd table to the component of the first and so on.

When I converted the report to use the SQL drivers, the time to run increased dramatically. It took almost 4 to 5 minutes for the same item where in Btrieve it took 3 to 4 seconds. I even tested the same select query in query analyzer and it ran the same.

Right now, I'm working on a stored procedure which initially went fine with selecting the records I needed very quickly but I don't have them ordered correctly so they will print in the same fashion as the indented BOM report does.

Kevin Scheeler

 

[vbajock]

I wrote a VB program that does this. Its part of a system that tracks cost components of an item for an importer who wanted to see his item costs broken down further than what Macola provides for, duty, exchg rate, etc, so they can make global changes to say an exchange rate and automatically make that change to all components purchased in that currency. It then used a BOM processor, using self joins of BMPRDSTR as you described, to perform the roll up to finished goods cost. In order to make it fully automatic I had to write a ton of low-level table creation and structure code so the app could react to changes in the BOM depth. Definately not a do-it yourself project. It also is vulnerable to data errors - for example, if a Location is missing a raw material for the rollup, it screws up your BOM joins, so it is best if you create a master table from just the BMPRDSTR joins first so you know you have all the required raw materials in the data, and then use update-style code to fill in the IMINVLOC and IMITMIDX stuff after your done. You can then set up error code to look for blanks or zeros.

 

[vbajock]

Don - whats your current email address?
Kirk

 

[dgillz]

dgilsdorf@trianglepartners.com or dgilsdorf@mchsi.com


Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com