Working with Formulas, Totals, and Roll-ups

There are Four sections here:

• Single level roll-up of a property column
• Single level roll-up of a calculated property column
• Multi-level (eg. sub-assembly) roll-up of a calculated property column
• Troubleshooting

Creating a “Roll-up” is the act of summing a property column in a BOM. A single Level Roll-up does nothing more than total a property column.  A Multi-Level Roll-up totals a property column from a lower level sub-assembly and places that value in the assembly.

For example, an assembly has a sub-assy with four parts. To understand the cost of the entire assembly we must “roll-up” the costs of the four parts of the sub-assembly into the assembly.

The same can be done for weight, mass, or any other numeric property.

OpenBOM can do single level and multi-level roll-ups.

Single level roll-up (eg total) of a property column

A single-level roll-up of a single value is simply the sum (or total) of the cells in a column. It can be easily applied and controlled by the “Display Totals” function in BOM.

1. To calculate the total for a property column start in Single Level display

• • Click the down arrow next to the Property Name and choose  “Enable rollup totals”
  • Click the “Display Totals” checkbox in the menu bar.

2. Here we are enabling rollup totals for the Cost property

3. Next we check the “Display Totals” checkbox

4. The Cost for all items in this BOM have been “rolled-up” in the totals row at the bottom.

Single level roll-up (eg total) of a calculated property column

Similar to above but when the property column to be totaled is a calculated value.

Open your BOM and

1. Switch to SIngle Level

2. Click Add Property

3. Add a property which will be calculated (in this case Extended Cost)

Tip: Use the Property order command to move columns to your liking.  Or use the “Add property to Right” command on the property drop down menu.

4. Next define the formula for Extended Cost as shown below

5. When the Formula Editor appears define the formula for Extended Cost as Cost * Quantity.

6. Next enable Roll-up totals for Extended Cost column as shown below:

7. And finally turn on “Display Totals” in the menu bar

Multi-level roll-up (eg. total) of a calculated property column

This section deals with the case where you wish to total the values of a property from a sub-assembly or sub-assemblies and place that value (eg. roll it up) into the assembly.

Some rules to remember:

• Add properties like Cost, Extended Cost, etc, using the Add Property command while in Single Level display.
• Be sure all values in your formula are defined, example. You MUST have a value in the Quantity column.
• Be sure to always check the box to “Add property to sub-assemblies” as shown below

1. Here we are adding Cost, do the same for Extended Cost.  Be sure to “Add to sub-assemblies“!

2. In sections I & II above it was not necessary to add to sub-assemblies as we were only totalling values in the top level, here we are totalling them (eg. rolling them up) from lower sub-assemblies so it is required that we have the identical properties in use in all levels.

3. Next define the formula for Extended Cost similar to above but you MUST choose the Roll-up option from the formula editor as shown below:

4. Switch to multi-level display and be sure you have a Quantity and Cost defined for all items at ALL levels

5. Return to Single Level and enable rollup totals for Extended Cost column as shown below

6. Finally Enable Totals checkbox in the menu bar to see the Extended Cost total for the entire assembly

Troubleshooting:

• A BOM can only have one roll-up column active at a time.  We are working to remove this limitation.
• Some browsers behave differently when rendering the totals, if you have trouble refresh your browser.
• If calculated cells will not fill in or you see an #ERR after refresh, go to the top cell in the column, RMB->Edit Formula and be sure the formula definition AND the “Apply to all rows” options are correct
• For multi-level rollups
  • be sure ALL items at ALL levels have a Quantity value, this includes sub-assemblies as well
  • Ensure that a Cost is associated with every “part”, if you are rolling up Extended Cost, the Cost for each sub-assembly will automatically be calculated and filled in with the sum of the Extended Costs from the parts of the sub-assembly(s) below

The following blog post has additional information and links to videos

Sharing is caring!