Connect SQL, OLE DB, and ODBC#

Reportworq reads from relational databases through three built-in connectors: SQL Server, generic OLE DB, and generic ODBC. All three expose tables, views, schemas, and columns, and back either a table pick or a raw SQL query in a data model. This guide covers configuring the connection, keeping credentials out of the connection string, and the driver and timeout traps that cause most relational failures.

Before you begin#

• You are an administrator, working in Settings > Integrations > DATASOURCES. See Connect a data source for the general add-and-test flow.
• The database is reachable from the Reportworq server.
• The driver or provider is installed on the Reportworq server, not just your workstation. For ODBC this means a 64-bit driver; see the DSN traps below.
• You have credentials, or for Windows and trusted authentication, the server's service account has the required database permissions.

Configure a SQL Server connection#

1. Add the SQL Server connector and open its editor.
2. Enter the server, database, and credentials.
3. Optionally set a Validation Query, the query that Test connection runs to verify the connection.
4. Save and select Test connection. A valid, reachable database returns "Success."

Configure an OLE DB or ODBC connection#

OLE DB and ODBC take a freeform Connection String rather than discrete server and database fields.

1. Add the OLE DB or ODBC connector and open its editor.
2. Enter the Connection String for your provider or driver.
3. Keep credentials out of the string using the substitution variables below.
4. Optionally set a Validation Query for the connection test.
5. Save and select Test connection.

Keep credentials out of the connection string#

Do not put a username and password directly in the connection string. Instead, put the placeholders %USERNAME% and %PASSWORD% in the string and enter the real values in the separate masked Username and Password fields. Reportworq substitutes them at connect time, so the secrets never appear in the visible or stored string, and they are stored encrypted. Copy-links in the editor insert each token for you.

Connection-string examples#

SQL Server takes discrete server, database, and credential fields rather than a connection string. OLE DB and ODBC take a freeform string that depends on the provider or driver installed on the Reportworq server. The examples below use the credential placeholders; substitute the driver or provider name for the one actually installed on your server.

• SQL Server via OLE DB (Microsoft OLE DB Driver): Provider=MSOLEDBSQL;Data Source=DBSERVER;Initial Catalog=Finance;User ID=%USERNAME%;Password=%PASSWORD%;
• SQL Server via ODBC: Driver={ODBC Driver 18 for SQL Server};Server=DBSERVER;Database=Finance;UID=%USERNAME%;PWD=%PASSWORD%;
• PostgreSQL via ODBC: Driver={PostgreSQL Unicode(x64)};Server=dbhost;Port=5432;Database=finance;Uid=%USERNAME%;Pwd=%PASSWORD%;
• MySQL via ODBC: Driver={MySQL ODBC 8.0 Unicode Driver};Server=dbhost;Database=finance;User=%USERNAME%;Password=%PASSWORD%;
• CSV or flat file via the Microsoft Text Driver (OLE DB or ODBC): point the driver at the folder holding the file, then read it with the =RWSQL function so the report re-pulls the file on every run instead of a manual copy and paste.

Configure a SQLite connection#

SQLite is a built-in relational connector for a file-backed SQLite database, on the same base as the SQL connectors. It appears in the catalog as SQLite.

1. Add the SQLite connector and open its editor.
2. Enter the Connection String, normally just a file path in the form Data Source=<path>, for example Data Source=C:\Reportworq\data\finance.sqlite.
3. If the file needs credentials, use the same %USERNAME% and %PASSWORD% placeholders and masked fields as OLE DB and ODBC.
4. Save and select Test connection.

The file path resolves on the Reportworq server, not on your workstation, so the database file must be readable by the server's service account. SQLite defaults its row-cap dialect to LIMIT. For a shared, multi-writer database, use a server database instead of a SQLite file.

Advanced Options: timeouts#

The collapsible Advanced Options section carries two timeouts:

