⬇ Download Reportworq
⬇ Guide PDF
Home › User Guide › Worksheet functions
referencereport author

General report and layout functions#

The general family is the RW.Tools.* functions that post-process the generated workbook. They do not fetch data; they reshape and format what the data functions produced. Reportworq runs them in a fixed order during report generation, then clears each formula cell on success.

Every function has a canonical dotted name and a legacy short alias. Both resolve to the same function, and new workbooks should use the canonical form.

Function Alias What it does
RW.Tools.Sort RWSORT Sorts a range or table, optionally keeping only the top or bottom N rows
RW.Tools.Suppress RWSUPPRESS, RWZERO Hides or deletes rows or columns on a condition
RW.Tools.Group RWGROUP Builds Excel outline groups from repeated identifiers
RW.Tools.BlankRow RWBLANKROW Inserts blank rows next to the formula row
RW.Tools.PageBreak RWPAGEBREAK Inserts a horizontal or vertical page break
RW.Tools.Resize RWRESIZE Auto-fits a row height or column width, including merged cells
RW.Tools.RemoveFormula RWREMOVEFORMULA Replaces formulas with their values
RW.Tools.SheetName RWSHEETNAME Sets the worksheet output name from a formula
RW.Tools.SheetColor RWSHEETCOLOR Sets the worksheet tab color from a formula
RW.Tools.SheetSuppress RWSHEETSUPPRESS Suppresses a worksheet from a formula

The layout pipeline order#

When several of these functions interact, order matters. Reportworq runs them in this sequence on the expanded data:

  1. Data importers (RW.Relational.Export, RW.PlanningAnalytics.MDX) populate the ranges.
  2. RW.Tools.Sort
  3. RW.Tools.BlankRow
  4. RW.Tools.Group
  5. RW.Tools.Suppress
  6. RW.Tools.PageBreak and RW.Tools.RemoveFormula

This guarantees, for example, that a top-N sort is applied before rows are suppressed.

RW.Tools.Sort#

Sorts a target range or named table, with an optional limit that keeps only the top or bottom N rows after sorting. Use it instead of a separate sort-then-suppress pair when you only need the ranked portion of a dataset.

Signature

=RW.Tools.Sort(sortRange, [key1], [ascending1], [key2], [ascending2], [key3], [ascending3], [limitType], [limitCount])
# Argument Type Default Meaning
1 sortRange String or range required A cell address such as "A1:D30", resolved on the sheet holding the formula, or a named table. A table sorts its data rows, with the header excluded.
2 key1 Integer 0 Primary sort column, 1-based and relative to the sort range. 0 skips this level.
3 ascending1 Boolean TRUE FALSE sorts descending.
4 key2 Integer 0 Secondary key, applied only when key1 values tie.
5 ascending2 Boolean TRUE Direction for key2.
6 key3 Integer 0 Tertiary key, applied only when key1 and key2 both tie.
7 ascending3 Boolean TRUE Direction for key3.
8 limitType String none "TOP" keeps the first N rows after sorting, "BOTTOM" the last N. Case-insensitive.
9 limitCount Integer none How many rows to keep. Must be a positive whole number.

Column indexes are relative to the range. If sortRange starts at column D, then key1 = 1 means column D and key2 = 2 means column E.

The limit arguments are positional. limitType and limitCount must be supplied together as the last two arguments. Reportworq detects them automatically, so you do not need placeholder arguments, and you can apply a limit without sorting at all.

Rows outside the limit are hidden, not deleted. They behave like any other hidden Excel row: present in the workbook, not printed or displayed. If limitCount is greater than or equal to the number of rows in the range, nothing is hidden.

Examples

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

The fourth example sorts descending and then keeps the last three rows, which are the three smallest values. The fifth applies a limit to the data in whatever order it already has.

When an argument is wrong

Situation Result
sortRange omitted Formula error. It is required.
More than nine arguments Formula error, too many parameters.
A key is 0 That sort level is skipped.
limitCount is zero, negative, or fractional The TOP/BOTTOM pattern is not recognized, the arguments fall through to sort-parameter parsing, and you get a parse error.
limitCount is at least the row count Nothing is hidden.

RW.Tools.Suppress#

Hides or deletes rows or columns based on a Boolean condition.

Signature

=RW.Tools.Suppress(doSuppress, [type], [mode])
# Argument Type Default Meaning
1 doSuppress Boolean FALSE Suppression is applied when TRUE.
2 type String or Boolean "rows" "rows" or TRUE for rows, "columns" or FALSE for columns.
3 mode String or Boolean "delete" "delete" or TRUE removes; "hide" or FALSE hides.

