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 Collection#

Reportworq supports several functions that are used to "writeback" data into configured data sources like SQL, Anaplan, IBM Planning Analytics 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. These parameters are used to target either a specific range or even Microsoft Excel tables with some of the following options:

RW.TOOLS.PAGEBREAK#

General Purpose#

RW.Tools.PageBreak also known as RWPageBreak inserts a horizontal or vertical page break relative to the row or column that contains the formula. If you are on a version earlier than 5.0.0.91 please use RWPageBreak.

Syntax#

=RW.Tools.PageBreak(doBreak, [mode], [position])

Arguments#

Behavior#

• A horizontal page break is inserted at the formula row; an after break is inserted one row later.
• A vertical page break is inserted at the formula column; an after break is inserted one column later.
• If Excel printing is configured to fit pages tall or fit pages wide, the conflicting page break direction is rejected.
• On success, the formula cell is cleared.

Usage notes#

• Use RW.Tools.PageBreak after your dynamic data area so the break tracks the generated report layout.
• Boolean forms are supported, but string arguments are easier to read in a workbook template.

Examples#

=RW.Tools.PageBreak(TRUE)

=RW.Tools.PageBreak(TRUE, "horizontal", "after")

=RW.Tools.PageBreak(A1, "vertical", "before")

Common mistakes#

• Enabling both a page break and a conflicting Fit To Pages setting.
• Expecting FALSE to remove an existing manual page break; the formula simply does nothing in that case.

RW.TOOLS.REMOVEFORMULA#

The RW.TOOLS.REMOVEFORMULA aka RWRemoveFormula function removes formulas from a specified range of cells at report generation time, replacing them with their static values. If you are on a version earlier than 5.0.0.91 please use RWRemoveFormula.

At the job distribution level when exporting to Excel you can define if you want to keep all formulas, remove all formulas, remove provider formulas, remove provider formulas (except writeback) but if you need more control/finesse over what formula cells are left and which remain you can use that is when you would want to use RW.TOOLS.REMOVEFORMULA.

Syntax#

=RW.Tools.RemoveFormula(doRemove, targetRange, [mode])

Arguments#

Behavior#

• Entire-row references expand to every used column on the sheet.
• Entire-column references expand to every used row on the sheet.
• Multi-range references such as {A1:A2,B3:B4} are supported.
• In provider mode, Reportworq only removes formulas whose text contains a TM1 provider formula name.
• On success, the formula cell is cleared.

Usage notes#

• Use RW.Tools.RemoveFormula when you want to freeze calculated results before distribution.
• provider mode is useful when you want to strip provider formulas but keep normal Excel formulas.

Examples#

=RW.Tools.RemoveFormula(TRUE, A1)

=RW.Tools.RemoveFormula(TRUE, A:A)

=RW.Tools.RemoveFormula(TRUE, {A1:A2,B3:B4}, "provider")

Common mistakes#

RW.TOOLS.RESIZE#

The RW.TOOLS.RESIZE function aka RWResize automatically resizes the row height or column width of the cell it is placed in at report generation time. It works like Excel's "AutoFit" feature, but applied dynamically as part of the Reportworq report run to ensure rows and columns fit their content after all data has been populated. If you are on a version earlier than 5.0.0.91 please use RWResize.

Syntax#

=RW.Tools.Resize([type], [pad])

Arguments#

Behavior#

• Hidden rows and hidden columns are skipped.
• Merged cells are handled during auto-fit, so this formula is useful for narrative layouts, wrapped text, and troubleshooting rows that otherwise do not size cleanly around merged content.
• On success, the formula cell is cleared.

Usage notes#

• Use RW.Tools.Resize on text-heavy headers, comments, or narrative cells that are populated at runtime.
• Add a small pad value when the default auto-fit feels too tight.

Examples#

=RW.Tools.Resize()

=RW.Tools.Resize("columns")

=RW.Tools.Resize("rows", 5)

Common mistakes#

Supplying a text value for pad; the implementation only applies padding when the second argument is numeric.

Expecting hidden rows or columns to resize.

