The out-of-the-box Project Cache is configured to report on the Project Statement and Projects Tracker list columns and metrics (included in the out-of-the-box templates). If you have added columns to a Project Statement, you can use the Automatic Mapping steps below to include them in Project Cache reporting.

For new columns in the Projects Tracker list, or metric items in the Metrics list, you should follow the Manual Mapping steps below to include these columns in Project Cache reporting.

Automatic Mapping

To map a column from the Project Statement to the Project Cache:

  1. Add Column to the Project Statement
  2. Add Column to Project Cache List
  3. Mapping a Column to the Project Cache

Automatic mapping is a recent addition to BrightWork. It currently allows you to do the following:

Add
Mapping
Add
Column
Remove
Mapping
Project Statement
Project Tracker
Metrics

Add Column to the Project Statement

If you have not already created the new Project Statement column you need then:

  1. Click the settings cog in the top right and click Site contents.
  2. To open the Project Statement's 'List Settings' page click the ellipsis to the right of the Project Statement tile and click Settings in the pop out dialog box.
  3. Click Create column to create the new column and configure it as needed and click OK.

Add Column to Project Cache List

The following steps require Site Collection Administrator access.

When adding a column to the Project Cache list you need to open the Project Cache and create a column of the appropriate type.

  1. The Project Cache list is a hidden list to help prevent accidental editing, though there are two ways to open the Project Cache list:
    1. From the Project Cache Mapping page select the 'Project Cache List' link at the top of the page.
    2. Alternatively, access it by its URL by editing the URL in your browser and pasting bwProjectCache after the site collection name in the URL.
      For example the URL should be something like: https://myserver/sites/mysitecollection/bwProjectCache
  2. Add the column to the list using the appropriate column type.

Guidelines for creating a new Column in the Project Cache

If the source column is coming from a Project Statement or Projects Tracker list, you should follow these rules:

Source Column Type      Project Cache Column Type
Single line of text
  • Single line of text (Recommended)
  • Choice
Multiple lines of text
  • Multiple lines of text
    It is recommended that you specify the same type of text e.g.
    • 'Plain text' to map to 'Plain text'.
    • 'Enhanced rich text' to map to 'Enhanced rich text'.
    • 'Append Changes to Existing Text' to map to 'Append Changes to Existing Text'.
Choice
  • Choice (Recommended)

    If you are adding a choice column to the Project Cache list, ensure that Yes is selected under Allow 'Fill-in' choices.

    With choice columns, you do not have to worry about the values entered into the choice box - they are picked up from the source column; however, you should clear the default value to prevent it displaying in reports where there is no value specified for the relevant item.

    If multiple selections are allowed in the source column then it is recommended that multiple selections be allowed in the Project Cache column. If the single value option is selected then only one value is written to the cache.

  • Single line of text
  • Choice with 'allow multiple selections'
Choice with 'allow multiple selections'
  • Choice with 'allow multiple selections'
Number
  • Number (Recommended)
    It is recommended that the Project Cache column be configured with the following same number formats as the Project Statement:
    1. Number of decimal places
    2. Show as percentage
    'Default value' should be blank to prevent a value displaying in reports where there is no value specified.
  • Currency
Currency
  • Currency (Recommended)
    It is recommended that the Project Cache column be configured with the following same number formats as the Project Statement:
    1. Number of decimal places
    2. Currency format
    'Default value' should be blank to prevent a value displaying in reports where there is no value specified.
  • Number
Date and Time
  • Date and Time
Lookup
  • Choice (Recommended)

    A Lookup cannot map to a Lookup column in the Project Cache. Mapping to a Choice column is recommended because it is a closer feature match than a Single line of text column.

  • Single line of text
Lookup with 'Allow multiple values'
  • Single line of text (Recommended)
  • Choice
Yes/No
  • Yes/No
Person or Group
  • Person or Group (Recommended)
  • Person or Group with 'Allow multiple selections'
  • Choice
  • Single line of text
Person or Group with 'Allow multiple selections'
  • Person or Group with 'Allow multiple selections' (Recommended)
  • Person or Group
  • Choice
  • Single line of text