The default mode is delete, not hide. This is the single most common surprise in the function surface. If you only want the rows out of sight, pass the hide mode explicitly.

Processing order protects coordinates. Rows are processed sheet by sheet from the bottom upward, and columns from the right toward the left, so earlier deletions never invalidate later coordinates. Adjacent formulas that share a sheet, direction, doSuppress value and mode are coalesced into one contiguous suppression range.

Because rows and columns can be deleted, later layout steps must not depend on the original coordinates.

Examples

=RW.Tools.Suppress(A1=0)
=RW.Tools.Suppress(TRUE, "rows", "delete")
=RW.Tools.Suppress(TRUE, "columns", "hide")

When an argument is wrong

Situation Result
You expected hide and did not pass a mode The rows or columns are deleted.
You expected top-down delete order Rows go bottom-up, columns right-to-left.
You used hide but need them gone Hidden rows still exist in the file. Use delete.

RW.Tools.Group#

Creates Excel outline groups from repeated row or column identifiers.

Signature

=RW.Tools.Group(identifier, [axis], [visible], [additionalGroupDepth])
# Argument Type Default Meaning
1 identifier String, number or Boolean required The group key for this row or column. Blank means do not group this position.
2 axis String "rows" "rows" or "columns".
3 visible String "show" "show" starts the group expanded, "hide" starts it collapsed.
4 additionalGroupDepth Integer 0 Adds extra outline depth over the same body range.

The first match is the header and stays outside the group. Consecutive matching non-blank identifiers form one group, and the first of them is treated as the header or summary line rather than being placed inside the collapsible body. This trips people up when they expect the whole block to collapse.

A blank identifier ends the current group without inserting a separator. A change straight from one non-blank identifier to another inserts a blank separator row or column so Excel keeps the two groups distinct.

Nesting. For row grouping, each helper column is a separate nesting level; for column grouping, each helper row is. Outer levels are processed before inner ones. Put the helper formulas in hidden helper columns or rows when you do not want them visible.

Examples

=RW.Tools.Group("East")
=RW.Tools.Group("Product A", "rows", "hide")
=RW.Tools.Group("Quarter", "columns", "show", 1)

When an argument is wrong

Situation Result
Blank identifiers in the middle of a block The group ends at the blank.
Expecting the first matching row to collapse It is the summary line and stays outside the body.
Invalid axis or visible Reportworq logs a warning and falls back to the default.

RW.Tools.BlankRow#

Inserts one or more blank rows next to the row containing the formula. Rows are inserted after all runtime expansion and before suppression, so spacing works in a report whose size is only known at run time.

Signature

=RW.Tools.BlankRow([doInsert], [count], [mode], [insertAbove], [rowHeight])
# Argument Type Default Meaning
1 doInsert Boolean TRUE FALSE makes the formula a no-op, which is how you drive insertion from another cell.
2 count Integer 1 A whole number from 0 to 1000.
3 mode String "blank" "blank" gives unformatted rows; "copyformat" inherits the style of the row immediately above the insertion point.
4 insertAbove Boolean TRUE TRUE inserts above the anchor row and shifts it down, FALSE inserts below.
5 rowHeight Number none Explicit height in points. Must be non-negative.

The anchor row is the row holding the formula. Insertions are processed top to bottom, and each one shifts the anchors below it, so row numbers stay correct without any manual adjustment.

Several BlankRow formulas on one row consolidate into one instruction. count becomes the maximum across them; mode, insertAbove and rowHeight are taken from the formula in the leftmost column; and doInsert is true if any of them is true.

Examples

=RW.Tools.BlankRow()
=RW.Tools.BlankRow(TRUE, 3)
=RW.Tools.BlankRow(TRUE, 2, "blank", FALSE)
=RW.Tools.BlankRow(TRUE, 2, "copyformat")
=RW.Tools.BlankRow(TRUE, 1, "blank", TRUE, 18)
=RW.Tools.BlankRow(A1, 2)

When an argument is wrong. Invalid arguments never fail the report. Reportworq logs a warning and uses a safe default.

Situation Result
count is fractional, for example 1.5 Warning, treated as 0, nothing inserted.
count is negative Warning, treated as 0, nothing inserted.
count is over 1000 Warning, capped to 1000.
mode is neither accepted value Warning, defaults to "blank".
rowHeight is negative or non-numeric Warning, height not set.

