|
Classic file types are no longer available for use as of January 2021. You can transition your classic files or download a PDF. Learn More

Workiva Support Center

Download data from external reporting in Microsoft® Sustainability Manager

Download data from external reporting in Microsoft® Sustainability Manager

  • Last updated on

microsoft.png

External reporting in Microsoft Sustainability Manager enables you to manage reporting across multiple standards and frameworks. Your organization can track details, including:

  • Assessments, or the instances of a specific standard for which you choose to disclose responses, such as the Corporate Sustainability Reporting Disclosures (CSRD) 2024
  • Dimensions used to categorize or break down the specific values — or facts — to report, such as geographic location, similar to dimensions for Program metrics

With the Workiva Platform, you can build Chains to automatically download assessments and dimensions from external reporting in Microsoft Sustainability Manager into Wdata Tables, based on a time period specified in a custom External reporting in MS Sustainability Spreadsheet. You can then use these Tables as the source for a Query or Spreadsheet connection, such as for sustainability reporting in the Workiva Platform.

Note: While you build multiple chains in these instructions, you'll run only one chain — which then automatically runs the others — to download data from Microsoft and update the spreadsheet.
Note: The External reporting in MS Sustainability spreadsheet used by these chains is provided by Workiva. If you don't have it but are interested, contact your Customer Success Manager for details.

Prerequisites

To build these chains, first set up these connectors in Chains:

Tip: All of the chains' commands use the default CloudRunner. No GroundRunners are needed.

To enable integration with Microsoft Dataverse, register an app with a Microsoft Entra ID, and note the following for the Microsoft Dynamics CRM connector:

  • The URL and tenant ID of the instance of external reporting in Microsoft Sustainability Manager to download from
  • The client ID and secret of the Microsoft Entra ID 

Lastly, note the IDs for the External reporting in MS Sustainability spreadsheet, its sheets, and its connected tables.

Build a chain to log data downloads

To start, build a chain to update the External reporting in MS Sustainability spreadsheet to track when emissions data uploads to Workiva.

ms-esg-log-chain.png

Step 1. Create the chain

  1. From Chains, click Create, and select Create chain.
    create-chain.png
  2. In Setup, enter a name of 3. External reporting in MS Sustainability log and a description to help explain that the chain logs when data uploads from external reporting in Microsoft Sustainability Manager.
  3. Click Save.

Step 2. Start with a Runtime Inputs event

  1. From Chain events, move Runtime inputs to Start.
  2. Select the Runtime inputs event, and click Edit.
  3. In Basic info, enter a name and description to help identify the event.
    msm-runtime-inputs.png
  4. In Variables, add TextField inputs with these display names:
    • Range
    • Spreadsheet ID
    • Control sheet ID
    • Chain run log ID
    • Refresh year
    Tip: Leave the variables' default values blank; they'll be generated when you run the 1. MS Sustainability refresh chain created later.
  5. Select Required for all variables, and click Save.

Step 3. Add commands to get time period from Control sheet

To identify the time period of the emissions data to download from Microsoft:

  1. From Available connectors, select File utilities, and move Create file to the canvas.
  2. Drag a link from Runtime inputs to Create file.
  3. Select the Create file command, and click Edit.
  4. In Basic info, enter a name and description to identify the command.
    ms-project-esg-create-file.png
  5. Enter the command's properties, and click Save:
    Property Value
    Connector Select the File utilities connector to use.
    Text Enter the string to update in Control sheet:
    1. Enter ,,Data last updated on .
    2. Select the System.DateTime variable from Runtime.
  6. From Available connectors, select Workiva, and move Write sheet data to the canvas.
  7. Drag a link from Create file to Write sheet data.
  8. Select the Write sheet data command, and click Edit.
  9. In Basic info, enter a name and description to help identify the command.
  10. Enter the command's properties, and click Save:
    ms-esg-write-sheet-data.png
    Property Value
    Connector Select the Workiva connector to use.
    Spreadsheet ID Select the Spreadsheet ID runtime input from Trigger.
    Sheet ID/name Select the Control sheet ID runtime input from Trigger.
    Data file Select the Created file output from Create file.
    Delimiter Enter a comma (,).
    Range Select the Range runtime input from Trigger.
    Use async Clear this checkbox.
    Use Platform API Select this checkbox.

Step 4. Add commands to update the Chain run log

