Create A Support Ticket

Please use the form below to submit a support request.

    Upload a file (optional)

    View Categories

    Excel Functions – Relational Data Sources

    2 minute(s) read

    Data Collection #

    Reportworq supports several functions that are used to “writeback” data into configured SQL data sources. These formulas are only available and executed when Reportworq receives a file for processing using a Reportworq Data Collection license.

    SQL Datasources (SQL Server, ODBC or OleDB) #

    The functions described in this section are specific to SQL datasources (SQL Server, ODBC or OleDB).

    RWSQL #

    The RWSQL function returns data to an Excel spreadsheet from a SQL Server, ODBC or OLE DB data source, using a named Reportworq data source connection and SQL query.

    Syntax

    RWSQL(Range or Table Name, Connection Name, SQL Query)

    ParameterDescription
    Range or Table NameA reference to an Excel table or a cell range. See Excel Ranges or Table Names for more information.
    Connection NameThe name of an existing ODBC or OLE DB Connection that is defined in Reportworq, which will be used to retrieve data for the RWSQL formula. For more information on the configuration of these connections, please see Datasources -> SQL Sources.
    SQL QueryThe query to execute against the datasource provider. The query must only return a single dataset.

    Examples

    The following examples are based on an SQL Datasource connection named “SampleData” in Reportworq.

    FormulaDescription
    =RWSQL( “Table 4”, “SampleData”, “Select * FROM Stores”)Export all data from the “Stores” database table/view to the Excel table named “Table 4”, matching data in fields based on Excel table column name.
    =RWSQL( A2, “SampleData”, “Select * FROM Stores”)2 Possible Results:
    – Export all data from the “Stores” database table to cell A2, overwriting any existing data to the right and below that cell.
    – If A2 is within an existing Excel table then update in the manner described in the named “Table 4” example.
    =RWSQL( A2:D10, “SampleData”, “Select * FROM Stores”)Export all data from the “Stores” database table to the range A2:D10, clearing and resizing that range to fit the query result.

    RWSQLUPDATE/RWSQLUPSERT #

    The RWSQLUPSERT & RWSQLUPDATE functions are both designed to update data in a single record of a relational database table when using the Data Collection capabilities of Reportworq and a valid SQL Server, ODBC or OLE DB SQL database connection. The differences between the functions are:

    • RWSQLUPDATE: can only update an existing record.
    • RWSQLUPSERT: can both insert and update a record.

    Both functions require the following information:

    • The name of the saved SQL database connection in Reportworq.
    • The table name being updated.
    • A way to uniquely locate the record:
      • The name of an Id column.
      • The value to search for in that Id column.
    • A minimum of 1 column name/value combination indicating the value(s) to update in that record.

    These functions will fail to update data if the Reportworq connection credentials do not have write access to the table or:

    • RWSQLUPDATE, RWSQLUPSERT: The Id Column lookup returns more than one record.
    • RWSQLUPDATE: The Id Column lookup returns no records.

    Syntax

    RWSQLUPDATE / RWSQLUPSERT ( Connection Name, Table Name, Id Column Name, Id Column Value, Column Name / Value List )

    ParameterDescription
    Connection NameThe name of an existing ODBC or OLE DB Connection that is defined in Reportworq, which will be used to update data using the RWSQLUPDATE formula. This connection must have WRITE access to the specified Table Name. For more information on the configuration of these connections, please see Datasources -> SQL Sources.
    Table NameThe name of the table being updated. For example, TimeEntry.
    Id Column NameThe name of the column that is used to uniquely search for a single record in the table. For example, EmployeeIdAndPeriod.
    Id Column ValueThe value is used to uniquely identify the row in the Id Column for updating. For example, John Snow_20220108.
    Column Name / Value listA minimum of one combination of table Column Name and Column Value which are to be updated by the SqlUPDATE function. For an example, see the highlighted section of the following formula:

    =RWSQLUPDATE(“MySqlDatabase”, “TimeEntry”, “EmployeeIdAndPeriod”, “John Snow_20220108”, “Hours”, 12, “Comments”, “Generated Report Packages without Reportworq”)