Grouping with sub-assemby parts. 1

[lbizzell]

If I have an item number 0126015500 and it has sub-assembly parts as 0126015500-001 and 0126015500-002. Is there a way to group by 0126015500 with the sub assembly parts below?


Example:
0126015500
0126015500-001
0126015500-002

Thanks-
Laurie

 

[rosemaryl]

Dear Laurie,


You don't mention what version of crystal or what database. You do not give an example of the field name.

For example, does the sub assembly item # = 0126115500-001 or is it another field?

Provide some more details: Database, Version of Crystal, Example field names and so on and I am sure you will get a better response.

regards,
ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[lbizzell]

II'm trying to create a Bill of Materials list. I suppose it would help if I provided more information

It's Crystal 9 with SQL server. The items are all pulled from the same table {item.item} and all sub-assembly items are indicated with -xxx.

Thanks-
Laurie

 

[rosemaryl]

Dear Laurie,

Cool, then this is very easy.

Create formula and group on that (not as efficent performance wise, but not terrible). If you find that you are taking a performance hit .. then we can explore a sql expression.

There are different options for this, If your item number field varies in length then we will have to take another path...

Now, first the Crystal formula:

{Item.Item} [1 to 10]
//extracts first 10 characters of the field

//new formula
//if item is of variable length

numbervar p := if InStr({Item.Item},'-') = 0 then length({Item.Item})
else InStr({Incident.Subject ID},'-')-1 ;

mid({Item.Item},1,p )
//end formula

Hope that helps,

ro






Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[lbizzell]

Ro,
It worked great! Thanks so much!
Laurie

 

[mikeplee]

I have a similar problem to Laurie, but in my case, the sub assembly parts are generated by a stored procedure in SQL Server. We are using Crystal 9. What I'm trying to accomplish is have a group footer for each item that has a total of the cost for each level of that item.

Our ERP software already has a costed bill of materials report that outputs the item and all the sub assemblies that go into it, but does not group them with within Crystal. The output is similar to below:

level Item#
DetailA 0 PartA-main item
DetailA 1 PartA sub assembly #1
DetailA 1 PartA sub assembly #2
DetailA 2 sub assembly for preceding assembly
DetailA 1 PartA sub assembly #3

DetailA 0 PartB-main item
DetailA 1 PartB sub assembly #1
DetailA 1 PartB sub assembly #2
DetailA 2 sub assembly for preceding assembly

....etc.

The level and item number is a string that is created by the stored procedure and output into crystal. It is also only one field.

Any ideas would be appreciated.

Thanks
Mike

 

[rosemaryl]

Dear Mike,

Are there any other fields and what does the actual Item # look like for each example.

regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[mikeplee]

Ro,

The report containcs the following fields: description, source(purchased or manufactured), quantity, lot size, unit of measure, material cost, labor cost, overhead cost and outside cost.

The part numbers aren't any sequence where I can group easily...I've been able to get a total for the level for each individual item, but haven't yet figured out a way to sum them....I used a formula to grab the material, labor, overhead, and outside cost numbers depending on what level the item is. An example of the formula is:

if ({Rpt_IndentedCostedBillofMaterialSP;1.indenture}) startswith " 0"
then {Rpt_IndentedCostedBillofMaterialSP;1.tOvhd}

level item material overhead
labor outside
0 701632-ff2

1 702372

2 702369

1 701123-11-e

Thanks
Mike

 

[adhhealth]

so
group by table.level

 

[mikeplee]

I would if I could, but there is no table with level in it...the level and item number is in one field, generated by the stored procedure.

Thanks
Mike