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 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.
RWPAGEBREAK does not remove or replace any existing page breaks defined in your Excel workbooks.
Syntax
RWPAGEBREAK( Enable, [Direction] )
| 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. |
| 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 “Horizontal”. |
| Position | This 3rd argument is optional and indicates if the pagebreak should be before or after the row/column number. – If omitted the pagebreak will occur before the current row/column – 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 If the Excel fit to page option for height or width is set to a positive number (anything other than Automatic) then RWPAKEBREAK formula will throw a warning when used for horizontal or vertical pagebreaks respectively. |
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, TRUE, “After” ) | Inserts a horizontal page break after row 40 |
RWREMOVEFORMULA #
The RWREMOVEFORMULA function removes formulas from a specified range of cells. You can remove all formulas, or only formulas that originate from the provider. Removing unnecessary formulas can significantly reduce the size of a report.
Syntax
RWREMOVEFORMULA( Enable, Range, Mode)
| Parameter | Description |
| Enable | If TRUE, formulas are removed as determined by the other parameters. If FALSE, does nothing. |
| Range | A reference specifying the cells from which formulas may be removed, depending on the Mode parameter. |
| 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” — Formulas from the provider are removed. If this parameter is omitted, all formulas are removed. |
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, (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 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] )
| 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. This can be useful when certain formatting situations still result in too small of a resize to display the cell value, or when additional spacing between report rows or columns is desired. The value is represented in points. |
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( “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 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] )
| 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). |
Examples
| Formula | Description |
| =RWSORT(A1:D30) =RWSORT(A1:D30, 1, TRUE) | Sorts data in the range A1:D30 based on the first column in ascending order. |
| =RWSORT(“Table 1”) =RWSORT(“Table 1”, 1, TRUE) | Sorts data in the Excel table named “Table 1” based on the first column in ascending order. |
| =RWSORT(A1:D30, 1, TRUE, 3, TRUE) | Sorts data in the range A1:D30 based on the first column in ascending order, followed by the third column in ascending order. |
| =RWSort(“Table1”, 1, TRUE, 2, TRUE, 3, FALSE) | Sorts data in the 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. |
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. |