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
- Open the sample file called rst_advanced_sample_project.rvt. This file ships with Revit.
- Start Ideate BIMLink, then select New to create a new link definition.
- Select the From Schedule option and pick the schedule called “Concrete Beam Schedule” as shown above.
- You will notice the warning above about the “Total Cost” not being supported.
- 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.
- Select OK, then use Export to write the data to a new Excel file.
Create a Pivot Table
- Within your new Excel file, select the 1st cell (A1), then from the Insert tab, select the Pivot Table button.
- 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.
- Select OK. This will create an new tab within the workbook called “Sheet 1.”
- Select the Mark and Volume checkboxes.
- 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.
- 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.
- 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.
- From the Design tab, use these settings:
- Subtotals = Do Not Show Subtotals
- Grand Totals = “Off for Rows and Columns” or On For Columns Only”
- Report Layout = Show in Tabular Form
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.
- From the PivotTable Analyze tab select Fields, Items, & Sets, then pick Calculated Field.
- 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.