Hyperlink or Picture
  • Hyperlink or Picture
    It is recommended that the same URL format is used for both columns. These are either:
    1. Hyperlink
    2. Picture
Calculated Single line of text
  • Single line of text (Recommended)
  • Choice
Calculated Number
  • Number (Recommended)
    It is recommended that the Project Cache column be configured with the following same number formats as the Project Statement:
    1. Number of decimal places
    2. Show as percentage
    'Default value' should be blank to prevent a value displaying in reports where there is no value specified.
  • Currency
Calculated Currency
  • Currency (Recommended)
    It is recommended that the Project Cache column be configured with the following same number formats as the Project Statement:
    1. Number of decimal places
    2. Currency format
    'Default value' should be blank to prevent a value displaying in reports where there is no value specified.
  • Number
Calculated Date and Time
  • Date and Time
    Caution: when writing a calculated column formula to return a date it is important to check for blank dates in the formula and return a blank in that instance.
Calculated Yes/No
  • Yes/No
Choice Indicator Icon
  • Hyperlink or Picture, format URL as Picture.
Number Indicator Icon
  • Hyperlink or Picture, format URL as Picture.

If the source is in a Metric, select a column type that maps to the data type you want to output from the Metric.

  • Metric Value (use MetricValue) - use a Number/Currency column or a Date and Time column, depending on the Metric type
  • Target Value (use TargetValue ) - use a Number/Currency column or a Date and Time column, depending on the Metric type
  • Indicator Icon (use IndicatorIcon) - use a Hyperlink or Picture column
  • Indicator Status (use IndicatorStatus) - use a Choice column
  • Formatted Metric Value (use FormattedMetricValue) - use a Text column
  • Formatted Target Value (use FormattedTargetValue) - use a Text column

We do not recommend adding Site Columns to the Project Cache list.

It is recommended that the same or a similar name is used in the source and Project Cache to make understanding and maintenance easier.

Mapping a Column to the Project Cache

  1. Click the settings cog in the top right and click Site settings.
  2. Click Map Columns to the Project Cache under BrightWork Settings.

You can then add new column Mappings or manage existing Mappings.

The Project Cache Mapping page contains the following:

  1. Project Statement
    Option to select the source list to map to the Project Cache from. For this version, only Project Statement lists are supported.
  2. Project Statement Column
    Displays Project Statement column names, with their column types. The page shows Project Statement columns that are:
    1. Visible in Project Statement's forms or views
    2. Mapped to the Project Cache
    3. Not in the BrightWork reserved column list
  3. Project Cache Column
    Displays the Project Cache column name that is being mapped to.
  4. Metric Override

    When updating the Project Cache, if a mapping for both an active metric and a Project Statement value exist for the same Project Cache column, then the metric value is written to the Project Cache and not the Project Statement value. The Metric override column indicates that the mapping file is looking for both a metric value as well as a Project Statement value. A 'Yes' indicates that both mapping are possible. If the metric also exists in the site it will display the name of the Metric in brackets.

    A good example to help understand this is % Complete. In the Project Lite template the % Complete is filled in manually by the project manager using the Project Statement. In a Project Standard template, the % Complete is a metric and gets updated automatically. Both of these values are mapped to the one % Complete column in the Project Cache.

  5. Metric Active

    If a Metric Override is possible, and the metric exists in the site, this columns displays whether the metric is active or not.

    'Yes' means the metric exists in the site and is active. In this scenario, the metric's value will be written to the Project Cache and the Project Statement's value will not.

  6. Remove

    Clicking on Remove will delete the specific mapping from the mapping file. This can be useful if you make a mistake and need to remove an incorrect mapping.
    Caution: consider the impact on other sites in the site collection before you Remove a mapping.

To add a new column mapping:

  1. Click Add a new column mapping.
  2. Select the Project Statement column you wish to map from in the Select Source Column drop-down list.
    If no options are available, it means that there is no valid column in the Project Statement to map to the Project Cache.
  3. Select the Project Cache column you wish to map in the Project Cache Column drop-down list.
    If no options are available it means that there is no column in the Project Cache of the correct type available to map to.
  4. Click Save and your mapping will appear in the list of mappings.

