Excel Functions

Overview

ReportWORQ is designed to support all of Microsoft Excel's standard, built-in functions. ReportWORQ additionally supports several data provider-specific Excel functions which are documented, per data provider, in Datasources documentation.  

ReportWORQ does not support the use of Visual Basic for Applications or any other scripting languages, but it does include several ReportWORQ-specific Microsoft Excel functions that are designed to perform common reporting and data integration tasks that enhance its report distribution and data collection capabilities:

Data Provider-Specific ReportWORQ Functions

• Dynamic Reporting Features:

  • Relational Database Queries: RWSql

  • Workday Adaptive Planning: 

    • Data Crosstab Export: AIExtract, AIOption, AITier

    • Modeled Sheet Export: AIModeledExport 

• Data Collection Support:

  • Anaplan: AnaplanInput

  • Relational Databases: RWSqlUpdate, RWSqlUpsert

  • Workday Adaptive Planning: AIInput, AIModeledInput

General Purpose ReportWORQ Functions

• Page Break Customization: RWPageBreak

• Autofit rows/columns: RWResize

• Sort a data range: RWSort

• Remove unwanted rows/columns: RWSuppress

Data Collection

ReportWORQ supports several functions that are used to "writeback" data into configured data sources like SQL, Anaplan and 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.

Ranges and Tables

Several ReportWORQ-specific functions support a "Range or Table Name" parameter that is typically used when specifying a location for data to be output to, for instance in the RWSql or AIModeledExport functions. These parameters are used to target either a specific range or even Microsoft Excel tables with some of the following options: 

Workday Adaptive Planning

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:

1. 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.  

2. 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. 

3. 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.

AIExtract

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])

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)

AIOption

The AIOption function is used to provide global options for the report query, such as autoformatting and zero suppression.

Syntax

AIOption(name, value)

Supported Options

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])

Examples

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.

Syntax - Referencing an OfficeConnect cell

AIInput(Value Range, [Adaptive Sheet Name])

Examples

Syntax - Providing intersection information

AIInput(Value Range, [Adaptive Sheet Name], "Dimension|Element", ["Dimension|Element"], ["Dimension|Element"], etc.)

Examples

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)

SQL

RWSQL

The RWSQL function is used to return data to an Excel spreadsheet from an ODBC or OLE DB data source using a named ReportWORQ data source connection and SQL query.

Syntax

RWSQL( Range or Table Name, Connection Name, SQL Query)

Examples

Note that all examples below are based on a SQL Datasource connection named "SampleData" in ReportWORQ.

RWSQLUPDATE/RWSQLUPSERT

The RWSQLUPSERT & RWSQLUPDATE functions are both designed to update data in a single record of a relational database table when using the Data Collection capabilities of ReportWORQ and a valid ODBC or OLE DB SQL database connection. The differences between the functions are:

• RWSQLUPDATE: can only update an existing record.

• RWSQLUPSERT: can both insert and update a record.

Both functions require the following information:

• The name of the saved SQL database connection in ReportWORQ.

• The table name being updated.

• A way to uniquely locate the record:

  • The name of an Id column.

  • The value to search for in that Id column.

• A minimum of 1 column name/value combination indicating the value(s) to update in that record.

These functions will fail to update data if the ReportWORQ connection credentials do not have write access to the table or:

• RWSQLUPDATE, RWSQLUPSERT: The Id Column lookup returns more than one record.

• RWSQLUPDATE: The Id Column lookup returns no records.

Syntax

RWSQLUPDATE / RWSQLUPSERT ( Connection Name, Table Name, Id Column Name, Id Column Value, Column Name / Value List )

ANAPLANINPUT

The ANAPLANINOUT formula provides the ability to write a value back to an Anaplan cube. The ANAPLANINOUT formula is typically defined in an Excel report that is generated as an input template by ReportWORQ to collect data from users who, for instance, need to only provide minor updates to a portion of a cube.

Syntax

ANAPLANINOUT( Input Row, Input Column, Input Value )

General Purpose

RWPAGEBREAK

The RWPAGEBREAK function is used to manually toggle page breaks on rows or columns, based on a user-defined conditional expression. Page breaks are activated at the location of the function when the Enable parameter is TRUE, for instance when a specific heading is detected in data that indicates a need to insert a break in rows or columns. The Direction field indicates whether a vertical (columns) or horizontal (rows) page break is inserted.

Syntax

RWPAGEBREAK( Enable, [Direction] )

Examples

RWRESIZE

The RWRESIZE function performs an autofit to data operation on the row or column containing the RWRESIZE function. This is most often used to resize columns to avoid a ##### display when a cell is not large enough to display a numeric value. RWRESIZE has special support for Merged Cells. If an RWRESIZE formula is located in a merged cell then the resize operation will be performed on the group of rows or columns in the cell's range and the height or width will be distributed equally. The RWRESIZE will not perform its operation if the row or column it is acting on is hidden. 

Syntax

RWRESIZE( [Axis], [Pad] )

Examples

RWSORT

The RWSORT function will sort a specified range of data based on up to 3 sort keys, each optionally specifying a sort ascending or descending option.

Syntax

RWSORT( Range or Table Name, [Sort Column 1], [Sort Order 1], [Sort Column 2], [Sort Order 2], [Sort Column 3], [Sort Order 3] )

Examples

RWSUPPRESS

The RWSUPPRESS function is used to enable custom suppression of rows and/or columns. Suppression can hide or delete the row/columns based on the condition in the Enable parameter. While this function is typically used to check for zero or blank values (zero suppression), any expression can be used. For example, combing RWSUPPRESS with RWSORT would allow you to rank data in order and then keep only the top n rows.

Syntax

RWSUPPRESS( Enable, [Axis], [Mode] )

Examples