How to Use Pivot Tables with Ideate BIMLink

When your Revit schedule does not use the “Itemize every instance” option, the data displayed in the Revit schedule is being grouped according to the Sorting tab setup. When this is done, your Ideate BIMLink export will look quite different from the schedule because all data rows will be exported, and not grouped. You can follow these steps to convert your Ideate BIMLink raw data into a Pivot table which can group the data to align with the Revit schedule. Existing customers can also contact [email protected] to request this sample pivot table.

Related video: Calculated Parameters and Ideate BIMLink

Create the Excel File

  1. Open the sample file called rst_advanced_sample_project.rvt. This file ships with Revit.
  2. Start Ideate BIMLink, then select New to create a new link definition.
  3. Select the From Schedule option and pick the schedule called “Concrete Beam Schedule” as shown above.
  4. You will notice the warning above about the “Total Cost” not being supported.
  5. From the Properties tab, find the “Count” property and add this to the properties on the right. NOTE: this property was added to Ideate BIMLink in May 2021.
  6. Select OK, then use Export to write the data to a new Excel file.

Create a Pivot Table

  1. Within your new Excel file, select the 1st cell (A1), then from the Insert tab, select the Pivot Table button.
  2. OPTIONAL: The Table/Range will default to the specific cells, as shown. If you change the range to only reference the columns A:O then all future rows of data will be included.
  3. Select OK. This will create an new tab within the workbook called “Sheet 1.”
  4. Select the Mark and Volume checkboxes.
  5. Drag the Cost property into the Rows box. You may want to carefully select the Cost column and use Format Cells to change the units to Currency.
  6. To change the Volume formatting, select the field from the “Sum of Volumes” from the Values box and use the Field Settings>Number Format to edit the decimal rounding.
  7. Drag the other properties, as desired into the Rows checkbox. To mirror the schedule displayed within the Revit file, you should exclude both the Reference Level and Count properties.
  8. From the Design tab, use these settings:

Create a Calculated Field

To display the total cost of each beam type, we will need to take the Volume (ft3) x Cost per unit ($/ft3). This requires the creation of a calculated field.

  1. From the PivotTable Analyze tab select Fields, Items, & Sets, then pick Calculated Field.
  2. Make a new field called Total as shown. The Formula will need to be (Volume*Cost)/Count.

Update the Raw Data

Now that your pivot table is complete, you should be sure to use the option that will automatically refresh the pivot table each time the Excel file is opened. To access this setting, select the pivot table, then go to the Pivot Table Analyze tab and select Options as shown below. From the Data tab, pick the “Refresh data when opening the file” checkbox. Now your totals will be automatically updated to reflect any new Ideate BIMLink data.

If the number of data rows in your export is larger than your original export, you will also want to make sure that the data range (see Create Pivot table, step 2) has been defined to include the entire column length.

Submit feature or issue request for Ideate Software

Thank you for taking time to inform us about a bug or feature request.

By entering your data above and clicking ‘SUBMIT’ you are consenting to the collection and use of that data to respond to your comments and inquires, as described in our Privacy Policy. We may also transfer the data you submit in the ‘Email Us’ form to our VAR partners for the limited purpose of a sales follow-up. Please review our Privacy Policy for more details on how and when we collect data and what we use it for.