When using the Project Cache Mapping page, only one person can make updates at a time. If more than one change is made at the same time then you may get an error saying: 'This version is not the latest version of the report'. Should this happen you need to refresh the Project Cache Mapping page and try your change again.

Manual Mapping

In order to map Projects Tracker items and Project Metrics to the Project Cache you still need to follow these more manual steps.

Add Column to Project Cache List

Follow the steps for Adding a Column to the Project Cache List above.

Update ProjectCacheGlobalSettings.xml

Update the global ProjectCacheGlobalSettings.xml file that maps the column to the Project Cache list column.

  1. Open the top level site of your site collection in SharePoint Designer 2013.
    For example, the URL should be something like: https://myserver/sites/mysitecollection
  2. Click All Files | _catalogs | bwConfigStore | ProjectCacheGlobalSettings.xml.
  3. Click Edit File to open the file for editing.
  4. Add a line of code to the appropriate list template.
    There are three scenarios:

Add a column in a Project Statement (710) or Projects Tracker (727) list

Example: <Field target="bwPCIProjectId" source="Title" />

With Project Statement (710) or the Projects Tracker (727) lists, you supply the following data:

    • Field target = the internal column name in the Project Cache list
    • source = the internal column name in the Project Statement or Project Tracker list

Add Metric Item

Example:
<Field target="bwPCIActualStartDate">
   <IfMetric id="ActualStartDate" source="MetricValue" />
</Field>

With Metric items, you supply the following:

    • Field target = the internal column name in the Project Cache list
    • ifMetric id = the Metric ID
    • source = the data type generated by the metric that you want to use
      Every metric can generate eight pieces of data that you can include:
      • Metric Value (use MetricValue)
      • Target Value (use TargetValue )
      • Indicator Icon (use IndicatorIcon)
      • Indicator Status (use IndicatorStatus)
      • Formatted Metric Value (use FormattedMetricValue)
      • Formatted Target Value (use FormattedTargetValue)
      • Formatted Warning Value (use FormattedWarningValue)
      • Formatted Danger Value (use FormattedDangerValue)

Map both a Metric value and a Project Statement Column to the same Project Cache Column

In certain situations, a piece of project data may be captured by a Metric in one template and in the Project Statement in another template. As when adding Project Metric Joins in the Report Editor, the Project Cache allows you to map both of these values to a single column in a report. If both values exist, active Metrics will take precedence.

Example:
<Field target="bwPCIActualStartDate" source="StartDateActual">
   <IfMetric id="ActualStartDate" source="MetricValue" />
</Field>

To map Metric items to a Project Cache column, you can map the following as needed:

    • Field target = the internal column name in the Project Cache list
    • source = the Metric ID or the internal name of the corresponding Project Statement column you want to map to
    • ifMetric id = the Metric ID
    • source = the data type generated by the metric
      Every metric can generate eight pieces of data that you can include:
      • Metric Value (use MetricValue)
      • Target Value (use TargetValue )
      • Indicator Icon (use IndicatorIcon)
      • Indicator Status (use IndicatorStatus)
      • Formatted Metric Value (use FormattedMetricValue)
      • Formatted Target Value (use FormattedTargetValue)
      • Formatted Warning Value (use FormattedWarningValue)
      • Formatted Danger Value (use FormattedDangerValue)
  1. Click Save.

The ProjectCacheGlobalSettings.xml is for specifying global mappings in a BrightWork site collection. Every site also has its own ProjectCacheSettings.xml file.

The Project Cache list supports the addition of calculated columns. You do not need to add these columns to the ProjectCacheGlobalSettings.xml file.

If the column you want to map has a different internal name in a certain site (for example, one project template uses a column called Organization and the rest use a column called Department), update the local ProjectCacheSettings.xml with the appropriate mapping information and it will override the global setting specified in the top level ProjectCacheGlobalSettings.xml file.

Changes to the ProjectCacheSettings.xml file are automatically included in Design Sync. This means that changes to this file should be made in the Template and synced, especially if Design Sync is being used.

Add Column to Cache Report

Finally, add the column to the Shared or Personal report that uses the cache.

Was this article useful?

Back to Top