Use Warnings Data for an Excel Dashboard
In this topic we will demonstrate how the exported warnings data from Ideate Explorer can be used with Excel to visualize the relative severity of warnings across one or more Revit projects. A similar workflow is also available for use with PowerBI. The example files referenced below are available by request for Ideate Explorer subscribers: REQUEST DATASET.
To begin, unzip the contents of the file provided. In this example we will place the files within a new folder called Test Project. The files included in the zip file are:
- Excel Dashboard Template — \IdeateExplorer-RevitWarnings.xltx: This Excel template file references the FileInformation.xlsx and the exported Warnings data from Ideate Explorer and has the interactive warnings charts.
- Warnings Data folder — \Ideate Explorer Warnings Export: This sub-folder is where Ideate Explorer Warning data from one or more Revit files are saved.
- File Information - FileInformation.xlsx: This file is used to gather file names and file sizes.
- Excel Chart Template folder — \Chart Templates: This sub-folder contains a few sample chart templates that are used in the IdeateExplorer-RevitWarnings template file.
The default location for Microsoft Excel Chart Templates folder is located here: C:\Users\<username>\AppData\Roaming\Microsoft\Templates\Charts. Verify that the Charts folder exists in this folder. If not, create a Charts folder here and copy the provided Charts into this folder.
Review Data Sources
The IdeateExplorer-RevitWarnings.xltx file contains the dashboard visualizations and references the warnings data found in the folder called Ideate Explorer Warnings Export. To ensure that this file can properly read the sample warnings data, review the data source setup by following these steps:
- Open IdeateExplorer-RevitWarnings.xltx. On the “Review Warnings”, click on “Enable Content” button if prompted to do so.
- Select the Query Tab. Query>Edit Panel>Edit. This will bring up Power Query Editor.
- Home Tab>Data Sources>Data source settings. This brings up Data source settings dialog box.
- Click on “Change Source…” button to update paths for File Information.xlsx and the Warnings Export Directory. Change the data source location to the correct location.
- In Power Query Editor – Home>Close>Close & Load.
- Data Menu>Queries & Connections>Refresh All. The data will show.
The Revit file(s) warning exports will now appear (or refresh) in the Revit Warnings worksheet tab and the Revit files(s) information will now appear (or refresh) in the File Information worksheet tab. Do not modify either of these worksheets, as they feed information into the other worksheet tabs.
Review Chart Templates
To complete the process of reviewing the sample warnings visualization data, you will need to use the chart template provided. Follow the steps below to connect the IdeateExplorer-RevitWarnings.xltx file to the chart templates.
Excel: Single File Worksheet Tab
The chart on this tab is designed to let the user pick from a list of files and review the Revit warnings per the Ideate Explorer rankings as shown below.
- Make the worksheet tab called Single File active.
- Click on an open spot on the Warnings Chart. This will allow you to modify its properties and swap the chart out for another chart template.
- Insert Menu>Charts>Click on expand icon, as shown above.
- In the left hand column, select Templates. Select the SingleFileWarning template. Click OK
- Click on any of files under File Name slicer to look at an individual file.
- The chart should be visible. If not, click on the Data menu > Queries & Connections panel > Refresh All
Excel: Count Worksheet Tab
The chart on this worksheet tab is designed to display the Revit warnings per the Ideate Explorer rankings for all files within as shown below. Repeat the steps above for the chart found on the Count tab, choosing the Count template.
Excel: Count MB Worksheet Tab
The chart on this worksheet tab is designed to display the Revit warnings per the Ideate Explorer rankings for all files within as shown below. Repeat the steps above for the chart found on the Count MB tab, choosing the CountMB template. The threshold line in red represents the value established within your company for an acceptable number of warnings per the file size. You can also set this value within Ideate Explorer.
NOTE: For this chart it is important that the rows on the 2nd table match the table above.
If needed, highlight the last row (columns A:E) and using the green dot, drag downwards to create any additional rows that may be needed as shown.
SAVE your work as a new Excel Workbook into any location you wish.
Add New Project Data
If you wish to use your project data with the above template information, then follow these steps.
- Remove the provided sample Excel files from the Ideate Explorer Warnings Export folder.
- Open the FileInformation.xlsx file and edit the existing table to add your Warnings Export File Names and related Revit File Sizes, in MB. You will need to manually gather the file name and file size, as this information is not exported as part of the Warnings Export. Note: Be sure to maintain the table format. Save and close the Excel file.
- After completing the above steps, return to your Excel Warnings Chart file. Navigate to the Data menu > Queries & Connections Panel > Refresh All to update the results. Note: If you move or change the locations of the FileInformation.xlsx file and the Warningd Export folder, then you will need to update the paths in the Data source settings. Refer to the Review Data Sources section above for more information.
- Save your work