To help explore and narrow the focus of data collected for Environmental, Social, and Governance (ESG) reporting, you can create queries based on ESG Program data , including:
- It topics, metrics, values, and tasks
- The custom dimensions available for its metric values
- The custom tags available for its metrics
Tip: You can connect these queries to Spreadsheets, such as to link an ESG Program's metric values to reporting outputs.
Add ESG Program as a query source
To use an ESG Program's data in a query:
- From the query's Sources panel, click New source, and select the data to include in the results:
- To include data about an ESG Program and its topics, metrics, values, or tasks, select ESG, ESG program.
- To include data about the custom dimensions available for an ESG Program's metric values, select ESG, Program dimensions.
- To include data about the custom tags available for an ESG Program's metrics, select ESG, Program tags.
Tip: For additional context around an ESG Program's metrics' dimensions or tags, add the ESG Program's data and its Program dimension or tag data as sources, and create a relationship to join them.
- Search for and select the ESG Program to use in the query, and click Add.
- From the Sources panel, specify how to use the data columns in the query:
- To use columns as results in Builder, select them, and then select Add to fields or drag them to the Fields tab.
- To use columns as filters in Builder, select them, and then select Add to filters or drag them to the Filters tab.
- To include a column in the query's statement in SQL, right-click it and select Add to query.
- To use columns as results in Builder, select them, and then select Add to fields or drag them to the Fields tab.
- Create the query as necessary.
Source columns
When you add ESG Program data as a source, you can include its data columns in the query results.
When you select Add program data as a source, the query can include these columns:
| Column | Description | Type |
|---|---|---|
| Program name | The ESG Program's name | Text |
| Program year | The year of the ESG Program's data | Integer |
| Metric name | The name of a metric within an ESG Program topic | Text |
| Metric code | The unique identifier generated for a metric | Text |
| Metric type | The type of value collected for a metric — Text, Currency, Number, Percent, or Date | Text |
| Metric description | The description entered for a metric | Text |
| Dimensions | The custom dimensions applied to a metric | Text |
| Value—text | The value entered for a metric, as text | Text |
| Value—number | The quantitative value entered for a metric, as a number | Decimal |
| Unit | For a metric with a type of Currency, Number, or Percent, its unit of measurement | Text |
| Supporting attachments | The filenames of attachments uploaded for a metric value
Note: If a value has multiple attachments, their filenames appear as a comma-separated list. The results include only uploaded attachments; no attached Workiva files or web addresses appear. |
Text |
| Source type | How a metric value is provided:
|
Text |
| Source file | For a metric with a connected source file, the name of the source | Text |
| Source section | For a metric with a connected source file, the name of the section in the source file where the value appears | Text |
| Frameworks | All related ESG Explorer content referenced by a metric
Tip: To supplement connected ESG content with additional details from ESG Explorer, add All Explorer data as a query source. |
Text |
| Task name | The name of a data collection task created for a metric's value | Text |
| Task assignee | The username of the workspace member assigned a task to collect the metric's value | Text |
| Task approver | The username of the workspace member assigned a task to approve the value collected for a metric
If a value has multiple approvers or approval steps:
|
Text |
| Task due date | The due date for a value's data collection task | Date |
| Task approver due date | The due date for a value's approval task
Tip: If a value has multiple approval steps, this is the due date for its final approval. |
Date |
| Task status | The overall status of a value's data collection, such as Not started, In progress, or Complete | Text |
| Task instructions | The instructions entered for a metric value's assignee and approver | Text |
| Notes | Any notes entered for a metric, such as for footnotes | Text |
| Topic | The name of a topic within the ESG Program | Text |
| Topic path | Where a topic appears in the ESG Program's outline, such as ["Climate","Greenhouse gas (GHG)","Direct GHG Emissions"]
|
Text |
| Custom tags | All custom tags applied to a topic metric | Text |
| Reporting year | The reporting year of the ESG Program's metric values | Integer |
| Reporting period start | The starting month of the reporting period of the ESG Program's metric values; for example, 1 for a yearly value, or 10 for a Q4 quarterly value |
Integer |
| Reporting period end | The ending month of the reporting period of the ESG Program's metric values; for example, 12 for a yearly value or a Q4 quarterly value |
Integer |
| Source location | For a metric value with a connected source spreadsheet, its cell's coordinates | Text |
When you select Add program dimension data as a source, the query can include these columns:
| Column | Description | Type |
|---|---|---|
| Dimension name | The name of a custom dimension set up for the ESG Program, such as Location or Gender | Text |
| Value ID | The unique ID assigned to a dimension's value, such as IA or NB | Text |
| Value name | The full name of a dimension's value, such as Iowa or Non-binary | Text |
| Dimension status | Whether a dimension is active or inactive | Text |
When you add select Add program tag data as a source, the query can include these columns:
| Column | Description | Type |
|---|---|---|
| Tag name | The name of a custom tag set up for the ESG Program | Text |
| Tag value | The name of the tag's value | Text |
Join ESG Program data with dimension or tag data
For additional context around an ESG Program's dimensions or tags, add the ESG Program's data and its dimension or tag data as sources, and then create a relationship to join them.
For example, to include context around an ESG Program's dimensions:
- In Sources, select Add program data and Add program dimension data, and choose the same ESG Program for both.
- In Fields, add the columns to include from each source.
At a minimum, include:- Dimensions from the ESG Program data
- Value ID from the dimension data
- In Relationships, add a left join:
- In the left source, select Calculated field, then apply a calculation based on the Dimensions column from the Field Properties panel:
JSON_FORMAT(JSON_ARRAY_GET(JSON_EXTRACT(JSON_PARSE( {1} ), '$.*'), 0))Tip: You can quickly add the
JSONfunctions to the calculation from the Formula menu. - In the right source, select the Value ID field of the dimension data.
- In the left source, select Calculated field, then apply a calculation based on the Dimensions column from the Field Properties panel:
- Click Save.
Include columns for specific frameworks or tags
When you use an ESG Program as a query source, you can include columns for all framework references and custom tags applied to its topics' metrics. For clarity, you can apply calculations to include columns for only specific frameworks or tags instead.
When you include the Frameworks column in a query, it provides all framework references applied to the ESG Program's topic metrics. For example:
| Frameworks |
|---|
| "gri_2020": ["305", "305-1"], "sasb_2021": ["EM-CM-110a", "EM-CM-110a.1"] |
For clarity, you can instead include a separate column for a framework's references, such as:
| GRI | SASB |
|---|---|
| ["305", "305-1"] | ["EM-CM-110a", "EM-CM-110a.1"] |
To include a column for a framework's references in a query's results, apply this calculation:
- From the Sources panel, right-click Calculation, and select Add to Fields.
- Under Fields, select Calculation.
- From the Field properties panel, enter the calculation's column header in the query results, such as "SASB" for references to Sustainability Accounting Board Standards (SASB).
- From the Sources panel, right-click the source Program's Frameworks column, and select Include in calculation.
- From the Field properties panel, enter the calculation:
JSON_EXTRACT({[field]}, '$.[framework]')- Replace
[field]with the Frameworks column's number under Included columns, such as1. - Replace
[framework]with the name and version of the framework of the references to include, such assasb_2021.
- Replace
- Click Apply.
When you include the Custom tags column in a query, it provides all tag values applied to the ESG Program's topic metrics. For example:
| Custom tags |
|---|
| {"CDP": ["C5.1", "C4.1a"], "Location": ["Ames", "Denver"]} |
For clarity, you can instead include a separate column for a tag, such as:
| CDP | Location |
|---|---|
| ["C5.1", "C4.1a"] | ["Ames", "Denver"] |
To include a column for a custom tag's values in a query's results, apply this calculation:
- From the Sources panel, right-click Calculation, and select Add to Fields.
- Under Fields, select Calculation.
- From the Field properties panel, enter the calculation's column header in the query results, such as "CDP" for metrics tagged with CDP questionnaire items.
- From the Sources panel, right-click the source Program's Custom tags column, and select Include in calculation.
- From the Field properties panel, enter the calculation, and replace
[tag]with the name of the custom tag of the values to include, such asCDP.
JSON_EXTRACT({[field]}, '$["tag"]')Tip: To remove the double-quotes from tag values in the query results, append the
W_JSON_ARRAY_JOINstatementW_JSON_ARRAY_JOIN( JSON_EXTRACT( {[field]} , '$["tag"]'), ',')). - Click Apply.
Update ESG Program query sources
As the ESG Program evolves and introduces new features, you may need to update its query source to include any related columns.
Tip: When an ESG Program has new columns to include, its query source displays a warning in Sources.
To update an ESG Program's query source:
- In Sources, right-click the source, and select Remove source.
- Add the ESG Program as a source again.
- Edit the query to include the ESG Program's columns.