To update the Chain run log sheet when data downloads from Microsoft:

  1. From Available connectors, select Workiva, and move Get sheet data to the canvas.
  2. Drag a link from Runtime inputs to Get sheet data.
  3. Select the Get sheet data command, and click Edit.
  4. In Basic info, enter a name and description to help identify the command.
  5. Enter the command's properties, and click Save:
    ms-esg-get-sheet-data.png
    Property Value
    Connector Select the same Workiva connector as earlier.
    Spreadsheet ID Select the Spreadsheet ID runtime input from Trigger.
    Sheet ID/name Select the Chain run log ID runtime input from Trigger.
    Region Enter A1:.
    Value style Select Calculated.
    Revision Enter -1.
  6. From Available connectors, select File utilities, and move Create file to the canvas.
  7. Drag a link from Get sheet data to Create file.
  8. Select the Create file command, and click Edit.
  9. In Basic info, enter a name and description to help identify that the command creates the log entry.
    ms-esg-create-log-file.png
  10. Enter the command's properties, and click Save:
    Property Value
    Connector Select the same File utilities connector as earlier.
    Text Enter the string to update the columns of the Chain run log sheet:
    1. Enter the column headers: Date,Details, and press Enter.
    2. Select the System.DateTime variable from Runtime.
    3. Click the System.DateTime variable, and add a Parse Date/Time transformation:
      msm-parse-date-time.png
      • Select ISO Extended (Platform Standard).
      • In Output date format, enter %Y-%m-%d.
      • Select the input and output date timezones.
    4. Enter ,data for .
    5. Select the Refresh year runtime input from Trigger.
    6. Enter  was refreshed.
  11. From Available connectors, select Tabular transformation, and move Advanced query to the canvas.
  12. Drag a link from Create file to Advanced query.
  13. Select the Advanced query command, and click Edit.
  14. In Basic info, enter a name and description to identify the command.
  15. Enter the command's properties, and click Save:
    ms-esg-advanced-query.png
    Property Value
    Connector Select the Tabular transformation connector to use.
    Tables Add two tables to use in the query:
    1. For the first table:
      • In File, select the Data output from Get sheet data.
      • In Table name, enter a.
    2. For the second table:
      • In File, select the Created file output from the preceding Create file.
      • In Table name, enter b.
    Query Enter the query to run: 
    select * from a
union all
select * from b
    Input delimiter Select Comma.
    Output delimiter Select Comma.
    Preview results Select this checkbox.
  16. From Available connectors, select Workiva, and move Overwrite sheet data to the canvas.
  17. Drag a link from Advanced query to Overwrite sheet data.
  18. Select the Overwrite sheet data command, and click Edit.
  19. In Basic info, enter a name and description to identify the command.
  20. Enter the command's properties, and click Save:
    ms-esg-overwrite-sheet.png
    Property Value
    Connector Select the same Workiva connector as earlier.
    Spreadsheet ID Select the Spreadsheet ID runtime input from Trigger.
    Sheet ID/name Select the Chain run log ID runtime input from Trigger.
    Data file Select the Result output from Advanced query.
    Delimiter Enter a comma (,).
    Start cell Enter A1.
    Use async Select this checkbox.
    Use Platform API Clear this checkbox.
  21. Click Publish, enter a note about its publication, and click Publish.

Build chains from the Load Data to Wdata template

Next, use the Load Data to Wdata template to build chains to update the External reporting in MS Sustainability spreadsheet's connected tables with the data downloaded from Microsoft. The Load Data to Wdata template creates chains that work together to:

  • Determine whether the dataset already exists in the table
  • Update the table with the latest dataset

load-data-wdata.png

To create the chains from Templates, open the Load Data to Wdata | Primary chain template, and click New chain.

Build chain to iterate over downloaded data

To ensure your download includes all available data, build a chain to iterate over and compile the assessments and dimensions for the specified time period.

ms-esg-compile.png

Step 1. Create the chain

  1. From Chains, click Create, and select Create chain.
  2. In Setup, enter a name of 2. External reporting in MS Sustainability compile and a description to help explain that the chain compiles the data downloaded from external reporting in Microsoft Sustainability Manager.
    ms-esg-compile-settings.png
  3. Add the variables:
    Name Value
    Spreadsheet ID Enter the ID for the External reporting in MS Sustainability spreadsheet.
    Control sheet ID Enter the ID for the Control sheet section of the spreadsheet.
    Chain run log ID Enter the ID for the Chain run log section of the spreadsheet.
    Dimension table ID Enter the ID for the table connected to the spreadsheet that stores dimensions from external reporting in Microsoft Sustainability Manager.
  4. Add a dynamic variable named nextLink, and click Save.

Step 2. Start with events to set variables

To set the chain's variables:

  1. From Trigger event, move Runtime inputs to Start.
  2. Select the Runtime inputs event, and click Edit.
  3. In Basic info, enter a name and description to help identify the event.
  4. In Variables, add these TextField inputs:
    ms-esg-compile-runtime.png
    • nextLink
    • path
  5. Select Required for both variables, and click Save.
  6. From Chain events, move Set dynamic chain variables to the canvas.
  7. Drag a link from Runtime inputs to Set dynamic chain variables.
  8. Select the Set dynamic chain variables event, and click Edit.
  9. In Basic info, enter a name and description to help identify the event.
    ms-esg-set-dynamic-variable.png
  10. Set up the action, and click Save:
    Dynamic variable Value
    Select nextLink. Select the nextLink runtime input from Trigger.