RW.TOOLS.SORT#

The RW.TOOLS.SORT aka RWSort function will sort a range or Excel Table (ListObject) at report generation time, directly within the Reportworq template. It supports sorting by up to three columns in any combination of ascending or descending order, and can optionally limit the output to the top N or bottom N rows after sorting. If you are on a version earlier than 5.0.0.91 please use RWSort.

Syntax#

=RW.Tools.Sort(sortRange, [key1], [ascending1], [key2], [ascending2], [key3], [ascending3], [limitType], [limitCount])

All arguments after sortRange are optional.

Arguments#

Note: limitType and limitCount must be supplied together as the last two arguments. They are detected automatically — no placeholder arguments are needed.

Examples#

Sort by the first column ascending (implicit)

=RW.Tools.Sort("A1:D30")

Sort by the first column ascending (explicit)

=RW.Tools.Sort("A1:D30", 1, TRUE)

Sort by the first column ascending, then second column descending

=RW.Tools.Sort("A1:D30", 1, TRUE, 2, FALSE)

Sort by three columns

=RW.Tools.Sort("A1:D30", 1, TRUE, 2, TRUE, 3, FALSE)

Sort by the first column and keep the top 5 rows

=RW.Tools.Sort("A1:D30", 1, TRUE, "TOP", 5)

Sorts column 1 ascending, then hides every row beyond position 5.

Sort by the first column descending and keep the bottom 3 rows

=RW.Tools.Sort("A1:D30", 1, FALSE, "BOTTOM", 3)

Because the data is sorted descending, the last 3 rows represent the three smallest values.

Sort by two columns and return the top 10 rows

=RW.Tools.Sort("A1:D30", 1, TRUE, 2, TRUE, "TOP", 10)

Keep the top 5 rows without specifying a sort key

=RW.Tools.Sort("A1:D30", "TOP", 5)

Uses whatever order the data is already in and hides all rows after the 5th.

Behavior#

Sort range

The sortRange argument may be:

• A cell address string such as "A1:D30" — the range is resolved on the sheet that contains the formula.
• A named table name — the formula sorts the table's data rows (header row is excluded).

Column key indexes are 1-based and relative to the sort range. If the range starts at column D, key 1 refers to column D, key 2 to column E, and so on.

Execution order

RW.Tools.Sort runs as a one-time post-processing step after all data-import formulas (e.g., RW.Relational.Export) have populated the workbook, and before any row-suppression (RW.TOOLS.SUPPRESS) pass.

This means:

• Sorting always operates on the final expanded dataset.
• Row suppression is applied after sorting, so RW.Tools.Sort with a TOP/BOTTOM limit integrates naturally with other suppression rules.

TOP / BOTTOM limit

When limitType and limitCount are supplied:

1. The sort is performed first (if sort keys are specified).
2. Rows outside the requested limit are hidden (not deleted).

If limitCount is greater than or equal to the total number of rows in the range, no rows are hidden.

Hidden rows

Rows hidden by the TOP/BOTTOM limit behave like any other hidden row in Excel. They are included in the workbook but not printed or displayed. They can be revealed by RW.TOOLS.SUPPRESS or standard Excel unhide operations.

Argument validation and error handling#

RW.TOOLS.SUPPRESS#

The RW.TOOLS.SUPPRESS aka 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. If you are on a version earlier than 5.0.0.91 please use RWSuppress.

Syntax#

=RW.Tools.Suppress(doSuppress, [type], [mode])

Arguments#

Behavior#

• Row suppressions are processed sheet-by-sheet from the bottom upward.
• Column suppressions are processed from the right toward the left.
• Adjacent formulas with the same sheet, direction, doSuppress value, and suppression mode are grouped into one contiguous suppression range.
• RW.Tools.Suppress runs after RW.Tools.BlankRow and RW.Tools.Group, and before RW.Tools.PageBreak and RW.Tools.RemoveFormula.
• On success, the formula cell is cleared.

Usage notes#

