Benchmark Integration - Cost Sheets

Owned by Maria Harreguy
Last updated: Sept 10, 2023Version comment
3 min read

The integration between Benchmark and Workbench is done with a flat file exported from a Benchmark project and can be imported either into a Workbench Job Budget with the Benchmark file as it is or can be imported into a Workbench Quote Costsheet, which will require the file to be modified a bit to match the columns of the Workbench import.

The key to this integration is the data mapping for Workbench Activity Codes and Workbench Work Centre Codes.

Benchmark currently derives the Workbench Activity Code from a configurable setting:

  • Directly from the Resource Code, or

  • A mapping using the Resource Category

The most commonly used method is the Resource Code as Activity Code, and this is the one that will be explained below.

 

For the import to be successful

  • Benchmark Project Resource Code must match Workbench Activity code

  • Benchmark Project Resource Cost Code must match Workbench Work Centre Code

  • Benchmark file must be modified to have a total of 22 columns (Benchmark exports 21 columns which matches the Workbench Budget import).

 

Importing the file generated by Benchmark

The .csv file generated by Benchmark can be imported into a Cost Sheet. Things to consider:

  • Costsheet must be created first.

  • Costsheet Input must be At Revenue & Cost Element (not At Cost Element, nor At Activity, Work Centre), which has a format of Revenue lines and cost lines under each Revenue line. The Revenue lines are Benchmark Project Items, and the Cost lines are the Benchmark Resources.

  • Import stored procedure is Cost Sheet Schedule Import (wbImportCostSheetScheduleLinesWeb). Download the import file sample, and populate it with the data from the file.

    • Quantity column from the original file needs to be moved as the last column

    • Add a Markup column after Sell Rate. No values needed. This will make a total of 22 columns.

 

Tips to troubleshoot errors:

If you get an error when importing the file in Workbench, the error displays a line number, that is the row in your file. The file can be opened using Excel, Notepad (or any text editor). Duplicate the file and work with the copy of the file. Delete all lines except for the header line, the row that caused the error, the previous row and the next row. This way you can just focus on a smaller data set that will make it easier to find what could be wrong. Any columns that are marked as mandatory in the import screen must be populated. Play around with this data set, modify the data and try to import again. Once you have identified the cause of the error(s) you can fix that on the original file. Delete the costsheet lines imported while you were testing, and import the original file with the data fixes you have done.

 

Technical details about the Resource data mapping between Benchmark and Workbench

A key rule used in this export file from Benchmark to Workbench relates to how Resources are classified and “mapped” between the two systems. The following Table explains this mapping and is provided for your information:

 

Benchmark configuration:

Attached is a document from Benchmark with the steps on how to configure Cost Codes, the integration, and how to create the export file. It is highly recommended that you check Benchmark’s user documentation first, to make sure you check the latest documentation, however, this document version will suffice to understand the steps required to be done in Benchmark.