Data Collection #
Reportworq supports several functions (AIInput and AIModeledInput) that are used to “writeback” data into Workday (specifically Standard Sheets, Modeled Sheets, and Adaptive Cube Sheets). These formulas are only available and executed when Reportworq receives a file for processing using a Reportworq Data Collection license.
The functions described in this section are specific to Workday Adaptive Planning datasources.
AIExtract #
The AIExtract function is designed to extract a crosstab of data from Workday Adaptive Planning similarly to running a Web Matrix Report. The AIExtract function supports nesting dimensions on rows and/or columns and like web reports, the element selections can dynamically load children or descendant elements automatically.
To provide maximum flexibility while leveraging native Excel & Reportworq features the matrix report generated by AIExtract is defined using a combination of 3 user-defined Excel Formulas that work together:
- The primary function, AIExtract, is used to indicate:
- Where on the spreadsheet the data should be exported.
- The dimension selections, including the axis and order they appear.
- Any options that apply to the report query.
- Each dimension’s element selections are defined using an AITier function. The AITier function is similar to dragging a selection in a Web Matrix Report and setting options for that selection.
- The AIOption function is used to modify query options, such as zero suppression.
The sample report, available for download below, shows a report spec defined on the Spec worksheet using AIExtract, AITier, and AIOption functions. The resulting output is rendered on the Report worksheet. The report in the sample nests Levels and Accounts on Rows, with both dimensions fully expanded, and nests Versions and Time on Columns. Versions is based on a static list of 2 versions and Time is dynamically expanded to Quarters.
The AIExtract function combines the AITier and AIOption functions into a report specification for extracting and then loading into a target cell range.
Syntax
AIExtract(Target Range, Tiers[, Options])
| Parameter | Description |
| Target Range | The top left cell address where the report extract should be exported to. |
| Tiers | A range of cells (or areas of ranges) that provide the AITier formulas for generating the report. |
| Options | An optional range of cells that provide the AIOption formulas for generating the report. |
AITier
AITier functions define a dimension and selection on a report axis, as well as the nesting order on the axis. Each AITier formula must be placed on an Axis where the dimension element selection can be set to none, children to dynamically expand to children or descendants to fully expand each element. Multiple AITier formulas for the same dimension can be defined so long as they appear on the same axis, which is useful when a mix of expanded and collapsed elements is required.
Syntax
AITier(Axis, Dimension, Selection, Depth, Order)
| Parameter | Description |
| Axis | The name of the axis for this selection: – Rows – Columns – Filters |
| Dimension | The name of the dimension for this selection, e.g. Versions, Time, Levels. |
| Selection | Element selections can be defined as: – A semi-colon delimited list of element names to include. – A reference to a range of cells that contain element names (see row 5 in the example file) |
| Depth | The hierarchy depth to expand each element in Selection, which can be one of these options: – None – Children – Descendants |
| Order | The order for this tier on the axis, moving from outer to inner. |
AIOption
The AIOption function is used to provide global options for the report query, such as autoformatting and zero suppression.
Syntax
AIOption(name, value)
| Parameter | Description |
| Name | The name of one of the supported options (see below). |
| Value | The value for that specific option. |
Supported Options
| Name | Description | Values | Default |
| suppresszeros | Suppress zero rows / columns | 0: none, 1: suppress rollups, 2:supress zeros | 0 |
| suppressrollups | Suppress automatic rollups | TRUE, FALSE | FALSE |
| autoformat | Automatically format the report: – Bold formats on column and row headers – Autofit column widths | TRUE, FALSE | TRUE |
AIFilter #
The AIFILTER function sets a filter for an Adaptive Report specification. Using AIFILTER enables you to use various Reportworq parameter types such as text lists and parameter sets when working with Adaptive Reports, instead of being limited to only the Adaptive Dimension Parameter type.
A worksheet can contain any number of AIFILTER formulas, and they can be positioned anywhere on the worksheet. Filters apply to the Adaptive report on the same worksheet only.
Syntax
AIFILTER([dimensionName], [memberName])
Formula parameters consist of one Adaptive dimension and one or more member names from that dimension. A member name can be specified as text or as a reference such as a lookup, cell reference, or named range.
| Parameter | Description |
| Dimension Name | The name of the Workday Adaptive dimension that contains the desired data. |
| Member Name | The name of the desired data member within the dimension. |
Examples:
- =AIFILTER(“Versions”, “Actual”)
- =AIFILTER(“Levels”, “New Jersey”, “Connecticut”, “Maine”)
- =AIFILTER(“Accounts”, RNG_ACCOUNTS)
AIModeledExport #
The AIModeledExport function exports data from a Modeled Sheet into an Excel Range or Table. This formula is typically used in concert with the AIModeledInput formula, defined in a calculated column, to distribute input forms to users who need to provide minor updates to a portion of a Modeled Sheet.
AIModeledExport options are modeled after parameters in the Adaptive exportConfigurableModelData API function.
Syntax
AIModeledExport( Range or Table Name, Version, Adaptive Sheet Name, [Start Period], [End Period], [Levels], [Include Descendants])
| Parameter | Description |
| Range or Table Name | A reference to an Excel table or a cell range. See Excel Ranges or Table Names for more information. |
| Version | The name of the Adaptive Version to export the data from. For example ‘Budget’. |
| Adaptive Sheet Name | The name of the Adaptive Modeled Sheet to export data from. For example ‘Personnel’. |
| Start Period | Optional name of a starting leaf period. |
| End Period | Optional name of an ending leaf period. |
| Levels | Optional pipe-delimited list of levels to export. |
| Include Descendants | Optional flag to also include descendants for the specified Levels. |
Examples
| Formula | Description |
| =AIModeledExport(“Table 4”, “Budget”, “Personnel”) | Export the “Personnel” Modeled Sheet for the “Budget” version to the table named “Table 4”. |
| =AIModeledExport(“Table 4”, “Budget”, “Personnel”, , , “East”) | Export the “Personnel” Modeled Sheet for the “Budget” version to the table named “Table 4”, including only the “East” level. |
| =AIModeledExport(“Table 4”, “Budget”, “Personnel”, “Jan-2020”, “Dec-2020”, “East|West”, TRUE) | Export the “Personnel” Modeled Sheet for the “Budget” version for all months between Jan-2020 and Dec-2020 to the table named “Table 4”, including only the “East” and “West” levels and their descendants. |
AIInput #
The AIInput formula adds the ability to write a value back to a Workday Adaptive Planning Standard or Cube Sheet. This formula references a cell that is linked to an Adaptive cube value as part of an OfficeConnect Excel report, by referencing an Adaptive OfficeConnect cell, Reportworq will automatically use that cell’s intersection when writing back into the planning system. The AIInput formula also supports providing the intersection information directly in the cell as pairs of Dimension and Element selections. By supplying the intersection information directly in the formula, the user can leverage Excel functionality to dynamically adjust the intersection based on other formulas.
Important
This formula will only work if the report is distributed using Reportworq and if the cell referenced was originally a value loaded from Workday Adaptive Planning.
Syntax – Referencing an OfficeConnect cell
AIInput(Value Range, [Adaptive Sheet Name])
| Parameter | Description |
| Value Range | A reference to a cell that was originally loaded from Adaptive when Reportworq created the output file. |
| Adaptive Sheet Name | Optional cube sheet to import the data into when referencing cube sheets. |
Examples
| Formula | Description |
| =AIINPUT(D10) | Reference an existing Adaptive OfficeConnect Standard Sheet cell that was distributed with Reportworq |
| =AIINPUT(D10, “New Premiums”) | Reference an existing Adaptive OfficeConnect Cube Sheet cell that was distributed with Reportworq. |
Syntax – Providing intersection information
AIInput(Value Range, [Adaptive Sheet Name], “Dimension|Element”, [“Dimension|Element”], [“Dimension|Element”], etc.)
| Parameter | Descirption |
| Value Range | A reference to a cell that was originally loaded from Adaptive when Reportworq created the output file. |
| Adaptive Sheet Name | Optional cube sheet to import the data into when referencing cube sheets. |
| Dimension|Element Pair | A string specifying the dimension name and element name separated by a pipe. E.g. “Versions|budget” or “Period|Jan-2023” |
| Dimension|Element Pair | Provide as many Dimension|Element pairs as required to identify the unique input intersection |
Examples
| Formula | Description |
| =AIINPUT(D10, “New Premiums”, “Versions|Budget”, “Accounts|Quotes”, “Levels|NJ”, “Time|Jan-2023”) | Reference an input cell and manually provide the tuple intersection information |
AIModeledInput #
The AIModeledInput formula provides the ability to write a value back to a Workday Adaptive Planning Modeled Sheet. This formula uses the importConfigurableModelData Adaptive API and the rules of that API method will apply to this formula. The AIModeledInput formula is typically defined in a calculated column in an Excel table that is updated during report distribution using the AIModeledExport formula. These formulas are designed to work hand in hand with input form distribution for users who need to provide minor updates to a portion of a Modeled Sheet.
Syntax
AIModeledInput(Version, Adaptive Sheet Name, Import Key Name, Import Key Value, Input Column Name, Input Value)
| Parameter | Description |
| Version | The name of the Adaptive Version to load the data to. For example, Budget. |
| Adaptive Sheet Name | The name of the Adaptive Modeled Sheet to import data into. For example, Personnel. |
| Import Key Name | The name of the dimension to use as an Import Key for updates. For example, EmployeeId. |
| Import Key Value | The dimension element to uniquely identify this row for updating. For example, John Snow. |
| Input Column Name | The name of the column in the Modeled Sheet to update. For example, Salary. |
| Input Value | The value to update in the Modeled Sheet for the specified input column, item (as defined by Import Key Name + Value), and version. |