• Use hide when users may still want to unhide the suppressed rows or columns later.
• Use delete when the rows or columns should be removed from the finished report entirely.
• Because rows and columns can be deleted, later layout steps should not rely on the original coordinates.

Examples#

=RW.Tools.Suppress(A1=0)

=RW.Tools.Suppress(TRUE, "rows", "delete")

=RW.Tools.Suppress(TRUE, "columns", "hide")

Common mistakes#

RW.Tools.SheetColor#

This function is only available in version 5.0.0.91 or later. Sets the worksheet tab color from inside Excel when the Sheet Tab Color worksheet option is not configured in the Reportworq UI.

Syntax#

=RW.Tools.SheetColor(value)

value should evaluate to a color value supported by the existing worksheet tab color behavior, such as:

• an integer color value
• a hex color like #FF0000
• a named color like Red

Examples#

=RW.Tools.SheetColor("#4F81BD")

=RW.Tools.SheetColor(A1)

=RW.Tools.SheetColor("Red")

Behavior#

• Only one RW.Tools.SheetColor formula is allowed per worksheet.
• If the worksheet's Sheet Tab Color option is configured in the Reportworq UI, the UI value wins.
• If the UI option is not configured and exactly one RW.Tools.SheetColor formula exists on the worksheet, the formula value is used.
• If multiple RW.Tools.SheetColor formulas exist on the same worksheet, no formula-based tab color is applied.

Error behavior#

When the UI option is configured, each RW.Tools.SheetColor formula on that worksheet is marked as an error:

RW.Tools.SheetColor was ignored because Sheet Tab Color is configured in the Reportworq worksheet options.

When multiple RW.Tools.SheetColor formulas exist on the same worksheet, all of them are marked as errors:

Multiple RW.Tools.SheetColor formulas were found on this worksheet. Only one is allowed, so no formula-based sheet tab color was applied.

If the formula returns a value that cannot be applied as a worksheet tab color, no formula-based color is applied and the formula is marked as an error.

Example error:

Error setting tab color on sheet 'Sheet1' using value 'NotAColor'.

RW.Tools.SheetName#

This function is only available in version 5.0.0.91 or later. Sets a worksheet's output name from inside Excel when the Sheet Name worksheet option is not configured in the Reportworq UI.

Syntax#

=RW.Tools.SheetName(value)

value should evaluate to the worksheet name you want to use.

Examples#

=RW.Tools.SheetName("P&L")

=RW.Tools.SheetName(A1)

=RW.Tools.SheetName("Sales - " & B1)

Behavior#

• Only one RW.Tools.SheetName formula is allowed per worksheet.
• If the worksheet's Sheet Name option is configured in the Reportworq UI, the UI value wins.
• If the UI option is not configured and exactly one RW.Tools.SheetName formula exists on the worksheet, the formula value is used.
• If multiple RW.Tools.SheetName formulas exist on the same worksheet, no formula-based rename is applied.

Error behavior#

When the UI option is configured, each RW.Tools.SheetName formula on that worksheet is marked as an error:

RW.Tools.SheetName was ignored because Sheet Name is configured in the Reportworq worksheet options.

When multiple RW.Tools.SheetName formulas exist on the same worksheet, all of them are marked as errors:

Multiple RW.Tools.SheetName formulas were found on this worksheet. Only one is allowed, so no formula-based sheet name was applied.

If the formula does not return a valid worksheet name, no formula-based rename is applied and the formula is marked as an error.
To be valid, the formula result must:

• not be blank or whitespace
• satisfy Excel worksheet naming rules after Reportworq applies its normal sheet-name sanitization rules

In practice, that means the final name still needs to fit Excel's worksheet naming restrictions such as length limits and disallowed characters.

Example error:

RW.Tools.SheetName did not return a valid worksheet name.

RW.Tools.SheetSuppress#

This function is only available in version 5.0.0.91 or later. Suppresses a worksheet from inside Excel when the Sheet Suppression worksheet option is not configured in the Reportworq UI.

Syntax#

=RW.Tools.SheetSuppress(value)

value should evaluate to a suppression flag. Values that behave like the existing worksheet suppression logic will suppress the sheet, including:

