I need some help with trying to figure out how to group multiple records based on multiple levels of parents.
What I currently have is the original dataset and SQL report are based on the "Parent" column, however the structure gets lost and makes it hard to read as the subgroups of one assembly may be on a different page from the parent assembly.
It looks like this.
I'll try to show the existing situation and what I've done so far with the data and what result I'm trying to achieve.
Existing data structure is as below.
- The ID KEY is just an incremental number in a rolling database. As a new design is entered, it's entire BOM is entered in order based on the ItemKey structure.
- The ItemKey structure actually starts at 1 and then sub levels branch off based on the category the components belong to, but I filter it down to this level based on user selected "Assemblies" and "Subassemblies".
- We group the Parent fields but this leads to the poor structure in the report as parent components won't be listed directly with their sub components.
ID KEY Design ItemKey ItemNo Article No Material Description Variable Quantity UOM Parent ParentQuantity BOMComponent Total Qty 11115278 CS01060 1.2.1 1 1LUC000058EWX REFERENCE WINDING VISUAL
3 PC 1LUC002738AAX 1 Active_Part 3 11115279 CS01060 1.2.2 2 1LUC002739AAX
CORE CLAMP ASSY
1 PC 1LUC002738AAX 1 Active_Part 1 11115280 CS01060 1.2.2.1 1 1050013126 REFERENCE BOLT - HEX GRADE 8
6 PC 1LUC002739AAX 1 Active_Part 6 11115281 CS01060 1.2.2.2 2 1050021026 REFERENCE BOLT-HEXHD_GR5_PLAIN 0.63-11UNCx2.00in 16 PC 1LUC002739AAX 1 Active_Part 16 11115282 CS01060 1.2.2.3 3 1060001019 REFERENCE WASHER-PLAIN_0.63 NOM 1.31x0.66x0.09 16 PC 1LUC002739AAX 1 Active_Part 16 11115283 CS01060 1.2.2.4 4 1064238008 REFERENCE BELLVILLE SPRING WASHER
6 PC 1LUC002739AAX 1 Active_Part 6 11115284 CS01060 1.2.2.5 5 1230144002 1040059006 COVER PLATE-FLTCHPLT T:0.375 5.00X5.0 12 PC 1LUC002739AAX 1 Active_Part 12 11115285 CS01060 1.2.2.6 6 1230155002 1040057012 STIRRUP-YOKEBAND LARGE
8 PC 1LUC002739AAX 1 Active_Part 8 11115286 CS01060 1.2.2.7 7 4230173903
JACKSCREW_ASSY-1.25-7UNC
12 PC 1LUC002739AAX 1 Active_Part 12 11115287 CS01060 1.2.2.7.1 1 1060002017 1060002017 NUT-HEX 1.25-7UNC SAE J429Gr5 1 PC 4230173903 12 Active_Part 12 11115288 CS01060 1.2.2.7.2 2 4230172003 4230172003 JACKSCREW - 1.25-7UNC 1.25-7UNC 1 PC 4230173903 12 Active_Part 12 11115289 CS01060 1.2.2.7.3 3 1LUC009151DCCH 1060007017 NUT-HEX JAM 1.25-7UNC SAE J429Gr5 1 PC 4230173903 12 Active_Part 12 11115290 CS01060 1.2.2.7.4 4 1LUC070170DAI 4230165008 INSUL-CUP JACKSCREW PRESSBOARD - FORMED 1 PC 4230173903 12 Active_Part 12
Current Structure Layout Desired Structure Layout Level 1 Level 2Level 2Level 3
Level 2Level 4Level 4
Level 4Level 5Level 1 Level 2
Level 2Level 3
Level 3Level 4Level 2Level 3Level 1
Level 3Level 2Level 3
The only way I can see to obtain the desired structure is by splitting the "Parent" column into multiple columns based on the level that row is on.
From this I've arrived at the following subquery.
ID KEY PrevID Design ItemKey ItemKeyMod Key1 Key2 Key3 Key4 Key5 Key6 ItemKeyTotal ItemNo Article No Material Description Variable Quantity UOM Parent Parent1 Parent2 Parent3 Parent4 ParentQuantity BOMComponent Total Qty 11115278 11115277 CS01060 1.2.1 1 1
4 1 1LUC000058EWX REFERENCE WINDING VISUAL
3 PC 1LUC002738AAX 1LUC002738AAX
1 Active_Part 3 11115279 11115278 CS01060 1.2.2 2 2
5 2 1LUC002739AAX
CORE CLAMP ASSY
1 PC 1LUC002738AAX 1LUC002738AAX
1 Active_Part 1 11115280 11115279 CS01060 1.2.2.1 2.1 2 1
6 1 1050013126 REFERENCE BOLT - HEX GRADE 8
6 PC 1LUC002739AAX
1LUC002739AAX
1 Active_Part 6 11115281 11115280 CS01060 1.2.2.2 2.2 2 2
7 2 1050021026 REFERENCE BOLT-HEXHD_GR5_PLAIN 0.63-11UNCx2.00in 16 PC 1LUC002739AAX
1LUC002739AAX
1 Active_Part 16 11115282 11115281 CS01060 1.2.2.3 2.3 2 3
8 3 1060001019 REFERENCE WASHER-PLAIN_0.63 NOM 1.31x0.66x0.09 16 PC 1LUC002739AAX
1LUC002739AAX
1 Active_Part 16 11115283 11115282 CS01060 1.2.2.4 2.4 2 4
9 4 1064238008 REFERENCE BELLVILLE SPRING WASHER
6 PC 1LUC002739AAX
1LUC002739AAX
1 Active_Part 6 11115284 11115283 CS01060 1.2.2.5 2.5 2 5
10 5 1230144002 1040059006 COVER PLATE-FLTCHPLT T:0.375 5.00X5.0 12 PC 1LUC002739AAX
1LUC002739AAX
1 Active_Part 12 11115285 11115284 CS01060 1.2.2.6 2.6 2 6
11 6 1230155002 1040057012 STIRRUP-YOKEBAND LARGE
8 PC 1LUC002739AAX
1LUC002739AAX
1 Active_Part 8 11115286 11115285 CS01060 1.2.2.7 2.7 2 7
12 7 4230173903
JACKSCREW_ASSY-1.25-7UNC
12 PC 1LUC002739AAX
1LUC002739AAX
1 Active_Part 12 11115287 11115286 CS01060 1.2.2.7.1 2.7.1 2 7 1
13 1 1060002017 1060002017 NUT-HEX 1.25-7UNC SAE J429Gr5 1 PC 4230173903
4230173903
12 Active_Part 12 11115288 11115287 CS01060 1.2.2.7.2 2.7.2 2 7 2
14 2 4230172003 4230172003 JACKSCREW - 1.25-7UNC 1.25-7UNC 1 PC 4230173903
4230173903
12 Active_Part 12 11115289 11115288 CS01060 1.2.2.7.3 2.7.3 2 7 3
15 3 1LUC009151DCCH 1060007017 NUT-HEX JAM 1.25-7UNC SAE J429Gr5 1 PC 4230173903
4230173903
12 Active_Part 12 11115290 11115289 CS01060 1.2.2.7.4 2.7.4 2 7 4
16 4 1LUC070170DAI 4230165008 INSUL-CUP JACKSCREW PRESSBOARD - FORMED 1 PC 4230173903
4230173903
12 Active_Part 12
I split the "ItemKey" column and create new columns with those split values to then adjust the placement of the "Parent" values under Parent1, Parent2...
The report looks a little better but still an issue with the grouping because of all the empty values in the "Parent1", "Parent2"... columns.
Since the order is correct by default, I need a way to fill in the blanks in these columns with the previous actual value.
For example, all of "Parent1" should be "1LUC002738AAX" because that is the highest level that all other components belong to.
"Parent2" should remain empty as long as there is no value in "Key2", otherwise it should replicate down until the next existing value and continue until all records in the query are filled.
"Parent3" should remain empty as long as there is no value in "Key3", otherwise it should replicate down until the next existing value and continue until all records in the query are filled.
Same for "Parent4" then "Parent5" and "Parent6", even though I don't have all of those columns there yet.
Then I should be able to group by "Parent1", "Parent2", "Parent3" and then "Parent4".
Is there a way to group by more than 4? I think I have a maximum of 6 sub groups in any design.
Thanks for your help!
peteywhit