From Wdata, you can create queries based on data in Sustainability Program, including:
- Topics, metrics, values, and tasks
- Custom dimensions available for metric values
- Custom tags available for metrics
With these queries, you can:
- Explore your sustainability data through a focused lens, using filters and parameters.
- Connect the results to a Spreadsheet, such as to export sustainability data to a file or provide source links to reporting outputs.
- Provide additional context for metrics' tags or dimensions.
Add Program as a query source
To use a 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 a Program and its topics, metrics, values, or tasks, select Sustainability, Sustainability program.
- To include data about the custom dimensions available for a Program's metric values, select Sustainability, Program dimensions.
- To include data about the custom tags available for a Program's metrics, select Sustainability, Program tags.
Tip: For additional context around a Program's metrics' dimensions or tags, add the Program's data and its dimension or tag data as sources, and create a relationship to join them.
-
Search for and select the 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.
For example, for a query with a Sustainability program source:Filter Returns Program year Only values collected for specific years Dimensions Only metrics with specific custom dimensions applied, and the values collected for those dimensions Task status Only tasks with specific data collection statuses -
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 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 Program's name | Text |
| Program year | The year of the Program's data | Integer |
| Metric name | The name of a metric within an 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 dimension name and value applied to a metric value, such as Tip: If your metrics support multiple dimensions, you can use Dimension name and Dimension value fields to return separate columns for dimensional names and values. |
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 Sustainability Explorer content referenced by a metric Tip: To supplement connected content with additional details from Sustainability Explorer, add Sustainability Explorer 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
Note: If a value has multiple assignees, they appear as a comma-separated list, such as
|
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 Program | Text |
| Topic path | Where a topic appears in the 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 Program's metric values | Integer |
| Reporting period start | The starting month of the reporting period of the 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 Program's metric values; for example, 12 for a yearly value or a Q4 quarterly value |
Integer |
| Source location | For a value with a connected source spreadsheet, its cell's coordinates | Text |
| Dimension name |
For a value with a dimension applied, the name of the dimension Note: If your metrics support multiple dimensions, the query can include up to seven Dimension name fields — one for each dimension possible for a metric. A dimension's position — such as in Dimension 1 name, Dimension 2 name, and so on — reflects its order in the metric's Configure metric panel. |
Text |
| Dimension value |
For a value with a dimension applied, its dimensional value Note: If your metrics support multiple dimensions, the query can include up to seven Dimension value fields — one for each dimension possible for a metric. A dimension's position — such as in Dimension 1 value, Dimension 2 value, and so on — reflects its order in the metric's Configure metric panel. |
Text |
Note: Your workspace may not yet support multiple dimensions for metric values. Stay tuned!
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 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 Program | Text |
| Tag value | The name of the tag's value | Text |
Join Program data with dimension or tag data
For additional context around a Program's dimensions or tags, add the Program's data and its dimensions or tags as sources, and then create a relationship to join them.
For example, to include context around a Program's dimensions:
- In Sources, select Sustainability program and Program dimensions, and choose the same Program for both.
- In Fields, add the columns to include from each source.
At a minimum, include:- Dimensions from the Program data
- Value ID from the dimensions
- 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.
-
- Click Save.
Include columns for specific frameworks or tags
When you use a Program as a query source, you can include columns for all framework references and custom tags applied to its 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 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 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 Program query sources
As the Program evolves and introduces new features, you may need to update its query source to include any related columns.
Tip: When a Program has new columns to include, its query source displays a warning in Sources.
To update a Program's query source:
- In Sources, right-click the source, and select Remove source.
- Add the Program as a source again.
- Edit the query to include the Program's columns as necessary.