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:
| Reference Target | Description | Examples |
| Excel Table by Name | Reportworq will search for an Excel table with this name and repopulate the table with data for any table heading names that match data source column names. The table must have “Show Header Row” enabled. Please see a note below about referencing tables when bursting to multiple worksheets | “Table 3” |
| Excel Table by Reference | When using a parameter to create multiple copies of a worksheet containing the Excel table you want to reference, it is not possible to reference the table name as a string because copies of it will get a new ID on each worksheet. Instead, you must: – Reference a cell in the data range of the table (not a column heading). – Use the INDIRECT formula in any custom-calculated columns to make relative references to other values in the table’s data. This is needed because when the worksheet is copied any formula references to the table will otherwise point to the wrong table. For example, to reference the cell in the previous column: =INDIRECT(ADDRESS(ROW(), COLUMN() – 1)) | A2 A2 in this example is the first data value in a table that spans A1:G15 and has the required header row enabled. |
| Single Row / Cell | Reportworq will populate data starting with the top left cell address, overwriting anything below and to the right of that cell. | B10 B:B |
| Multi-Row Range | Reportworq will clear and delete the range before inserting rows required to populate data, and will not overwrite anything below and to the right of that range. | A1:M30 B:Z |
General Purpose #
The functions described in this section are not specific to any particular datasource.
RWPAGEBREAK #
The RWPAGEBREAK function inserts a horizontal or vertical page break at the cell where the formula is placed, at report generation time. This allows page breaks to be driven dynamically by report data, for example, inserting a page break after each group of rows rather than relying on fixed, manually-placed breaks in the template.
RWPAGEBREAK does not remove or replace any existing page breaks defined in your Excel workbooks.
If the worksheet’s page setup is configured to fit to a set number of pages tall (for horizontal breaks) or fit to a set number of pages wide (for vertical breaks), inserting a page break will throw an error, since the two settings are incompatible. Ensure the worksheet uses a percent-scale page setup when using RWPAGEBREAK
Syntax
RWPAGEBREAK( Enable, [Direction], [position] )
| Parameter | Description |
| Enable | Whether or not to insert a page break in the row or column (defined by Direction) where the RWPAGEBREAK function is located. This is typically a formula that references a value in the report indicating a new section. When TRUE, the page break is inserted. When FALSE, no page break is added and the formula is a no-op. |
| Direction | Permitted values for Direction are: – TRUE or “horizontal” for row page breaks. Inserts a break above the row containing the function. – FALSE or “vertical” for column page breaks. Inserts a break left of the column containing the function. The default value for this optional parameter, if omitted, is TRUE or “horizontal”. |
| Position | Optional parameter and indicates if the pagebreak should be before or after the row/column number. – If set to true or the word “Before” the pagebreak will occur before the current row/column – If set to false or the word “After” the pagebreak will occur after the current row/column The default value for this optional parameter, if omitted, is TRUE or “Before”. |
Examples
| Formula | Description |
| =RWPAGEBREAK ( TRUE ) =RWPAGEBREAK( TRUE, TRUE ) =RWPAGEBREAK ( TRUE, “Horizontal”) | Inserts a horizontal page break at the boundary between row 39 and 40. |
| =RWPAGEBREAK( TRUE, FALSE) =RWPAGEBREAK ( TRUE, “Vertical”) | Inserts a vertical page break at the boundary between column M and N. |
| =RWPAGEBREAK( FIND(“Summary”, $B40)=1, “Horizontal”) | Inserts a horizontal page break at the boundary between row 39 and 40 if the value in cell B40 contains the text “Summary”, which would indicate a heading and page break boundary. |
| =RWPAGEBREAK( TRUE, TRUE, FALSE ) =RWPAGEBREAK(TRUE, “horizontal”, “after” =RWPAGEBREAK( TRUE, TRUE, “after” ) | Inserts a horizontal page break after row 40. |
| =RWPAGEBREAK( TRUE, FALSE, FALSE ) =RWPAGEBREAK(TRUE, “vertical”, “after” =RWPAGEBREAK( TRUE, FALSE, “after” ) | Inserts a vertical page break after column N. |
RWREMOVEFORMULA #
The RWREMOVEFORMULA function removes formulas from a specified range of cells at report generation time, replacing them with their static values.
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 RWREMOVEFORMULA.
Syntax
RWREMOVEFORMULA( Enable, Range, Mode)
| Parameter | Description |
| Enable | When TRUE, formula removal is performed. When FALSE, the formula is a no-op and nothing is changed. |
| Range | The cell, range, entire row, entire column, or multi-area range to process. Accepts formats like A1, A:A, 1:1, or {A1:A2, B3:B4}. |
| Mode | Optional parameter that defines whether to remove all formulas, or to remove only formulas from the provider. Permitted values are as follows: – TRUE or “all” — All formulas are removed. – FALSE or “provider” — Removes only formulas from the provider. If this parameter is omitted, the default is TRUE/”all” removing all formulas. |
Examples
| Formula | Description |
| =RWREMOVEFORMULA(TRUE, A1:D4) =RWREMOVEFORMULA(TRUE, A1:D4, TRUE) =RWREMOVEFORMULA(TRUE, A1:D4, “all”) | Removes all formulas from cell range A1:D4. |
| =RWREMOVEFORMULA(TRUE, {A1:A2, B3:B4}) =RWREMOVEFORMULA(TRUE, {A1:A2, B3:B4}, TRUE) =RWREMOVEFORMULA(TRUE, {A1:A2, B3:B4}, “all”) | Removes all formulas from multiple ranges. |
| =RWREMOVEFORMULA(TRUE, (A:A, G6, 1:1), FALSE) =RWREMOVEFORMULA(TRUE, (A:A, G6, 1:1), “provider”) | Removes provider formulas from column A, cell G6, and row 1. |
| =RWREMOVEFORMULA(FALSE, A1:D4) | The formula does nothing because the Enable parameter is set to FALSE. |
RWRESIZE #
The RWRESIZE function 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.
Typical use case is placing RWRESIZE in a helper column alongside rows that contain dynamic, potentially multi-line text content like comments, descriptions, or AI-generated narrative. As the report renders and populates those cells, RWRESIZE ensures the row expands to show the full content without manual intervention. Another typical use case is to resize columns to avoid a ##### display when a cell is not large enough to display a numeric value.
RWRESIZE fully supports 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. This prevents accidentally unhiding rows/columns or applying sizing to rows that won’t be visible.
Syntax
RWRESIZE( [Axis], [Pad] )
| Parameter | Description |
| Axis | Permitted values for Axis are: – TRUE or “rows” for auto-fit row heights. – FALSE or “columns” to autofit column widths. The default value, if omitted, is TRUE / “rows”. |
| Pad | Optional additional height or width to add to the autofit algorithm. Useful for adding breathing room around content. |
Examples
| Formula | Description |
| =RWRESIZE() =RWRESIZE( TRUE ) =RWRESIZE( “rows” ) | Resizes the row’s height to auto-fit to data in the row’s cells. |
| =RWRESIZE( FALSE ) =RWRESIZE( “columns” ) | Resizes the column’s width to auto fit to data in the row’s cells. |
| =RWRESIZE(TRUE, 5) =RWRESIZE(“rows”, 5) | Resizes the row’s height to auto-fit to data, and adds 5 additional points to that height. |
| =RWRESIZE( “columns”, 2 ) | Resizes the columns width to auto fit to data, and adds 2 additional points to that width. |
RWSORT #
The 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.
Unlike Excel’s built-in SORT function, RWSORT operates as a post-processing step — the sort is physically applied to the workbook data when the report runs, making it compatible with dynamic report data populated by other RW formulas.
Syntax
RWSORT( Range or Table Name, [Sort Column 1], [Sort Order 1], [Sort Column 2], [Sort Order 2], [Sort Column 3], [Sort Order 3], [Top/Bottom], [N for Top/Bottom] )
| Parameter | Descritpion |
| Range or Table Name | A reference to an Excel table or cell range that represents the data to be sorted. See Excel Ranges or Table Names for more information. |
| Sort Column 1, Sort Column 2, Sort Column 3 | Optional Sort Column parameters are an integer index indicating which column to sort in the specified range or table. 1 for the first column, 2 for the second, etc. The default column is 1. |
| Sort Order 1, Sort Order 2, Sort Order 3 | Optional Sort Order parameters indicate the sort direction for a corresponding Sort Column: – TRUE: Ascending order – FALSE: Descending order The default sort order is TRUE (ascending). |
| TOP/BOTTOM, N | After sorting, limits the results to the top or bottom N rows. This optional parameter must be followed by N which is the number of rows to keep when using TOP or BOTTOM. *this optional parameter is coming soon* to accomplish a similar functionality you can combine RWSORT with an RWSUPPRESS function to hide or delete rows after sorting based on condition. |
Examples
| Formula | Description |
| =RWSORT(A1:D30) =RWSORT(A1:D30, 1, TRUE) =RWSORT(“Table 1”) =RWSORT(“Table 1”, 1, TRUE) | Sorts data in the range A1:D30 or Excel table named “Table 1” based on the first column in ascending order. |
| =RWSORT(A1:D30, 1, TRUE, 3, TRUE) =RWSORT(“Table 1”, 1, TRUE, 3, TRUE) | Sorts data in the range A1:D30 or Excel table named “Table 1” based on the first column in ascending order, followed by the third column in ascending order. |
| =RWSORT(A1:D30, 1, TRUE, 2, TRUE, 3, FALSE) =RWSORT(“Table1”, 1, TRUE, 2, TRUE, 3, FALSE) | Sorts data in the range A1:D30 or Excel table named “Table 1” based on the first column in ascending order, then by the second column in ascending order, followed by the third column in descending order. |
| =RWSORT(A1:D30, 1, TRUE, “TOP”, 5) =RWSORT(“Table 1”, 1, TRUE, “TOP”, 5) | Sorts data in the range A1:D30 or Excel table named “Table 1” based on the first column in ascending order, return only the top 5 rows. |
| =RWSORT(A1:D30, 1, TRUE, “BOTTOM”, 5) =RWSORT(“Table 1”, 1, TRUE, “BOTTOM”, 5) | Sorts data in the range A1:D30 or Excel table named “Table 1” based on the first column in ascending order, return only the bottom 5 rows. |
Note: When “TOP” or “BOTTOM” is provided, after sorting is applied, rows outside the number requested are hidden not deleted. If the N is greater than or equal to the total number of rows in the range, then no rows are hidden.
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.
Syntax
RWSUPPRESS( Enable, [Axis], [Mode] )
| Parameter | Description |
| Enable | Provide a TRUE or FALSE to enable suppression for this row or column. This is often a formula that performs a zero check but can be any formula that evaluates to TRUE or FALSE. For example, SUM(3:3)=0. |
| Axis | Optional parameter that defines whether the row or column of the cell containing the RWSUPPRESS formula is to be suppressed when Enable is TRUE. Valid parameter values are: – TRUE or “rows” to suppress the row. – FALSE or “columns” to suppress the column. The default axis is “rows”. |
| Mode | Optional parameters that define the suppress mode when Enable is TRUE: – TRUE or “delete”: Deletes the row/column. – FALSE or “hide”: Hides the row/column. The default mode is “delete”. |
Examples
| Formula | Description |
| =RWSUPPRESS(FALSE) | Does nothing to the current row. |
| =RWSUPPRESS(TRUE) =RWSUPPRESS(TRUE, TRUE, TRUE) =RWSUPPRESS(TRUE, “rows”, “delete”) | Delete the current row. |
| =RWSUPPRESS(TRUE, FALSE) =RWSUPPRESS(TRUE, FALSE, TRUE) =RWSUPPRESS(TRUE, “columns”, “delete”) | Delete the current column. |
| =RWSUPPRESS(TRUE, “rows”, FALSE) =RWSUPPRESS(TRUE, “rows”, “hide”) | Hide the current row. |
| =RWSUPPRESS(TRUE, FALSE, FALSE) =RWSUPPRESS(TRUE, “columns”, “hide”) | Hide the current column. |