• TRUE
• 1

Any other value leaves the sheet in place.

Examples#

=RW.Tools.SheetSuppress(TRUE)

=RW.Tools.SheetSuppress(A1)

=RW.Tools.SheetSuppress(COUNTIF(A1:A10, "Error") > 0)

Behavior#

• Only one RW.Tools.SheetSuppress formula is allowed per worksheet.
• If the worksheet's Sheet Suppression option is configured in the Reportworq UI, the UI value wins.
• If the UI option is not configured and exactly one RW.Tools.SheetSuppress formula exists on the worksheet, the formula value is used.
• If multiple RW.Tools.SheetSuppress formulas exist on the same worksheet, no formula-based suppression is applied.

Error behavior#

When the UI option is configured, each RW.Tools.SheetSuppress formula on that worksheet is marked as an error:

RW.Tools.SheetSuppress was ignored because Sheet Suppression is configured in the Reportworq worksheet options.

When multiple RW.Tools.SheetSuppress formulas exist on the same worksheet, all of them are marked as errors:

Multiple RW.Tools.SheetSuppress formulas were found on this worksheet. Only one is allowed, so no formula-based sheet suppression was applied.

Formula-based suppression is evaluated independently from sheet naming and sheet tab color, so an invalid RW.Tools.SheetSuppress formula does not block those other worksheet features.

RW.Tools.Group#

This function is only available in version 5.0.0.91 or later. Creates Excel outline groups at report-generation time for repeated rows or columns.

Syntax#

=RW.Tools.Group(identifier, [axis], [visible], [additionalGroupDepth])

Arguments#

Behavior#

• Consecutive matching non-blank identifiers form one group.
• The first matching row or column is treated as the header/summary and is not placed inside the grouped body.
• A blank identifier ends the current group without inserting a separator.
• A change from one non-blank identifier directly to another inserts a blank separator row or column between the two groups so Excel keeps them separate.
• For row grouping, each helper column is a separate nesting level. For column grouping, each helper row is a separate nesting level.
• Outer nesting levels are processed before inner levels.
• RW.Tools.Group runs after RW.Tools.BlankRow and before RW.TOOLS.SUPPRESS

Usage notes#

• Put helper grouping formulas in hidden helper columns or rows when you do not want them visible in the template.
• Use additionalGroupDepth only when you need extra outline depth for the same body region.
• On completion, the formula cells are cleared from the output workbook.

Examples#

=RW.Tools.Group("East")

=RW.Tools.Group("Product A", "rows", "hide")

=RW.Tools.Group("Quarter", "columns", "show", 1)

Common mistakes#

RW.Tools.BlankRow#

This function is only available in version 5.0.0.91 or later. Inserts one or more blank rows adjacent to the row containing the formula at report-generation time.

Rows are inserted after all runtime expansion steps (e.g., RW.Relational.Export) and before row suppression (RW.TOOLS.SUPPRESS), giving you deterministic spacing that works with dynamic reports.

Syntax#

=RW.Tools.BlankRow([doInsert], [count], [mode], [insertAbove], [rowHeight])

All arguments are optional.

Arguments#

Examples#

Insert one blank row above the current row (default behavior)

=RW.Tools.BlankRow()

Insert one blank row above (explicit)

=RW.Tools.BlankRow(TRUE)

Insert three blank rows above

=RW.Tools.BlankRow(TRUE, 3)

Insert two blank rows below the current row

=RW.Tools.BlankRow(TRUE, 2, "blank", FALSE)

Insert two rows above with copyformat

=RW.Tools.BlankRow(TRUE, 2, "copyformat")

Insert one blank row above with an explicit row height of 18 points

=RW.Tools.BlankRow(TRUE, 1, "blank", TRUE, 18)

Conditional insertion controlled by another cell

=RW.Tools.BlankRow(A1, 2)

Inserts two rows when the value of cell A1 is TRUE; does nothing when FALSE.

Behavior#

Anchor row