RW.Tools.PageBreak#

Inserts a horizontal or vertical page break relative to the row or column holding the formula.

Signature

=RW.Tools.PageBreak(doBreak, [mode], [position])
# Argument Type Default Meaning
1 doBreak Boolean required for any effect TRUE inserts the break.
2 mode String or Boolean "horizontal" "horizontal" or TRUE, "vertical" or FALSE.
3 position String or Boolean "before" "before" or TRUE, "after" or FALSE.

A horizontal break lands at the formula row, and an after break one row later. A vertical break lands at the formula column, and an after break one column later.

A break that conflicts with Fit To Pages is rejected. If the sheet's print setup is set to fit a number of pages tall or wide, the conflicting break direction does not take effect.

Passing FALSE does nothing. It does not remove a page break that is already in the template.

Place the formula after your dynamic data area so the break tracks the generated layout. The Boolean forms work, but the string forms read better in a template.

Examples

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

RW.Tools.Resize#

Auto-fits the row height or column width at the formula's location.

Signature

=RW.Tools.Resize([type], [pad])
# Argument Type Default Meaning
1 type String or Boolean "rows" "rows" or TRUE resizes the row, "columns" or FALSE the column.
2 pad Number 0 Extra size added after the auto-fit. Only numeric values have any effect.

Merged cells are auto-fitted. When the resize cell is part of a merged range, the function fits the whole merged range, and even a single unmerged cell has each line fitted correctly. Wrap Text must be on, and the merged autofit keys off the RW.Tools.Resize cell itself being part of the merged range. This is what makes it work for wrapped narrative, for example a TM1 commentary cube cell rendered to PDF. There is no longer any "cannot resize merged cells" limitation.

Hidden rows and hidden columns are skipped.

Examples

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

RW.Tools.RemoveFormula#

Replaces formulas with their current values across a cell, range, row, column, or multi-range selection. Use it to freeze calculated results before distribution.

Signature

=RW.Tools.RemoveFormula(doRemove, targetRange, [mode])
# Argument Type Default Meaning
1 doRemove Boolean required TRUE converts formulas to values.
2 targetRange Cell, range, row, column or multi-range required The area to process.
3 mode String or Boolean "all" "all" or TRUE removes every formula; "provider" or FALSE removes only TM1 provider formulas.

Entire-row references expand to every used column on the sheet, and entire-column references to every used row. Multi-range references such as {A1:A2,B3:B4} are supported. Non-formula cells are ignored.

Use provider mode when you want to strip provider formulas but keep the workbook's own Excel formulas working.

Examples

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

When an argument is wrong

Situation Result
targetRange omitted Formula error. At least two arguments are required.
You expected blank cells to change Non-formula cells are ignored.
You used provider mode on ordinary formulas Those formulas are left in place.

The three sheet functions#

RW.Tools.SheetName, RW.Tools.SheetColor and RW.Tools.SheetSuppress set a worksheet property from inside Excel, for cases where the value is only known at run time. All three share one rule set:

RW.Tools.SheetName#

=RW.Tools.SheetName(value)

value must evaluate to a valid worksheet name: not blank or whitespace, and still legal after Reportworq applies its normal sheet-name sanitization, which means it has to satisfy Excel's length limit and disallowed-character rules.

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

Error messages you may see:

RW.Tools.SheetName was ignored because Sheet Name is configured in the Reportworq worksheet options.
Multiple RW.Tools.SheetName formulas were found on this worksheet. Only one is allowed, so no formula-based sheet name was applied.
RW.Tools.SheetName did not return a valid worksheet name.

RW.Tools.SheetColor#

=RW.Tools.SheetColor(value)

value can be an integer color value, a hex color such as #FF0000, or a named color such as Red.

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

A value that cannot be applied leaves the tab color unchanged and flags the formula, for example:

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

RW.Tools.SheetSuppress#

=RW.Tools.SheetSuppress(value)

TRUE or 1 suppresses the sheet. Any other value leaves it in place.

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

The third example is the useful pattern: drop a whole sheet from the output when the data behind it failed a check.

Import-by-name and copied references#

These rules apply when a data importer targets a table or a copied range, and they are repeated here because they surface as layout problems:

Use cases#

Notes and limits#

Feedback on this page

Comments, questions, requests, or something missing or unclear? Email us - the page you are on is filled in for you.

Email feedback on this page

Or write to support@reportworq.com directly.