Determines if multi-instance fields are expanded into separate rows.

Data Type

bool

Default Value

false

Remarks

The behavior of this option depends upon the service you are connecting to. Please refer to the WQL section for an overview, as the reporting and SOAP sections only cover details specific to those services. Also refer to ExpandMultiValueLimit to understand the performance consequences of enabling this option.

WQL

WQL uses multi-value fields to reference multiple rows from another table, such one journal entry referencing multiple journal line items. By default the connector displays these as JSON aggregates which must be parsed to get individual values.

Enabling this option expands each of these multi-value references across multiple rows. The connector separates out each aggregate into an ID column and a descriptor column (the same as single-value ID fields). It also generates a new field called workdayIDIndex that counts the number of rows expanded from one WQL row. The workdayID and workdayIDIndex form a composite primary key, instead of the workdayID being the primary key on its own.

The connector only expands rows that are included in the SELECT clause of the query. If more than one multi-value reference is selected, its values are combined with the other multi-value fields using a CROSS JOIN. This ensures that all combinations are included in the output so they can be used in WHERE or JOIN conditions.

Reporting

The reporting API behaves similarly to the WQL API, with three main differences:

The first is that two extra columns are added instead of just one. One is called RowIndex and it behaves the same as workdayIDIndex. The other is called RowNumber and it counts the number of original rows from the report. It is included because reports do not have a built-in primary key like WQL data sources do.

The second is that reporting supports the ExpandIDTypes option. By default single-value IDs are treated as multi-value fields and expanded in the same way. This is because even single-value references can have a separate value for each type of ID. If ExpandIDTypes is enabled then each type of ID gets its own column and only multi-value references are expanded across rows.

The third is that the RaaS API supports two main types of reports: advanced reports and other types of reports. The next section explains the differences.

Advanced and Non-Advanced Reports

Most types of reports only include fields from their primary business object, with related business objects included using their IDs. Reading a non-advanced report with ExpandMultiValues expands ID values the same way as WQL does.

Advanced reports can include fields from both the primary business object and directly related business objects. Enabling ExpandMultiValues only expands one level of repeated values.

For example, the expenses reports object has the following structure. An advanced report includes fields from both the expense reports themselves as well the related line items and approver:

• Expense Report
  • Is Approved (boolean)
  • Expense Report Line Items (multi-valued relationship)
    • Line Amount (number)
    • Tax Codes (multi-valued relationship)
  • Approver (single-valued relationship)
    • Name (text)
    • Related Workers (multi-value relationship)

With ExpandMultiValues the connector exposes it as a view with these columns. Notice that Tax_Codes is left as an aggregate containing all ID types, while Approver.Related_Workers.ID is expanded and includes one type per row.

• RowNumber (int)
• RowIndex (int)
• Is_Approved (boolean)
• Expense_Report_Line_Items.Line_Amount (decimal)
• Expense_Report_Line_Items.Tax_Codes (string, XML aggregate containing all related codes)
• Approver.Name (string)
• Approver.Related_Workers.Descriptor (string)
• Approver.Related_Workers.ID.type (string)
• Approver.Related_Workers.ID (string)

SOAP

The SOAP API exposes a more complex data model with different types of repeated values:

1. Mutli-value references that are similar to WQL and reporting multi-value references.
2. Basic multi-value fields like Address_Line_Data in the Workers_Address_Data table.
3. Complex multi-value structures that contain multiple fields.

This property only applies to the first two types because complex repeated structures are always exposed as child tables. The other two types behave similarly to WQL and reporting APIs with a few exceptions:

• Multi-value references have only an ID. No descriptor is included.
• If the table normally has a primary key, the connector adds only an IDIndex column that behaves like workdayIDIndex. Otherwise the connector adds a row counter called RowID in addition to the IDIndex column.