The anchor row is the row that contains the RW.Tools.BlankRow formula. Inserted rows appear immediately above or below it depending on insertAbove.

Execution order

RW.Tools.BlankRow runs after all data-import and row-expansion steps (e.g., RW.Relational.Export) and immediately before RW.TOOLS.SUPPRESS. This means:

• Spacing is added after dynamic data has already been written.
• RW.Tools.Suppress can still hide or delete inserted blank rows as normal.

Multiple RW.Tools.BlankRow on the same row

If more than one RW.Tools.BlankRow formula exists on the same row, the engine consolidates them into a single instruction using the following rules:

• count — the maximum value across all formulas on that row.
• mode, insertAbove, rowHeight — taken from the formula in the leftmost column on that row.
• doInsert — TRUE if any formula on the row has doInsert = TRUE.

Multiple anchors on the same sheet

Insertions are processed top-to-bottom. Each insertion shifts all subsequent anchor rows downward, so the reported row numbers automatically stay correct.

Argument validation and error handling#

Invalid arguments never crash report generation. Instead, a warning is logged and a safe default is used:

RW.Tools.AIInsight#

This function is only available in version 5.0.0.91 or later. Calls a configured AI provider with a prompt and writes the response directly into a named Excel TextBox or a target cell at report-generation time.

Use RW.Tools.AIInsight to embed AI-generated summaries, commentary, or analysis into your Excel reports without any manual copy-and-paste step.

Syntax#

=RW.Tools.AIInsight(providerName, destination, prompt, [dataRange], [maxTokens])

The first three arguments are required.

Arguments#

Examples#

Write an AI summary to a TextBox

=RW.Tools.AIInsight("OpenAI", "TextBox1", "Provide a one-paragraph executive summary of this data.", A1:C20, 500)

Calls the OpenAI provider, appends the values in A1:C20 as a Markdown table to the prompt, and writes the response into the TextBox named TextBox1.

Write an AI response to a cell

=RW.Tools.AIInsight("OpenAI", D5, "Summarize the key trends in this range.", A1:C5)

Writes the AI response into cell D5 (top-left of the supplied reference).

Plain prompt without data range

=RW.Tools.AIInsight("Azure OpenAI", "CommentaryBox", "What are the top three risks in a declining revenue scenario?")

Sends a standalone prompt to the Azure OpenAI provider and places the response in the CommentaryBox TextBox.

Custom token limit

=RW.Tools.AIInsight("OpenAI", "SummaryBox", "Summarize this quarter's performance.", B2:F30, 2500)

Behavior#

Provider resolution

The provider is looked up by matching the providerName argument against the Name and ProviderId of every enabled AI provider configured in ReportWorq (case-insensitive). If no match is found, or the matched provider is disabled, the formula logs an error.

Prompt construction

The full prompt sent to the AI provider is built as follows:

1. The literal text supplied in the prompt argument.
2. If dataRange is supplied, a blank line followed by the range data rendered as a Markdown table is appended.

Destination: TextBox

When destination is a string, the formula searches all worksheets in the workbook for a TextBox with that name and replaces its text content with the AI response. If no TextBox with that name exists, a warning is logged and no change is made.

Destination: Cell

When destination is a cell reference, the AI response is written to the top-left cell of the referenced area.

Execution order

RW.Tools.AIInsight runs after all data-import formulas (e.g., RW.Relational.Export), sorting (RW.TOOLS.SORT), and row suppression. This ensures the AI receives the fully expanded, sorted dataset when a dataRange is specified.

Formula cell value

On success, the cell containing the RW.Tools.AIInsight formula is set to an empty string. The AI response is written exclusively to the destination, not to the formula cell itself. On failure (provider not found, provider disabled, or AI call error), the formula cell is set to a #VALUE! error value.

Argument validation and error handling#

Prerequisites#

An AI provider must be configured and enabled in ReportWorq before using RW.Tools.AIInsight. Navigate to Administration > AI Providers to add and enable a provider.

Usage notes#

• TextBox destinations work well with ReportWorq PowerPoint integration when you want AI-generated narrative copied into PowerPoint output.