Step 3. Add command group to iterate over dimensions

To compile the dimensions downloaded from external reporting in Microsoft Sustainability Manager:

  1. Move Command group to the canvas.
  2. Drag a link from Set dynamic chain variables to the group's In, then click Edit for the group.
  3. In Iterators, enable Iterate.
    ms-esg-group-iterator.png
  4. In Select modifier type, select Repeat until.
  5. Add this rule, and click Save:
    Data type to test Data Operation to test
    Select String. Select the nextLink runtime input from Trigger. Select Is blank.
  6. From Available connectors, select Microsoft Dynamics CRM, and move Retrieve to the canvas.
  7. Drag a link from Group start to Retrieve, then click Edit for the command.
  8. In Basic info, enter a name and description to help identify that the command retrieves dimension data.
  9. Enter the command's properties, and click Save:
    ms-esg-compile-retrieve.png
    Property Value
    Connector Select the Microsoft Dynamics CRM connector to use.
    Path Select the Path runtime input from Trigger.
    Query params Select the nextLink runtime input from Trigger.
    Custom headers Add this header:
    • In Key, enter Prefer.
    • In Value, enter:
      odata.maxpagesize=100,odata.include-annotations="OData.Community.Display.V1.FormattedValue"
    Max pages Enter 1.
    Output schema Enter this sample response: 
    {"@odata.context": "string","value": [{}],"@odata.nextLink": "string"}
  10. From Chain events, move Conditional to the canvas.
  11. Drag a link from Retrieve to Conditional.
  12. Select the Conditional event, and click Edit.
  13. In Basic info, enter a name and description to help identify the event.
    ms-esg-conditional-path.png
  14. In Conditions, add this rule,  and click Save:
    Data type to test Data Operation to test
    Select String. Select the Path runtime input from Trigger. Select =, and enter msdyn_dimensionlinks.
  15. From Available connectors, select JSON, and move Array to CSV to the canvas.
  16. Drag a link from Conditional to Array to CSV.
  17. Select the Array to CSV command, and click Edit.
  18. In Basic info, enter a name and description to help identify that command converts dimensions.
  19. Enter the command's properties:
    ms-esg-array-to-csv.png
    Property Value
    Connector Select the JSON connector to use.
    JSON data Select Value the Retrieved data output of the Retrieve command.
    Multi-value delimiter Enter a comma (,).
    Preview result Select this checkbox.
    Delimiter Select Comma.
  20. Add these columns, and click Save:
    Column name JSONPath
    dimension_name 
    .msdyn_name
    fact_name 
    .msdyn_artifactid_msdyn_esgfact.msdyn_name
    dimension_member 
    ."_msdyn_dimensionmember_value@OData.Community.Display.V1.FormattedValue"
    fact_numeric_value 
    .msdyn_artifactid_msdyn_esgfact."msdyn_numericalvalue@OData.Community.Display.V1.FormattedValue"
    fact_boolean_value 
    .msdyn_artifactid_msdyn_esgfact.msdyn_booleanvalue
    fact_text_value 
    .msdyn_artifactid_msdyn_esgfact.msdyn_textvalue
    fact_rich_text_value 
    .msdyn_artifactid_msdyn_esgfact.msdyn_richtextvalue
    fact_approval_policy 
    .msdyn_artifactid_msdyn_esgfact."_msdyn_approvalpolicy_value@OData.Community.Display.V1.FormattedValue"
    concept 
    .msdyn_artifactid_msdyn_esgfact."_msdyn_concept_value@OData.Community.Display.V1.FormattedValue"
    fact_status 
    .msdyn_artifactid_msdyn_esgfact."msdyn_factstatus@OData.Community.Display.V1.FormattedValue"
    fact_unit 
    .msdyn_artifactid_msdyn_esgfact."_msdyn_unit_value@OData.Community.Display.V1.FormattedValue"
    fact_period 
    .msdyn_artifactid_msdyn_esgfact."_msdyn_period_value@OData.Community.Display.V1.FormattedValue"
  21. Select Array to CSV, and click Copy.
  22. Drag a link from Conditional to the new Array to CSV, then double-click its link and select Error.
    conditional-error-link.png
  23. Select the new Array to CSV command, and click Edit.
  24. In Basic info, enter a name and description to help identify that the command converts assessments.
  25. In Command properties, replace the columns with these, and click Save.
    Column name JSONPath
    assessment_name 
    .msdyn_name
    assessment_concept 
    .msdyn_requirementconcept."_msdyn_conceptid_value@OData.Community.Display.V1.FormattedValue"
    assessment_concept_create_date 
    .createdon
    fact_name 
    .msdyn_esgfact_msdyn_esgfact.msdyn_name
    fact_period 
    .msdyn_artifactid_msdyn_esgfact."msdyn_period_value@OData.Community.Display.V1.FormattedValue"
    fact_status 
    .msdyn_artifactid_msdyn_esgfact."msdyn_factstatus@OData.Community.Display.V1.FormattedValue"
    fact_text_value 
    .msdyn_artifactid_msdyn_esgfact.msdyn_textvalue
    fact_rich_text_value 
    .msdyn_artifactid_msdyn_esgfact.msdyn_richtextvalue
    fact_approval_policy 
    .msdyn_artifactid_msdyn_esgfact."_msdyn_approvalpolicy_value@OData.Community.Display.V1.FormattedValue"
    concept_status 
    .msdyn_requirementconcept."statecode@OData.Community.Display.V1.FormattedValue"
    concept_required 
    .msdyn_requirementconcept."msdyn_required@OData.Community.Display.V1.FormattedValue"
    fact_numeric_value 
    .msdyn_artifactid_msdyn_esgfact."msdyn_numericalvalue@OData.Community.Display.V1.FormattedValue"
  26. From Available connectors, select Tabular transformation, and move Stack files to the canvas.
  27. Drag links from both Array to CSV commands to Stack files.
  28. Select the Stack files command, and click Edit.
  29. In Basic info, enter a name and description to help identify the command.
    ms-esg-compile-stack.png
  30. Enter the command's properties, and click Save:
    Property Value
    Connector Select the Tabular transformation connector to use.
    Delimiter Select Comma.
    Input files Specify the files to stack:
    1. Select Previous stack files output from Stack files.
    2. Enter a comma (,).
    3. Select the Converted file output of the dimensions' Array to CSV.
    Preview results Select this checkbox.
  31. From Chain events, move another Conditional to the canvas.
  32. Drag a link from Stack files to Conditional.
  33. Select the Conditional event, and click Edit.
  34. In Basic info, enter a name and description to help identify the event.
    ms-esg-conditional-nextquery.png
  35. In Conditions, add this rule,  then click Save:
    Data type to test Data Operation to test
    Select String. Select the Next query output of the Retrieve command. Select Is not blank.
  36. From Chain events, move another Set dynamic chain variables to the canvas.
  37. Drag a link from Conditional to Set dynamic chain variables.
  38. Select the Set dynamic chain variables event, and click Edit.
  39. In Basic info, enter a name and description to help identify the event.
    ms-esg-set-dynamic-nextlink.png
  40. Set up this action, and click Save:
    Dynamic variable Value
    Select nextLink. Select the Next query output of the Retrieve command.
  41. From Chain events, move Exit group to the canvas.
  42. Drag a link from Conditional to Exit group, then double-click the link and select Error.

