Bill of Materials in Crystal

[BigglesUK]

Has anyone tried doing a bill of materials report in Crystal?

I have 2 tables. See the picture below to get a rough idea. BOMVERSION is the grouping by BOMVERSION.BOMID, and then a table with the lines in on is (BOM).

Our lines have 2 item types. Phantom and Item. If its a phantom it has a Bill of materials under it.

Im trying to get a "flat pack" of the bill of materials. So where it has an item that has a bill of materials in it to include it into its own...

is this possible? I presume i need to use some sort of array or subreport but i cant get my head around how to process it.

 

[BigglesUK]

Forgot to mention im running Crystal XI to a SQL 2000 DB.

 

[Madawc]

I'm not clear what you're wanting. If you want to print a particular document for just some of the records you read, this can be done by having the document as a detail section and suppressing it when it wasn't suitable.

Right-click on a section and choose Section Expert. Then choose the formula icon (x+2 and a pencil) for suppression. Enter a formula to suppress the details.

If you can exclude the unwanted records in record selection, that is much more efficient than telling the Server to send them to you and then checking / rejecting them on your own PC.

If you want both a list of all records and longer documents for selected records, then use a subreport in the report header or footer.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[IdoMillet]

Look into the Hierarchical Grouping option under the Report menu.

- Ido

view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[synapsevampire]

So you want a listing of the materials for the phantom types, but not directly beneath the part, rather in some subsequent section?

And wouldn't all of the parts listed as phantom have this as well, the example shows just the one.

If you want it directly below (not like the example), Left Outer join the tables and group by the BOMID, show the BOMID and other info in the group header, and use the child table (bill of materials) in the details.

-k

 

[mocgp]

I understand exactly what you are doing with the BOM; I have developed an almost identical format by using a subreport to show the breakout list. The subreport is a clone of the main report but filters out the basic item category (in your case, "Item") and is linked on the id of the part (in your example, the second column). The subreport is suppressed for any item that does not have a breakout (phantom). If your report doesn't pull a lot of records, this is the best way to achieve what you want. The catch with this method lies in the size of the reports; since the subreport has to run for each row, performance can suffer greatly when you are pulling a lot of items. There is another way to do this without the subreport but I'm in the midst of trying to sort that one out and can't accurately describe the process at this point. If you have any specific questions, perhaps we can arrange to get connected via email and I can share my technique with you.

 

[BigglesUK]

Thank you all for your replies. Its given me several different idea's on what to do.

mocgp, sounds like you have a good grasp on what im trying to do. I have the unfortunatly circumestances that we have 30,000 released items, and 20,855 have BOMS! And we have quite a complicated structure and can have up to 12 levels in our structures. It takes quite a while to pull the records, thats why i was thinking a subreport which would refresh per level might be ok for just displaying one offs.

I would like to discuss it further with you if you can please, my email is bartley(at)xptech(dot)co(dot)uk.

thanks,

Andrew

 

[synapsevampire]

Given your situation, look into Ido's idea of hierarchical grouping, listed under the Report menu.

-k

 

[BigglesUK]

yeah, that was the other way I was investigating, looks interesting so im going to have a play with it.

 

[lbass]

If it makes sense, you might want to make the subreport on-demand, so that it executes only when the user wants the details. Should speed up the report greatly.

-LB

 

[kenhamady]

One key question for doing a recurring BOM like this is:
Do any of the items have to appear at more than one BOM level? If a screw that is used as part of the top level is also used in several of the sub levels then I am pretty sure this would preclude using the heirarchical grouping feature. If I remember correctly heirarchical grouping can only put a record into one group of the heirarchy.

Subreports would work for one level but since you have 12 recurring levels I will assume that the top phantom can be made up of several phantoms which are also made up of other phantoms, etc. Subreports can't launch subreports so this might be a limitation.

The only way that I have come close to this is to use 12 levels of self-referencing joins - repeating the tables with aliases out to the 12th level. By using Left Outer joins you will cause the data set to automatically stop expanding at the proper level. It is a real beast to do this way and probably would be best done within a view in the database.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides for Formulas, Parameters, Subreports, VB, .NET, Tips and Tricks

http://www.kenhamady.com/