BOM Totals using custom iProperties

[Shag_Bore]

Hello,

I am looking for a way to speed up my current method of calculating part totals. Attached is a excel spreadsheet with some formulas that group all parts and sum up their totals. Totals are comprised of 3 quantity types, AREA (ft²), Length (ft), and UNIT QTY. 

I have manipulated Inventor's part number naming so it groups parts together based on parameters (if they exist).I then added the true part number to a blank property, Cost Center. The parts (if applicable) have custom iproperties created based on initial values when created. (AREA (ft²), Length (ft))

In the spreadsheet, I manually create column QTY ACTUAL which is Length * Item QTY, (if there is no Length, then Item QTY is returned)

Then I created the GRAND TOTALS column, which is AREA (ft²) * QTY ACTUAL, (if there is no value in AREA (ft²), then QTY ACTUAL is returned)

Once I have the GRAND TOTALS for each separate part, I used two formulas, one to group all the parts based on the COST CENTER part names 

=IFERROR(INDEX($B$2:$B$100,AGGREGATE(15,6,(ROW($B$2:$B$100)-ROW($B$2)+1)/($B$2:$B$100<>"")/(ISNA(MATCH($B$2:$B$100,L$1:L1,0))),1)),"")

and the other formula to sum up those totals with the same names.

=SUMIFS(K:K,B:B,L2)

I would like to if possible do the same process but within Inventor, So that I can create a drawing with parts list that shows the Total Qty of each part, for our billing and purchasing department. They don't require the individual breakdown parts like fabrication department.

I have been digging around and found this rule, it goes through each part, find the AREA (ft²) parameter, but then sums all the parts and produces one custom iprop called AREA (ft²). 

Sub Main
  Dim oApp As Inventor.Application =  ThisApplication
  Dim oAssy As Inventor.AssemblyDocument = oApp.ActiveDocument
  Dim TotalArea As Double = 0
 
  For Each oSubDoc as Inventor.Document In oAssy.AllReferencedDocuments
    ' Only process if document is a part document
    If oSubDoc.DocumentType = kPartDocumentObject Then
       'Get Custom Properties (User Defined Properties)
       Dim oPartPropset As Inventor.PropertySet = oSubDoc.PropertySets("Inventor User Defined Properties")
       Try                                                
          Dim PartArea As Double = oPartPropset("AREA (ft²)").Value
          ' Add part area to total area
          TotalArea += PartArea
       Catch ee As Exception
         ' Do not raise or display an error.  We assume all parts have a "square foot" custom property
       End Try
    End If
  Next

  ' Get Master Assembly Custom PropertySet
  Dim oAssyPropset As Inventor.PropertySet = oAssy.PropertySets("Inventor User Defined Properties")
  Try
    ' Set Custom Property to total Area
    oAssyPropset("AREA (ft²)").Value = TotalArea
  Catch
    ' Do not raise or display an error.  We assume the Master Assembly has  a "total square foot" custom property
  End Try

  oAssyPropset = Nothing
  oAssy = Nothing
  oApp = Nothing
  
  'Remove the following when doen testing
  MessageBox.Show(TotalArea.ToString & " SqFt of parts in the assembly")
End Sub

I assume there is a way to create a custom parts lists like the excel document, with the GROUPED PARTS and GROUPED QTY'S, but I am not sure what the best way is?   Custom iproperties?

Thanks!

Sean

PS- I used Clint Brown's code to create the initial XLS from the Assembly BOM

oDoc = ThisDoc.ModelDocument
'Ensure that we are in an Assembly file - Exit if not
If oDoc.DocumentType = kPartDocumentObject Then
	MessageBox.Show("You need to be in an Assembly to Export a BOM", "TEKSIGN")
	Return
End If
oDoc = ThisApplication.ActiveDocument
Dim oBOM As BOM
oBOM = oDoc.ComponentDefinition.BOM

'**************************************************************************************
'You can change the output path by editing CSVpath below - by default the path is the same as the assembly file
'CSVpath = ("C:\Inventor\") 'If you change the path, remember to keep a \ at the end
CSVpath = ThisDoc.Path + "\"
'**************************************************************************************

'Get user input for Export Type:
Dim MyArrayList As New ArrayList
MyArrayList.Add("")
MyArrayList.Add("Structured - All Levels")
MyArrayList.Add("")
MyArrayList.Add("Structured - Single Level")
MyArrayList.Add("")
MyArrayList.Add("Parts Only - (Shows components in a flat list)")

SEANSBoMExporter = InputListBox("Choose a BoM type to Export: " & TEKSIGN , MyArrayList, d0, Title := "TEKSIGN: BoM Export ", ListName := "BoM Type")

If TEKSIGNBoMExporter = "Structured - All Levels" Then :Goto GoAllLevelsExport : End If
If TEKSIGNBoMExporter = "Structured - Single Level" Then :Goto GoSingleLevelsExport : End If
If TEKKSIGNBoMExporter = "Parts Only - (Shows components in a flat list)" Then : Goto GoPartExport : End If
If TEKSIGNBoMExporter = "" Then : Return : End If
	
'STRUCTURED BoM ALL LEVELS:
GoAllLevelsExport:
' the structured view to 'all levels'
	oBOM.StructuredViewFirstLevelOnly = False
' Make sure that the structured view is enabled.
	oBOM.StructuredViewEnabled = True
	Dim oStructuredBOMView As BOMView
	oStructuredBOMView = oBOM.BOMViews.Item("Structured")
' Export the BOM view to an Excel file
	oStructuredBOMView.Export(CSVpath + ThisDoc.FileName(False) + ".xls", kMicrosoftExcelFormat)
Goto GoLaunch:

'STRUCTURED BoM Single Level 
GoSingleLevelsExport:
	oBOM.StructuredViewFirstLevelOnly = True
	oBOM.StructuredViewEnabled = True
	oStructuredBOMView = oBOM.BOMViews.Item("Structured")
	oStructuredBOMView.Export(CSVpath + ThisDoc.FileName(False) + ".xls", kMicrosoftExcelFormat)
Goto GoLaunch:

'PARTS ONLY BoM
GoPartExport:
	oBOM.PartsOnlyViewEnabled = True
	Dim oPartsOnlyBOMView As BOMView
	oPartsOnlyBOMView = oBOM.BOMViews.Item("Parts Only")
	oPartsOnlyBOMView.Export (CSVpath + ThisDoc.FileName(False) + ".xls", kMicrosoftExcelFormat)
Goto GoLaunch:

'Get user input - do you want to see the BoM?
GoLaunch:
	i = MessageBox.Show("Preview the BOM?", "TEKSIGN", MessageBoxButtons.YesNo)
	If i = vbYes Then : launchviewer = 1 : Else : launchviewer = 0 : End If 
	If launchviewer = 1 Then ThisDoc.Launch(CSVpath + ThisDoc.FileName(False) + ".xls")