I am often asked about how to create a Crystal Report that generates a “Bill of Materials” or BOM. A Bill of Materials is the list of parts required for a job or order. The challenge comes when the some of the parts are actually assemblies or ‘kits’, which are made up of other parts but sold under a single part number.

When a BOM includes an assembly item, it should show all of the components needed to make that assembly. In some cases the assembly is made up of individual components but it is also possible that an assembly is made up of another smaller assembly – which may also be made up of another even smaller assembly. Customer describe their BOM requirements as having to go down 5, 10 or even more levels to get to the individual parts. The question is how do you do this in Crystal?

Some people think they can solve this with the “hierarchical grouping” (HG) feature of Crystal Reports. Unfortunately this feature will not work for any BOM I have seen. This is because HG assumes that each record only has one parent in the hierarchy. The classic example of HG is a list of employees with a second field that identifies their supervisor. A report using HG can automatically set up an organizational chart of who supervises who, no matter how many levels of supervisors there are – but only as long as each employee is supervised by one person. Or, in database terminology, each child record (component) can only belong to one parent (assembly).

But in most BOM environments the same component can be used in several assemblies, and this is where HG breaks down. Hierarchical Grouping will NOT automatically repeat a component record so that it appears in several different assemblies. So if you have an item like a washer that shows up in several assemblies, a report using HG will only show that component in one place.

So how do you create a BOM? The only way I have been able to do this is to daisy chain together several aliased instances of the same tables. You would have to have as many alias instances as the deepest point of your assembly hierarchy. Then you link them together using left outer joins. These will automatically explode each assembly out to the individual component level, which might be level 2 or level 6.

You can then write formulas to determine how far down each row had to go to reach the component. That way you can determine which column to use as the component part number. The quantity is a bit trickier because you have to take the quantity from the component level and multiply that by all of the quantities of the assemblies above that level. Then you have the true quantity of that component needed for the entire list. Once these formulas are written you should be able to group the records for each component together and total the true quantity needed for that component, regardless of the number of levels involved.