The pattern: two RaaS calls, one Join Loop, one aggregation

To merge two RaaS reports in Orchestrate, call each report with its own Send Workday RaaS Request step, then pass both responses into a Join Loop that matches rows on a key the two reports share, such as an employee ID. Inside the loop you read fields from both rows, and an aggregation on the Join Loop collects the merged rows into a single JSON or CSV document that a Store Document step saves.

Both report calls must come before the loop. The Send Workday RaaS Request reference says the component can't be used in join loops, and it is only available in the Asynchronous, Business Process and Integration System templates, so a Synchronous orchestration can't use it.

First check whether one report can do it

Merging in Orchestrate is the right answer when the two reports have different primary business objects or data sources, or one of them belongs to someone else and can't be changed. When both reports start from the same data source, such as workers, adding the second report's fields to the first (directly or through related business objects) gives one call and nothing to match. That is our recommendation rather than a Workday rule: it removes the unmatched-row and duplicate-key decisions below.

Call both reports before the loop

Add one Send Workday RaaS Request step per report. Each step carries:

  • The report's namespace in the path, of the form owner/report_name. For an Integration System orchestration, intsys.reportPath("Workers Report") returns that path from a report alias configured on the integration system, so the orchestration does not hard-code the owner. Workday's employee-demographic sample app reads its report path this way.
  • A format query parameter for the response format. The step's Content Type describes the step's output but does not determine the format Workday returns. Take the value from the JSON URL listed under Web Services > View URLs on the report definition's related actions.
  • Any report prompts as query parameters, named exactly as they appear in the View URLs output. The same sample passes a worker prompt as Workers!Employee_ID.

A RaaS request can take up to an hour to respond without timing out the orchestration, and the overall orchestration timeout resets when the response arrives. An Integration System orchestration has a 48-hour runtime limit. Setup on the report side (enabling it as a web service, sharing it with the integration system user) is covered in setting up Workday RaaS.

Use JSON for both reports. Every row then sits in the Report_Entry array (Workday's sample apps iterate RaaS responses with iterator("$.Report_Entry[*]")), and each field is named by the column's XML alias, which Workday shows on the report's columns once it is enabled as a web service. The iterator and key expressions become simple JSONPath reads. CSV also works with a Join Loop (the key becomes a column read such as columnValueAtIndex(0)), but a column-position key breaks silently when someone reorders the report.

Configure the Join Loop

Name the Join Loop step, for example MergeReports, then set the primary dataset and one joining dataset:

Primary Dataset
  Output Name:          worker
  Iterator:             data.WorkersReport.response.iterator("$.Report_Entry[*]")

Joining Dataset
  Output Name:          comp
  Primary Dataset Key:  data.MergeReports.worker.stringAtJsonPath("$.Employee_ID")
  Iterator:             data.CompReport.response.iterator("$.Report_Entry[*]")
  Joining Dataset Key:  data.MergeReports.comp.stringAtJsonPath("$.Employee_ID")

Which report is primary matters. The primary dataset drives the loop, so it should be the report whose rows the output must keep; the joining dataset is queried for every primary row and is capped at 500 MB. Workday's Join Loop reference also says a dataset with unusually large records is more efficient on the primary side. Keep the joining report narrow by including only the columns the merge needs.

If the key fields are formatted differently in the two reports (a padded ID in one, a plain number in the other), the match fails for every row. Fix it in the reports if you can; otherwise normalise both key expressions the same way with String functions such as trim(), upperCase() or leftPad().

Decide what happens to rows that exist in only one report

This is the decision that defines the merge. On the primary dataset, under Missing Match Handling:

  • Skip Record gives an inner join: only workers present in both reports are output.
  • Alternate Processing gives a left join: every primary row enters the loop. Branch on isDataDefined("MergeReports", "comp") to write the compensation fields when they exist and blanks or an exception flag when they don't.
  • Complete with Error stops the run at the first unmatched row, which suits a merge where a missing row means the data is wrong.

Rows that exist only in the joining report never enter the loop under any setting. If you need them too (a full outer join), read them after the loop from the join's residuals. Workday's project forecasting sample reads them as data("MergeReports", "joinResidual"); on that value, unusedJoinWithKeys("comp") lists the joining keys no primary row used, and dataJoinedWith("comp", key) returns the joining record for one of those keys. Loop over that list with a second Loop step to append the right-only rows. More on skipped and residual records is in the Join Loop guide.

Duplicate keys in the second report

A compensation or job-history report often has more than one row per worker. The Multiple Match Strategy decides: Use First or Use Last takes one row by position (sort the report so that position means something, such as most recent effective date last), Use All makes every matching row available in the loop body, Skip Record drops the primary row, and Complete with Error stops. Pick deliberately: leaving Use First on an unsorted report produces a plausible-looking file with arbitrary values.

Build the merged output

Inside the loop, read fields from both sides by output name:

data.MergeReports.worker.stringAtJsonPath("$.Employee_ID")
data.MergeReports.worker.stringAtJsonPath("$.Cost_Center")
data.MergeReports.comp.stringAtJsonPath("$.Base_Pay")

From the Join Loop's step menu, choose Add Aggregation and add an output. JSON Fold collects a JSON fragment per iteration into one array, typically built by a Create Text Template step in the loop body. CSV Fold takes a String List per iteration and produces one CSV document, optionally using a CSV Format from the app's settings for headers and column order. Workday's Orchestrate sampler app joins a CSV to an XML document exactly this way, with a CSV Fold of List.stringList(...) over fields from both sides. The CSV side is covered step by step in processing a CSV file in Orchestrate.

After the loop, a Store Document step saves the aggregated output. In an Integration System orchestration, the stored document is not attached to the integration event unless you set Attach document to integration event to true, so set it if people need to download the file from the event.

Prove the merge is complete

A run marked successful only shows that the loop finished. Prove the row counts instead:

  1. Count the input rows of each report, for example with countItemsAtJsonPath("$.Report_Entry") on each response.
  2. Add a Count aggregation to the Join Loop alongside the fold, so the run reports how many merged rows it produced.
  3. Account for the difference with the residuals: dataSkippedRecords() (skipped primary rows) and unusedJoinWithKeys("comp") should explain every row that is not in the output. summary() on the residuals returns a readable line for an integration message.
  4. Open the stored file and check a handful of workers whose values you know in both reports, including one with duplicate rows in the second report.

While building, Orchestration Builder's debugger runs one step at a time and shows its outputs. An Integration System orchestration has to be deployed and run once in the tenant before you can debug it; the debugger then imports a recent run's launch parameters, tokens and documents. For heavy volumes, see the Orchestrate looping and pagination limits before scheduling the merge.

Sources checked