Skip to main content
Procore

Update Custom Procore Analytics Reports Using Legacy Custom Fields

 Important

This tutorial is written for users more familiar with updating custom reports in Power BI Desktop. 

  1. Open Power BI Desktop.
  2. Open a report that has been customized to use custom fields from one of the following Procore fieldsets:
    • Project
    • Prime Contracts
    • Purchase Orders
    • Subcontracts
 Note
The screenshots throughout this tutorial use the Project fieldset as an example.


procore-analytics-project-custom-fields.png

  1. Click Transform Data to open the Power Query Editor.

    procore-analytics-transform-data.png
     
  2. Click New Source and select SQL Server from the drop-down menu.

    procore-analytics-new-source-sql-server.png
     
  3. In the SQL Server database window: 

    procore-analytics-sql-server-database-window.png
     
    • Enter constructionbi.database.windows.net in the Server field.
    • Enter Procore in the Database field.
    • Click the Import button under the Data Connectivity mode options.
    • Click OK.
  4. In the Navigator window:

    procore-analytics-navigator.png
     
    • Enter the SQL view name in the search bar to locate the custom field table. 
      Below are the SQL view names for the fieldsets applicable to this tutorial:
      • Project: vwProjectCustomField
      • Prime Contracts: vwPrimeContractCustomField
      • Purchase Orders: vwPurchaseOrderContractCustomField
      • Subcontracts: vwWorkOrderContractCustomField
    • Mark the checkbox next to the search result.
    • Click OK.
  5. Select the fieldset's ID column, the custom_field_key_label column, and the custom_field_value column.
    Below are the column names for the fieldset identifiers applicable to this tutorial:
    • Project: ID
    • Prime Contracts: PrimeContract_ID
    • Purchase Orders: PurchaseOrderContract_ID
    • Subcontracts: WorkOrderContract_ID
       
    • To select all columns:
      • Left click one column name.
      • Hold the CTRL key and left click the other column names 
  6. Right click on one of the three selected columns and select Remove Other Columns from the drop-down menu.

    procore-analytics-project-custom-fields-remove-other-columns.png
     
  7. Select the custom_field_key_label and custom_field_value columns.
    To select the two columns:
    • Left click one column name.
    • Hold the CTRL key and left click the other column name.
  8. Click the Transform tab.
  9. Click Pivot Column.

    procore-analytics-project-custom-fields-pivot-column.png
     
  10. In the Pivot Column window:

    procore-analytics-pivot-column-window.png
     
    • Click Advanced options.
    • Select Don't Aggregate from the Aggregate Value Function drop-down menu.
    • Click OK.
  11. Click the Home tab.
  12. If the update was successful, you will see your company's custom fields as individual columns in the query corresponding to their fieldset.

    procore-analytics-project-custom-fields-update-successful.png
     
  13. Click Close & Apply.

    procore-analytics-close-and-apply.png
     
  14. On your report, click the Model view.

    procore-analytics-model-view.png
     
  15. Establish a relationship with the appropriate table and columns for the fieldset.
    • Project: Connect Project.ID to vwProjectCustomField.ProjectID
    • Prime Contracts: Connect PrimeContract.ID to vwPrimeContractCustomField.PrimeContract_ID
    • Purchase Orders: Connect PurchaseOrderContract.ID to vwPurchaseOrderContractCustomField.PurchaseOrderContract_ID
    • Subcontracts: Connect WorkOrderContract.ID to vwWorkOrderContractCustomField.WorkOrderContract_ID

      procore-analytics-project-custom-fields-relationship.png
  16. Reconfigure your report's visuals by sourcing the applicable custom field data from the new table you've created.
  17. Save and publish your report.