Step 4. Specify the chain's output

To use the chain's compiled result when you run the chain to refresh the data from external reporting in Microsoft Sustainability Manager:

  1. Click Settings.
  2. In Chain outputs, click Add chain output.
    ms-esg-compile-output.png
  3. In Name, enter stackedFiles.
  4. Select the Stack files command and its Stack files output.
  5. Click Save.
  6. Click Publish, then enter a note about the chain's publication, and click Publish.

Build chain to download data from external reporting in Microsoft Sustainability Manager

Finally, build the chain to download assessments and dimensions from external reporting in Microsoft Sustainability Manager.

ms-esg-refresh.png

Step 1. Create the chain

  1. From Chains, click Create, and select Create chain.
  2. In Setup, enter a name of 1. External reporting in MS Sustainability refresh and a description to help explain that the chain downloads data from external reporting in Microsoft Sustainability Manager.
    ms-project-esg-refresh-settings.png
  3. Add the variables:
    Name Value
    Spreadsheet ID Enter the ID for the External reporting in MS Sustainability spreadsheet.
    Control sheet ID Enter the ID for the Control sheet section of the spreadsheet.
    Chain run log ID Enter the ID for the Chain run log section of the spreadsheet.
    Dimension table ID Enter the ID for the table connected to the spreadsheet that stores dimensions from external reporting in Microsoft Sustainability Manager.
    Assessment table ID Enter the ID for the table connected to the spreadsheet that stores assessments from external reporting in Microsoft Sustainability Manager.
  4. Add a dynamic variable named nextLink, and click Save.

Step 2. Add commands to download data for time period