• Command Timeout, in seconds. A value of 0 means wait indefinitely. Set a non-zero value on any source where a runaway query should be bounded.
• Connection Timeout, in seconds.

There is also a connection-level Limit Query Type dropdown that selects the row-cap dialect the connector emits: SELECT TOP for SQL Server, LIMIT for MySQL or PostgreSQL, FETCH FIRST n ROWS ONLY for Oracle or DB2, or Unavailable when the source has no row-cap clause.

Query the source in a data model#

Once the connection tests, a model author builds views over it on the model's Connection tab, choosing a Query type:

• Table or View picks a table or view from a filterable list. Table mode can select many tables at once to create several views in one action.
• SQL Query writes raw SQL in the editor, with a Format action. A SQL script can reference fields from any tables in the datasource, and supports {{variable}} double-brace tokens. Preview it before applying it to the model.

Text variables in SQL need single quotes: a {{var}} is substituted as a raw text replace with no automatic quoting, so a value that must become a SQL string literal has to be wrapped, for example '{{state}}' resolving to 'Maine'. Numeric values are bare.

Connect CData sources#

Several connectors reach their source through a CData driver. CData drivers wrap an API or SaaS system and present it to Reportworq as a relational, tabular source, so you query it with the same table or SQL modes as any database. Reportworq uses CData so that systems with no native SQL interface, such as Salesforce or Jira, can still feed a data model and be refreshed on every job run.

Supported CData connectors#

Reportworq ships CData drivers for the sources below. Each name links to CData's own ADO.NET documentation for that driver, which is the authoritative reference for its connection properties, the tables it exposes, and the authentication options it supports. Go there whenever you need a property that is not on the Reportworq form.

One source customers sometimes expect here is handled differently: Snowflake ships as a pre-configured OLE DB connector, not CData. See Configure an OLE DB or ODBC connection.

Configure a CData connection#

1. Add the CData connector you need and open its editor.
2. Provide the connection details. Most drivers accept a standard connection string of CData connection properties, and most also support OAuth for authentication. For an OAuth driver, complete the interactive Authenticate step, or supply the service-principal or client credentials the driver requires, so Reportworq can obtain and refresh a token.
3. Where the driver exposes separate credential fields, enter secrets there rather than in the visible connection string.
4. Save and select Test connection.
5. Query the source in a data model through table or SQL mode, exactly as for the SQL connectors above.

For per-connector traps, such as the Salesforce security token or the Power BI XMLA workspace and Azure app registration, see the Connector catalog.

Find the connection properties for a driver#

Each CData driver has its own connection properties and authentication options. To find them, start from the CData drivers documentation, open the driver for your source, then open its ADO.NET online documentation, which lists the connection-string properties and the OAuth setup for that source.

Notes and limits#

• ODBC DSN traps. The ODBC driver must be installed on the Reportworq server. The DSN must be 64-bit (a 32-bit DSN fails under the 64-bit service) and a System DSN (a User DSN can be invisible to the service account). For Windows or trusted authentication, the server's service account needs the database permissions. ODBC is the common path for most databases; OLE DB is mainly for Microsoft providers.
• A very long SQL query in one Excel cell. The relational query function is =RWSQL (internally RW.Relational.Export). If a SQL string is too long for one Excel cell, split it across several cells and concatenate them in the =RWSQL argument (for example =$A$1&$A$2&$A$3). Every fragment cell that is a formula must start with =, otherwise its leading quote is embedded literally into the string and the query silently fails. Validate the reassembled SQL in a database tool before deploying, and watch for missing table aliases once the fragments are joined.
• CData sources build on this base. Many API and SaaS systems (Salesforce, Jira, NetSuite, and more) are exposed as relational datasources through CData drivers on this same base. See Connect CData sources above and the Connector catalog. Note that Snowflake ships as OLE DB, not CData.

Going deeper. To curate columns into governed business fields, set filters, and publish a view for report authors, see Data models.