Customer Support

Creating a graphical bill of materials in Visio

Bill of materials / document tree in Visio With PDXpert PLM software, you can create parts and documents in multi-level structures. A PLM structure combines the features of a bill of materials with the supporting technical documentation traditionally depicted using a document tree. The PLM assembly parts list (with quantities, units of measure, reference designators) conveniently includes all relevant assembly drawings and instructions, inspection procedures, and specifications.

While most PLM software users will be quite comfortable navigating the multi-level structure, you may occasionally require a graphical means for showing the parts and documents of a product. A picture may be useful during a major design review, or as a navigation reference for manufacturing, product service or customer support staff.

Within 10 minutes, you can have a graphical view of your bill of material in Microsoft® Visio®. Although this process relies on Microsoft Office 2007 tools, the techniques should be applicable to other releases.

Download this file, which contains the XSLT and Excel spreadsheet template files that you'll need, as well as some example exports from PDXpert and a Visio and PDF file with the results.

Set up the BOM export format in PDXpert PLM software

You can export a multi-level structure from PDXpert PLM software as XML, CSV, Excel, and other useful formats. The native data format, PLMX (www.plmx.org), must be transformed using XSLT and, in this instance, exported as an Excel spreadsheet.

We'll create a new member of the PDXpert Data transformation collection (see the PDXpert client help guide: Contents > How to use the Collection Explorer > Adding a new collection member). We'll only need to do this once. This lets us use our Data transformation collection member any time we want to create the export file.

Set the Name to "Multi-level Export (Current)".
Mark the Table-formatted data checkbox as TRUE.
Open the MultiLevelExportCurrentXslt.txt file in Windows Notepad (not in a word processing application). Copy the entire contents (Ctrl+A, Ctrl+C), and paste it (Ctrl+V) into the new member's Export/Report XSLT textbox.
Close or lock the window to save your changes.

If you want to export unreleased assembly markups, you'd follow the same general procedure for creating a "Multi-level Export (Markup)" DT member using the MultiLevelExportMarkupXslt.txt file.

Create the PDXpert PLM software BOM export file

After we have created the new DT member, we're ready to export the multi-level product tree:

Open the Report/Export Wizard (Tools | Report/Export Wizard...).
Click the Export table-formatted data button.
Select the "Multi-level Export (Current)" dataset.
Drag a released multi-level assembly from the Item Explorer onto the Export Wizard.
Click the Finish button.
In the Save As file dialog, select an Microsoft Excel 97-2003 Workbook from the Save as type: dropdown, enter a file name, and click the Save button.

Applying the "Multi-level Export (Markup)" template would export the marked-up version of the assembly.

Prepare the bill of materials export file for Visio

Visio 2007 has a useful utility called the Organization Chart Wizard. It will import a list of data and automatically create a hierarchical organization tree, which looks pretty similar to what we'd want for our product tree.

Unfortunately, the Wizard assumes that an employee can have only one manager, and automatically filters any duplicate employee records. However, we want to show that a particular part or document reports to multiple parent items. So, we have to trick the Organization Chart Wizard by temporarily modifying the imported data, then removing the changes after the data has been imported into Visio. The Excel formulas to achieve this are a bit complicated, but it's mostly a copy-and-paste operation from our PDXpert-bill-of-materials-tree-for-visio.xls spreadsheet template.

In Excel, open both the PDXpert structure export file (created above) and the downloaded template file, then:

Insert a new row 2, and identify the export file top assembly. You only need to enter the values for Class, Number and Name. If your Number value is purely numeric (e.g., 000034), you can force it to be treated as a text string rather than a number (34) by prefixing it with an apostrophe ('000034).
Highlight and then copy all 15 columns of the top 2 rows (range N1:AB2) of formulas from the Excel template into the first empty cell to the right of your structure export data (M1 or N1). Some cells may appear blank, but they'll be useful in the next step.
Replicate the formulas in row 2 for all the items in your structure export spreadsheet. Highlight the 15 cells in the second row, then increase the selection to include all empty cells for the other items. Replicate the formulas by selecting the Fill Down command (Ctrl+D). Note that a unique number of "|" characters has been added to each row, which will prevent the Visio from filtering duplicates. We'll get rid of these later.

Click on the "new" tab next to the Structure tab to create a new worksheet in your export workbook called Chart. Paste the following values into the appropriate cells of the Chart worksheet (the results of C2 will appear blank, since the top assembly reports to nothing):

Cell	Value
A1	Name
A2	=INDIRECT(ADDRESS(ROW(),MATCH("ReportsTo",Structure!A$1:Structure!AZ$1,0)-13,4,,"Structure"))
B1	Title
B2	=INDIRECT(ADDRESS(ROW(),MATCH("ReportsTo",Structure!A$1:Structure!AZ$1,0)-12,4,,"Structure"))
C1	ReportsTo
C2	=INDIRECT(ADDRESS(ROW(),MATCH("ReportsTo",Structure!A$1:Structure!AZ$1,0),4,,"Structure"))

Creating the BOM import list on second (Chart) worksheet

Replicate the formulas in row 2 for all the items in your structure export spreadsheet. Highlight the 3 cells in the second row, then increase the selection to include all empty cells for the other items. The highlighted rows should match the number of data rows you have in the Structure worksheet. Replicate the formulas by selecting the Fill Down command (Ctrl+D).
Save and close the structure export file. We're now ready to import the file into Visio.

Displaying the multi-level bill of materials data in Visio

Open Microsoft Visio and then:

On the File menu, select New, select Business, and select Organization Chart Wizard.
On the Wizard, select Information that's already stored in a file or database.
Click the Next button, and follow the remaining steps of the wizard. You can initially accept most of the defaults provided by the Wizard.
After the structure is displayed, remove all of the "|" characters using the Replace dialog (Edit menu > Replace...).
- In the Find what: box, enter ||||. In the Replace with: box, enter |. Continue replacing until no more changes are possible.
- Repeat the replacement of || with | until no more changes are possible.
- You should now only have single non-repeating instances of |. Replace all | with a single space character (" "). Visio can't accept a zero-length space in the Replace with: box.

You can use the built-in Visio tools to arrange the objects, and can apply themes and other formatting as desired.

Final notes

To ensure that the item descriptions fit within the Visio boxes, cell P2 in the template has a formula which restricts the description length to 40 characters. Change the value in this formula (and in the subsequent rows) to adjust the description maximum length.

If you have more than 8 levels in your structure, insert additional columns immediately to the left of the structure tab's ReportsTo column, and update the formulas accordingly. However, given display & printing size constraints, it's more likely that you'll want to delete items from the list; do this on the Structure worksheet prior to copying the formulas from the template.

If your item descriptions use the "|" character, you can substitute "{" or "~" or another unused character in the REPT() function, contained in cell O2 in the template.