Relational database functions#
The relational family reads data from, and writes data back to, SQL, OLE DB and ODBC sources. RW.Relational.Export imports a query result into the workbook; the update and upsert functions write values back during input submission.
| Function | Alias | What it does |
|---|---|---|
| RW.Relational.Export | RWSQL, RWRELATIONALEXPORT |
Runs a query and imports the result into a range or named table |
| RW.Relational.Update | RWSQLUPDATE |
Updates the row matching a key column and value |
| RW.Relational.Upsert | RWSQLUPSERT |
Same as Update, but inserts the row when the key is missing |
Before you use these functions#
- A configured SQL, OLE DB or ODBC connection must exist, referenced by name in the
connectionNameargument. An admin sets this up; see Connect SQL, OLE DB and ODBC. RW.Relational.Exportruns during report generation. The write-back functions run during input submission, not the output pass.- The functions are inert in desktop Excel. They only run inside a Reportworq job or form submission.
RW.Relational.Export#
Executes a SQL query and imports the result set into an Excel range or named table.
Signature
=RW.Relational.Export(targetRange, connectionName, query)
| # | Argument | Type | Meaning |
|---|---|---|---|
| 1 | targetRange |
Range or named table | Where the result is written. A table target imports into the table body. |
| 2 | connectionName |
String | The configured SQL connection name. |
| 3 | query |
String | The SQL query text. |
All three are required.
Behavior
- A one-time report-generation formula. It runs before the layout functions, so
RW.Tools.Sort,RW.Tools.BlankRowandRW.Tools.Suppressall operate on the fully expanded result. - On success the formula cell is set to the query text that produced the data, rather than being cleared. That is how you can see, in the delivered workbook, exactly what was run.
- With a plain range target, the result is written starting at the supplied range. Leave enough room for the full result set.
- A multi-row range target is repopulated by a clear, delete, then repopulate cycle. It does not overwrite in place, and it does not disturb cells below or to the right of the range.
Targeting a named table
Use a table target when you want Excel tables, structured references, or downstream charts that grow with the result set.
A named table target must have "Show Header Row" enabled. The import writes into the table body below the header; without a header row it has nowhere to place the data.
For a copied formula, wrap a relative target in INDIRECT("...") so the reference does not drift as the formula is filled down. The modern alternative is to copy the sheets as a group, which rewrites cross-sheet references correctly.
Examples
=RW.Relational.Export(A1, "DefaultConnection", "SELECT * FROM Sales WHERE Year = 2026")
=RW.Relational.Export(SalesTable, "WarehouseDB", "SELECT CustomerID, Amount FROM Orders")
When an argument is wrong
| Situation | Result |
|---|---|
| Invalid target range or table name | Formula error before the query runs. |
| Blank query text | Formula error. |
| The query returns more data than expected | The imported data expands into the target area. |
RW.Relational.Update#
Writes values back to SQL by updating the row that matches a key column and key value.
Signature
=RW.Relational.Update(connectionName, table, idColumnName, idColumnValue, columnName1, value1, [columnName2, value2], ...)
| # | Argument | Type | Meaning |
|---|---|---|---|
| 1 | connectionName |
String | The configured SQL connection. |
| 2 | table |
String | The table to update. |
| 3 | idColumnName |
String | The key column used in the match condition. |
| 4 | idColumnValue |
Value | The key value to match. |
| 5 and up | columnName, value |
Repeating pairs | One or more columns to update. |
At least six arguments are required, so that at least one column/value pair is present. Append further pairs in the same formula for additional columns.
Behavior
- Processed during input submission, not the report-output pass. The formula cell is cleared on success.
- Reportworq records tuple-log information for the connection, table, key and updated columns.
- The row must already exist. Unlike Upsert, Update never inserts.
Examples
=RW.Relational.Update("DefaultConnection", "Users", "UserID", 123, "Email", "new@email.com")
=RW.Relational.Update("CRM", "Accounts", "AccountID", A1, "Manager", B2, "Territory", C3, "Priority", "High")
When an argument is wrong
| Situation | Result |
|---|---|
| Fewer than six arguments | Formula error during parsing. |
| Blank connection, table, key name, or key value | Formula error during parsing. |
| An odd number of trailing column/value arguments | The unmatched trailing item is silently ignored. Check your pairs; a mistyped column writes nothing and reports nothing. |
RW.Relational.Upsert#
Writes values back to SQL by updating an existing row, or inserting a new row when the key does not yet exist.
Signature
=RW.Relational.Upsert(connectionName, table, idColumnName, idColumnValue, columnName1, value1, [columnName2, value2], ...)
The arguments are identical to RW.Relational.Update. Upsert shares the same parser and the same rules, including the six-argument minimum, the cleared formula cell, the tuple logging, and the silently ignored odd trailing argument. The one difference is that Reportworq enables insert-on-missing-key because the function name is RW.Relational.Upsert.
Use Upsert when the workbook may create brand-new rows as part of contribution processing, for example a forecast ID that does not exist yet. Use Update when the target row must already exist and you want a missing row to be a no-op rather than a new record.
Examples
=RW.Relational.Upsert("AnalyticsDB", "Forecasts", "ForecastID", "Q3_2026", "Revenue", 5000000, "Confidence", 0.95)
=RW.Relational.Upsert("CRM", "Accounts", "AccountID", A1, "Status", "Active", "Territory", B2)
Check that the target database and connection actually permit inserts before relying on this.
Splitting a long query across cells#
A SQL query too long for one Excel cell can exceed Excel's roughly 8,192-character formula limit. Break it across several cells and concatenate them, for example =$A$1&$A$2&$A$3.
- Every fragment cell must start with
=. A fragment cell that does not begin with=embeds its leading quote character literally into the query string, and the query then fails silently. Make each fragment its own formula. - For SQL, add table aliases on ambiguous columns so the assembled query is valid.
- Validate the concatenated output in a query tool before you deploy. You can use
IF(...)across the fragments to switch between queries.
Use cases#
- SQL warehouse extract.
RW.Relational.Exportimports orders into a named table so downstream charts grow with the result, thenRW.Tools.RemoveFormulafreezes the values before the file is emailed. - Planning write-back. After a review,
RW.Relational.Upsertwrites updated forecast rows back to the planning database, inserting any new forecast IDs. - Corrections round. A contribution form collects amended values, and
RW.Relational.Updatewrites them to rows that are known to exist, so a typo in a key never silently creates a record.
Notes and limits#
- Update requires the keyed row to already exist. Use Upsert when the row may be new.
- Keep write-back formulas out of the delivered output. They render as
#NAMEin a contribution form, which is why their rows and columns are hidden by design. - All three functions date from around v5.0.0.60. Version availability is approximate.
Related pages#
- Functions overview: which one do I use
- IBM Planning Analytics functions for the MDX equivalent.
- General report and layout functions for shaping the imported data.
- Contribution functions for the form controls these write-back formulas sit behind.
- Connect SQL, OLE DB and ODBC for the connection setup.
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 pageOr write to support@reportworq.com directly.