To download data from Microsoft, based on the time period specified in the External reporting in MS Sustainability spreadsheet:

  1. From Available connectors, select Workiva, and move Get sheet data to Start.
  2. Select the Get sheet data command, and click Edit.
  3. In Basic info, enter a name and description to help identify the command.
  4. Enter the command's properties, and click Save:
    ms-esg-get-sheet-data-refresh.png
    Property Value
    Connector Select the same Workiva connector as earlier.
    Spreadsheet ID Select the Spreadsheet ID variable from Chain.
    Sheet ID/name Select the Control sheet ID variable from Chain.
    Region Enter B11:C12.
    Value style Select Calculated.
    Revision Enter -1.
  5. From Available connectors, select Tabular transformation, and move Advanced query to the canvas.
  6. Drag a link from Get sheet data to Advanced query.
  7. Select the Advanced query command, and click Edit.
  8. In Basic info, enter a name and description to help identify the command.
  9. Enter the command's properties, and click Save:
    ms-esg-advanced-query-refresh.png
    Property Value
    Connector Select the same Tabular transformation connector as earlier.
    Tables Add this table:
    • In File, select the Data output of Get sheet data.
    • In Table name, enter a.
    Query Enter select `Period` from a.
    Input delimiter Select Comma.
    Output delimiter Select Comma.
    Preview results Select this checkbox.
  10. From Available connectors, select Tabular transformation, and move Extract value to the canvas.
  11. Drag a link from Advanced query to Extract value.
  12. Select the Extract value command, and click Edit.
  13. In Basic info, enter a name and description to help identify the command.
  14. Enter the command's properties, and click Save:
    ms-esg-extract-value-refresh.png
    Property Value
    Connector Select the same Tabular Transformation connector as earlier.
    Input file Select the Result output of Advanced query.
    Column index Enter 1.
    Delimiter Select Comma.
    Row index Enter 2.
  15. From Available connectors, select Microsoft Dynamics CRM, and move Retrieve to the canvas.
  16. Drag a link from Extract value to Retrieve.
  17. Select the Retrieve command, and click Edit.
  18. In Basic info, enter a name and description to help identify that the command downloads a period's data.
  19. Enter the command's properties, and click Save:
    Property Value
    Connector Select the same Microsoft Dynamics CRM connector as earlier.
    Path Enter msdyn_rangeperiods.
    Query params Build the query parameters to apply:
    1. Enter: 
      $select=statecode,msdyn_rangeperiodid,msdyn_name,msdyn_from,msdyn_to&$filter=(msdyn_name eq '
    2. Select the Value output of Extract value.
    3. Enter ')&$orderby=msdyn_name asc.
    Custom headers Add this header:
    • In Key, enter Prefer.
    • In Value, enter 
      odata.maxpagesize=100,odata.include-annotations="OData.Community.Display.V1.FormattedValue"
    Max pages Enter 1.
    Output schema Enter this sample response: 
    {"@odata.context": "string","value": [{}],"@odata.nextLink": "string"}

Step 3. Add commands to download dimensions

  1. From Available connectors, select Microsoft Dynamics CRM, and move another Retrieve to the canvas.
  2. Drag a link between the two Retrieve commands.
  3. Select the new Retrieve command, and click Edit.
  4. In Basic info, enter a name and description to help identify that the command downloads dimensions.
  5. Enter the command's properties, and click Save:
    ms-esg-refresh-retrieve.png
    Property Value
    Connector Select the same Microsoft Dynamics CRM connector as earlier.
    Path Enter msdyn_dimensionlinks.
    Query params Build the query parameters to apply:
    1. Enter: 
      $expand=msdyn_artifactid_msdyn_esgfact($select=_msdyn_approvalpolicy_value,_msdyn_approvalrecord_value,msdyn_booleanvalue,_msdyn_concept_value,msdyn_factstatus,msdyn_name,msdyn_numericvalue,_msdyn_period_value,msdyn_richtextvalue,statecode,msdyn_textvalue,_msdyn_unit_value)&$filter=(msdyn_artifactid_msdyn_esgfact/_msdyn_period_value eq
    2. Select the Retrieved data output of the previous Retrieve command.
    3. Click Retrieved data, then add a Get value from JSON transformation:
      • In Output, select Text.
      • In Value, enter value, 0, and msdyn_rangeperiodid.
    4. Enter )&$orderby=msdyn_name asc.
    Custom headers Add this header:
    • In Key, enter Prefer.
    • In Value, enter 
      odata.maxpagesize=100,odata.include-annotations="OData.Community.Display.V1.FormattedValue"
    Max pages Enter 1.
    Output schema Enter this sample response: 
    {"@odata.context": "string","value": [{}],"@odata.nextLink": "string"}
  6. From Available connectors, select JSON, and move Array to CSV to the canvas.
  7. Drag a link from Retrieve to Array to CSV.
  8. Select the Array to CSV command, and click Edit.
  9. In Basic info, enter a name and description to help identify that the command downloads dimensions.
  10. Enter the command's properties:
    ms-esg-refresh-extract-dimension.png
    Property Value
    Connector Select the same JSON connector as earlier.
    JSON data Select Value from the Retrieved data output of the preceding Retrieve command.
    Multi-value delimiter Enter a comma (,).
    Preview result Select this checkbox.
    Delimiter Select Comma.
  11. Add these columns, and click Save:
    Column name JSONPath
    dimension_name 
    .msdyn_name
    fact_name 
    .msdyn_artifactid_msdyn_esgfact.msdyn_name
    dimension_member 
    ."_msdyn_dimensionmember_value@OData.Community.Display.V1.FormattedValue"
    fact_numeric_value 
    .msdyn_artifactid_msdyn_esgfact."msdyn_numericalvalue@OData.Community.Display.V1.FormattedValue"
    fact_boolean_value 
    .msdyn_artifactid_msdyn_esgfact.msdyn_booleanvalue
    fact_text_value 
    .msdyn_artifactid_msdyn_esgfact.msdyn_textvalue
    fact_rich_text_value 
    .msdyn_artifactid_msdyn_esgfact.msdyn_richtextvalue
    fact_approval_policy 
    .msdyn_artifactid_msdyn_esgfact."_msdyn_approvalpolicy_value@OData.Community.Display.V1.FormattedValue"
    concept 
    .msdyn_artifactid_msdyn_esgfact."_msdyn_concept_value@OData.Community.Display.V1.FormattedValue"
    fact_status 
    .msdyn_artifactid_msdyn_esgfact."msdyn_factstatus@OData.Community.Display.V1.FormattedValue"
    fact_unit 
    .msdyn_artifactid_msdyn_esgfact."_msdyn_unit_value@OData.Community.Display.V1.FormattedValue"
    fact_period 
    .msdyn_artifactid_msdyn_esgfact."_msdyn_period_value@OData.Community.Display.V1.FormattedValue"
  12. From Chain events, move Conditional to the canvas.
  13. Drag a link from Array to CSV to Conditional.
  14. Select the Conditional event, and click Edit.
  15. In Basic info, enter a name and description to help identify the event.
    ms-esg-refresh-conditional.png
  16. In Conditions, enter this rule, then click Save:
    Data type to test Data Operation to test
    Select String. Select the Next query output of the preceding Retrieve command. Select Is not blank.
  17. From Chain events, move Run chain to the canvas.
  18. Drag a link from Conditional to Run chain.
  19. Select the Run chain event, and click Edit.
  20. In Basic info, enter a name and description to help identify that the event runs the chain to iterate over the downloaded data.
    ms-esg-refresh-run-compile.png
  21. In Run chain, select the 2. External reporting in MS Sustainability compile chain created earlier.
  22. Enter the chain's runtime inputs, and click Save:
    Input Value
    nextLink Select the Next query output of the preceding Retrieve command.
    path Enter msdyn_dimensionlinks.
  23. From Available connectors, select Tabular Transformation, and move Stack files to the canvas.
  24. Drag a link from Run chain to Stack files.
  25. Select the Stack files command, and click Edit.
  26. In Basic info, enter a name and description to help identify the command.
    ms-esg-stack-files.png
  27. Enter the command's properties, and click Save:
    Property Value
    Connector Select the same Tabular Transformation connector as earlier.
    Delimiter Select Comma.
    Input files Specify the files to stack:
    1. Select the Converted file output from the preceding Array to CSV.
    2. Enter a comma (,).
    3. Select Run chain - Stacked files iteration - Stacked files output from Run chain.
    Preview result Select this checkbox.
  28. From Chain events, move another Run chain to the canvas.
  29. Drag a link from Stack files to Run chain.
  30. Select the Run chain event, and click Edit.
  31. In Basic info, enter a name and description to help identify that the event runs the chain to load data into Wdata.
  32. In Run chain, select the Load Data to Wdata | Primary chain created from the chain template.
    ms-esg-run-load-wdata.png
  33. Enter the chain's runtime inputs, and click Save:
    Input Value
    Table ID Select the Dimension table ID variable of Chain.
    File name Build the name for the dataset to upload to Wdata:
    1. Enter MS_Sustainability_dimensions_.
    2. Select the Value output from Extract value.
    3. Enter .csv.
    Data file Select Stack files output from Stack files.
    Load method Select Replace dataset.
    Rollback Clear this checkbox.
  34. From Chain events, move another Run chain to the canvas.
  35. Drag a link between the two Run chain events.
  36. Select the new Run chain event, and click Edit.
  37. In Basic info, enter a name and description to help identify that the event runs the chain to log the download.
    ms-esg-run-log.png
  38. In Run chain, select the 3. External reporting in MS Sustainability log chain created earlier.
  39. Enter the chain's runtime inputs, and click Save:
    Input Value
    Range Enter B5:E5.
    Sheet ID Select the Control sheet ID variable from Chain.
    Audit log sheet Select the Chain run log ID variable from Chain.
    Refresh year Select the Value output from Extract value.
  40. Select the Run chain event that runs the chain to load data into Wdata, and click Copy.
  41. Drag a link from Conditional to the new Run chain event, then double-click the link and click Error.
  42. Select the new Run chain event, and click Edit.
  43. In Data file, select the Converted file output from Array to CSV.
    ms-esg-run-wdata-copy.png
  44. Click Save.
  45. Select the Run chain event that runs the chain to log the download, and click Copy.
  46. Drag a link from the previous Run chain event and the new Run chain event.

Step 4. Add commands to download assessments

  1. From Available connectors, select Microsoft Dynamics CRM, and move another Retrieve to the canvas.
  2. Drag a link from the Retrieve command for the period's data to the new Retrieve command.
  3. Select the new Retrieve command, and click Edit.
  4. In Basic info, enter a name and description to help identify that the command downloads assessments.
  5. Enter the command's properties, and click Save:
    ms-esg-retrieve-assessments.png
    Property Value
    Connector Select the same Microsoft Dynamics CRM connector as earlier.
    Path Enter msdyn_assessmentrequirementfacts.
    Query params Build the query parameters to apply:
    1. Enter: 
      $select=msdyn_name,createdon&$expand=msdyn_esgfact_msdyn_esgfact($select=msdyn_name,msdyn_numericvalue,msdyn_richtextvalue,msdyn_textvalue,_msdyn_unit_value,msdyn_factstatus,_msdyn_approvalpolicy_value,msdyn_booleanvalue,msdyn_factstatus,msdyn_name,msdyn_numericvalue,_msdyn_period_value,msdyn_richtextvalue,msdyn_textvalue,_msdyn_unit_value),msdyn_requirementconcept($select=msdyn_name,msdyn_required,_msdyn_conceptid_value,msdyn_name,statecode,msdyn_required)&$filter=(msdyn_esgfact_msdyn_esgfact/_msdyn_period_value eq
    2. Select the Retrieved data output of the previous Retrieve command.
    3. Click Retrieved data, then add a Get value from JSON transformation:
      ms-esg-transform-retrieved.png
      • In Output, select Text.
      • In Value, enter value, 0, and msdyn_rangeperiodid.
    4. Enter )&$orderby=msdyn_name asc.
    Custom headers Add this header:
    • In Key, enter Prefer.
    • In Value, enter 
      odata.maxpagesize=100,odata.include-annotations="OData.Community.Display.V1.FormattedValue"
    Max pages Enter 1.
    Output schema Enter this sample response: 
    {"@odata.context": "string","value": [{}],"@odata.nextLink": "string"}
  6. From Available connectors, select JSON, and move Array to CSV to the canvas.
  7. Drag a link from Retrieve to Array to CSV.
  8. Select the Array to CSV command, and click Edit.
  9. In Basic info, enter a name and description to help identify that the command downloads dimensions.
  10. Enter the command's properties:
    ms-esg-array-assessments.png
    Property Value
    Connector Select the same JSON connector as earlier.
    JSON data Select Value from the Retrieved data output of the preceding Retrieve command.
    Multi-value delimiter Enter a comma (,).
    Preview result Select this checkbox.
    Delimiter Select Comma.
  11. Add these columns, and click Save:
    Column name JSONPath
    assessment_name 
    .msdyn_name
    assessment_concept 
    .msdyn_requirementconcept."_msdyn_conceptid_value@OData.Community.Display.V1.FormattedValue"
    assessment_concept_create_date 
    .createdon
    fact_name 
    .msdyn_esgfact_msdyn_esgfact.msdyn_name
    fact_period 
    .msdyn_artifactid_msdyn_esgfact."msdyn_period_value@OData.Community.Display.V1.FormattedValue"
    fact_status 
    .msdyn_artifactid_msdyn_esgfact."msdyn_factstatus@OData.Community.Display.V1.FormattedValue"
    fact_text_value 
    .msdyn_artifactid_msdyn_esgfact.msdyn_textvalue
    fact_rich_text_value 
    .msdyn_artifactid_msdyn_esgfact.msdyn_richtextvalue
    fact_approval_policy 
    .msdyn_artifactid_msdyn_esgfact."_msdyn_approvalpolicy_value@OData.Community.Display.V1.FormattedValue"
    concept_status 
    .msdyn_requirementconcept."statecode@OData.Community.Display.V1.FormattedValue"
    concept_required 
    .msdyn_requirementconcept."msdyn_required@OData.Community.Display.V1.FormattedValue"
    fact_numeric_value 
    .msdyn_artifactid_msdyn_esgfact."msdyn_numericalvalue@OData.Community.Display.V1.FormattedValue"
  12. Select the Conditional event, and click Copy.
  13. Drag a link from Array to CSV to the new Conditional event.
  14. Select the Conditional event, and click Edit.
  15. In Conditions, delete the existing Next query output, then select Next query from the Retrieve command for assessments.
    ms-esg-conditional-assessments.png
  16. Click Save.
  17. From Chain events, move Run chain to the canvas.
  18. Drag a link from Conditional to Run chain.
  19. Select the Run chain event, and click Edit.
  20. In Basic info, enter a name and description to identify that the event runs the chain to iterate over the downloaded data.
    ms-esg-run-compile-assessments.png
  21. In Run chain, select the 2. External reporting in MS Sustainability compile chain created earlier.
  22. Enter the chain's runtime inputs, and click Save:
    Input Value
    nextLink Select the Next query output of the preceding Retrieve command.
    path Enter msdyn_assessmentrequirementfacts.
  23. From Available connectors, select Tabular Transformation, and move Split value to the canvas.
  24. Drag a link from Run chain to Split value.
  25. Select the Split value command, and click Edit.
  26. In Basic info, enter a name and description to help identify the command.
  27. Enter the command's properties, and click Save:
    ms-esg-split-value.png
    Property Value
    Connector Select the same JSON connector as earlier.
    Input file Select the Converted file output from Array to CSV.
    Column name Enter assessment_name.
    Delimiter Select Comma.
    New headers Enter these headers, in this order:
    • assessment_name
    • assessment_det
    • assessment_requirement_name
    • concept_name
    • fct
    Value delimiter Enter a colon (:).
    Discard column Select this checkbox.
  28. From Available connectors, select Tabular transformation, and move Advanced query to the canvas.
  29. Drag a link from Split value to Advanced query.
  30. Select the Advanced query command, and click Edit.
  31. In Basic info, enter a name and description to help identify the command.
  32. Enter the command's properties, and click Save:
    ms-esg-advanced-query-assessments.png
    Property Value
    Connector Select the same Tabular Transformation connector as earlier.
    Tables Add this table:
    • In File, select Split values output from the Split value command.
    • In Table name, enter a.
    Query Enter 
    select
    assessment_name,
    assessment_requirement_name,
    assessment_concept,
    fact_name,
    fact_period
    from a
    Input delimiter Select Comma.
    Output delimiter Select Comma.
    Preview results Clear this checkbox.
  33. From Chain events, move Run chain to the canvas.
  34. Drag a link from Advanced query to Run chain.
  35. Select the Run chain event, and click Edit.
  36. In Basic info, enter a name and description to help identify that the event runs the chain to load data into Wdata.
  37. In Chain to run, select the Load Data to Wdata | Primary chain created from the chain template.
    ms-esg-load-assessments.png
  38. Enter the chain's runtime inputs, and click Save:
    Input Value
    Table ID Select the Assessment table ID variable of Chain.
    File name Build the name for the dataset to upload to Wdata:
    1. Enter MS_Sustainability_assessments_.
    2. Select the Value output from Extract value.
    3. Enter .csv.
    Data file Select the Result output from Advanced query.
    Load method Select Replace dataset.
    Rollback Clear this checkbox.
  39. Select the Run chain event that runs the chain to log the download, and click Copy.
  40. Drag a link from the previous Run chain event and the new Run chain event.
  41. Select the Split value command, and click Copy.
  42. Drag a link from the second Conditional to the new Split value command, then double-click the link and click Error.
  43. Select the Advanced query command, and click Copy.
  44. Drag a link from the previous Split value command to the new Advanced query command.
  45. Select the Advanced query command, and click Edit.
  46. In Tables, select Split values output from the previous Split value command.
    ms-esg-query-assessments-copy.png
  47. Click Save.
  48. Select the Run chain event that runs the chain to load data into Wdata, and click Copy.
  49. Drag a link from Advanced query to Run chain.
  50. Select the Run chain event, and click Edit.
  51. In Data file, select the Result output from the previous Advanced query command.
    ms-esg-run-load-copy.png
  52. Select the Run chain event that runs the chain to log the download, and click Copy.
  53. Drag a link from the previous Run chain event and the new Run chain event.
  54. Click Publish, enter a note about the chain's publication, and click Publish.

Run the chains

To refresh the External reporting in MS Sustainability spreadsheet and tables with the latest data:

  1. From the Control sheet of the spreadsheet, select the time period of the data to download.
    ms-esg-lead-sheet.png
  2. From Chains, select the 1. MS Sustainability refresh chain, and click Execute and Run chain.
    ms-esg-execute.png
    Tip: When you run this chain, it automatically runs the other chains to update the tables and External reporting in MS Sustainability spreadsheet with the latest data.
Was this article helpful?
0 out of 0 found this helpful
External reporting in Microsoft Sustainability Manager enables you to manage reporting across multiple standards and...