Hierarchical Grouping w Record Selection

[kwalters110]

Crystal Reports 8.5

Hi everyone, I could really use some help on this.

What I am trying to accomplish is generate a list that shows each piece of raw material for an assembly. It would like like follows:

Assembly 1
Raw Material 1
Component 1
Component 2
Raw Material 2
Component 1
Assembly 1
Raw Material 3
Component 3

In the table above, Assembly 1 has 3 components that use raw material. Component 1 has two different pieces of raw material. You should also note that Raw Material 2 is used directly on the assembly was well as component 1.

I was hoping I could get this using hierarchical grouping but so far I haven't had much luck. I am using the BOM view which has two fields: PARENT and PART. A PARENT can be any assembly or component, and a PART can be any component or raw material. So an assembly can have be made up of compents and/or raw material, and a component can be made up of other components and/or raw material.

Now that I got most of the technical stuff out of the way, here is what I have tried:

First I just added the V_BOM_MSTR view to the report, created a group on the {V_BOM_MSTR.PART} field and setup hierarchical grouping using {V_BOM_MSTR.PARENT} as the Parent instance field. This will give me the traversed tree where each Group #1 was an item on the bill of material, and each item of detail was the parent for that particular Group.

Thats exactly what I want to show but how can I select only the items for that particular assembly?

If you go into record selection and say {V_BOM_MSTR.PARENT} = {?Part} the record selection will only work for the first level of items in the BOM. If you have an item that is 3 levels deep (i.e. Assembly -> Component -> Raw Material) then the record selection will want the component to equal the {?Part}.

Any help would be greatly appreciated.

 

[IdoMillet]

You can start by joining the table to itself using an alias (Child). The join will create the first Parent-Child relationship. Then, apply the record selection condition to restrict the Parent table records to the assembly in question. Then, apply the hierarchical grouping to the "Child" table...

hth,
- Ido



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

http://www.MilletSoftware.com

 

[kwalters110]

Hi Ido

I actually tried that but I forgot to mention it. The results weren't quite what I wanted. Let me elobrate on what happened:

To show how I linked the tables here is the SQL:

SELECT
V_BOM_ASSM."PARENT",
V_BOM_PART."PARENT", V_BOM_PART."PART"
FROM
"GlobalCMC"."V_BOM_MSTR" V_BOM_ASSM LEFT JOIN "GlobalCMC"."V_BOM_MSTR" V_BOM_PART ON
V_BOM_ASSM."PART" = V_BOM_PART."PARENT"
ORDER BY
V_BOM_ASSM."PARENT" ASC,
V_BOM_PART."PART" ASC

Note that I am using a Left Outer join because if {V_BOM_ASSM.PART} is raw material, then it does not exist as a PARENT field in V_BOM_PART. You should also note that I have not implemented any type of record selection yet.

So Group 1 is {V_BOM_ASSM.PARENT} and Group 2 is {V_BOM_PART.PART}. Then I enabled hierarchical grouping for Group 2 where the Parent ID Field is {V_BOM_PART.PARENT}. Now here is a sample output:

Assembly 1
Assembly 1
Component 1
Component 2
Raw Material 2
Raw Material 3
Component 3
Component 4

The first Assembly 1 is the {V_BOM_ASSM.PARENT} and the second Assembly 1 is {V_BOM_PART.PART} and for some reason this particular part lists itself as a component so that duplication is expected, not a big deal. There is something missing from this though. Component 2 has Raw Material but it is not showing up.

Here is another example:
Assembly 1
Component 2
Component 3
Component 4

That should actually look like this:
Assembly 1
Raw Material 1
Raw Material 2
Component 1
Component 2
Component 3
Component 4

that examle completely skipped the first level of items on the BOM. After looking through a lot of the assemblies on my report, any assembly who does not have itself listed as a Part will not show the first level of the BOM. The other thing I noticed is the trees aren't being traversed for more than 3 levels deep (see the first example).

Any ideas as to what would cause that?

 

[IdoMillet]

It sounds like you have 2 types of hierarchical relationships here: Assembly-to-Assembly and Part-to-Assembly. I'm not sure if you also have Part-to-Part.

To clean it into a single hierarchy, create a UNION View so that Parts are no different than Assemblies.

By the way, at the start of your initial post, I don't follow the logic of having Parts go into raw materials. Shouldn't this be the other way around?

hth,
- Ido



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

http://www.MilletSoftware.com

 

[kwalters110]

Hi Ido,

Thanks for the quick response. I really appreciate the help.

It sounds like you have 2 types of hierarchical relationships here: Assembly-to-Assembly and Part-to-Assembly. I'm not sure if you also have Part-to-Part.

To clean it into a single hierarchy, create a UNION View so that Parts are no different than Assemblies.

I'm not quite sure I follow you here, could you elaborate alittle more?

By the way, at the start of your initial post, I don't follow the logic of having Parts go into raw materials. Shouldn't this be the other way around?

In the end, i want raw materials grouped together. If I am using the same raw material more than once I only want to show it one time in the report, and then list out the parts on the BOM that it belongs to. The end result needs to show purchase order cost graph for the past 4 years and if Raw Material 1 is being used on more than one part in the same assembly then there is no point in showing the same graph twice on the report. Does that make more sense? Sorry if it seemed alittle confusing.

Thanks again

 

[IdoMillet]

Now I see. The problem is that you are trying to mix 2 different objectives in 1 report.

The raw materials picture should be provided by a display or a data set (UNION may be required) that doesn't show parts. Or if you insist, the parts that each raw material is used for can be shown using inline subreports or concatanated string variables.

For example, the chart you just mentioned doesn't need to know anything about parts.

hth,
- Ido

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

http://www.MilletSoftware.com

 

[kwalters110]

Hi Ido,

Thanks so far for all your input but I still don't understand how to get the list of raw material. If a user enters a part number, how can I traverse the BOM to find every piece of raw material used in that part? Thats the part that has been holding me up. Are you saying I need to create a union and then run hierarchical grouping against that? What do i need to union together?