SQL Sources

Overview

Microsoft Excel supports several different types of data connections, data models, and power query modeling features. These data connections are often used to load relational database data into an Excel Table (List Object), Pivot Table, or Pivot Chart. ReportWORQ is not currently able to reuse these Excel-defined data connections, but ReportWORQ does support loading data into a Table or Range using proprietary ReportWORQ Excel functions like RWSQL that can extract, load, or refresh data from SQL-based datasources, such as Microsoft SQL Server, Oracle, DB2, and more. 

Because RWSQL functions can dynamically reference values in Excel workbooks, you can use ReportWORQ Parameters to copy sheets and adjust values referenced in SQL queries in your Excel reports. ReportWORQ additionally supports SQL-specific Parameter types, driven by SQL queries.

This topic covers the configuration of a connection to ODBC or OleDB drivers in ReportWORQ:

• Prerequisites

  • Driver Installation

  • Service Account Configuration

• Creating Connection Strings

• Configuring a Connection

  • ODBC Connections

  • OleDB Connections

  • Troubleshooting Connection Problems

• SQL-Specific ReportWORQ Parameters

Prerequisites

ODBC & OleDB Drivers

To create connections to SQL datasources in ReportWORQ you must have any required ODBC or OleDB drivers installed and configured on the machine where ReportWORQ is installed and running. Databases like Microsoft SQL Server often have ODBC and OleDB drivers installed, by default, with the operating system. Please check with your specific database vendor for supported ODBC or OleDB drivers, and download and install them if you do not already have them already.

Service Account Configuration

If your SQL connections need to be defined using a specific domain user account (versus a database-specific username and password), you will need to make sure the ReportWORQ Server application is configured to run as a user account that has been granted permissions to that database. Information on configuring the ReportWORQ Service account can be found in the Installation documentation, under Service Settings.

Creating Connection Strings

It is important to understand how to construct the connection strings required for the database that you will connect to ReportWORQ. Connection string syntax for OleDB and ODBC sources come with a variety of options, too numerous to list here. Different SQL datasources support the following parameters in a connection string, but often with different parameter names:

• Stored vs. trusted credentials (i.e., a user id + password stored in the string vs. running as the currently logged-in user)

• Specific drivers & driver versions

• Default database/catalog

• Other driver-specific connection properties

Crafting these strings by hand can be very challenging, so included here are a couple of tips for getting a valid connection string to use with ReportWORQ.

Tip 1: Use Online SQL Connection String Resources

Because not all drivers and databases are alike we recommend referring to your specific database's documentation or general connection string reference sources for examples of connection string options. The latter has sample connection strings for practically any SQL datasource, and ReportWORQ should support any of them, provided you have the specified drivers and use an example for an ODBC or OleDB sample connection string.

Tip 2: Use System or File DSNs

Another timesaver is to create a System or File Data Source Name (DSN) in Windows for the database connection ReportWORQ will use. This is done via the 64-bit ODBC Data Source Administrator tool from the machine where ReportWORQ is installed and provides helpful wizards to define and test connections to databases. The advantage of this approach is that it also helps verify that the driver that ReportWORQ will be using is installed and configured properly on the machine.

When using a System or File DSN, ReportWORQ can be configured using a very simple connection string (compared to non-DSN connection strings). For example, if you've created a DSN called "AdventureWorks", your connection string might look like this:

• Windows Authentication/Trusted Connection: DSN=AdventureWorks;Trusted_Connection=yes

• Specific User ID + Password: DSN=AdventureWorks;Uid=sa;Pwd=EyeLoveRep0rtWORQ!;

Configuration

To add ODBC or OleDB connection provider to the list of available Datasources, ReportWORQ administrators open the Administration screen then click the '+' button beside Datasources, and select the ODBC Provider or OleDB Provider option:

The specific connection details for either option are described below.

ODBC Connections

ODBC Connections are the most common SQL connection type since practically all databases have at least one supported ODBC driver available. When selecting an "ODBC Provider", this configuration opens on the right-hand side of the page:

• Datasource Name: This is the name that will be used to identify the connection by ReportWORQ in RWSQL, RWSQLUPDATE, and RWSQLUPSERT  formulas found in ReportWORQ Excel reports. The connection name must be unique. 

• Connection String: This is the connection string that ReportWORQ will use to connect to your database. See Creating Connection Strings for more information.

• Validation Query: This is a query that is used by ReportWORQ to verify that the Connection String is correct.

• Test: Clicking the '✓' button will either display a success message or show error messages with further information on missing or misconfigured settings.

• Save: The 'Save' button saves changes made to the connection. 

• The ODBC Datasource can be enabled or disabled by clicking the checkbox.

OleDB Connections

OleDB Connections are less common than ODBC and are most often used for Microsoft-supported data sources (like Excel and SQL Server). When selecting an "OleDB Provider", this configuration sidebar opens up on the right-hand side of the page: 

• Datasource Name: This is the name that will be used to identify the connection by ReportWORQ in RWSQL, RWSQLUPDATE, and RWSQLUPSERT  formulas found in ReportWORQ Excel reports. The connection name must be unique.

• Connection String: This is the connection string that ReportWORQ will use to connect to your database. See Creating Connection Strings for more information.

• Test Query: This is a query that is used by ReportWORQ to verify that the Connection String is correct.

• Command Timeout: This is an optional setting, defined in seconds, that is used to prevent ReportWORQ from waiting indefinitely for a query to return. The default of "0" means that ReportWORQ will wait indefinitely for a query response, whereas a value of 120 would mean ReportWORQ would wait for 2 minutes before canceling the query automatically if it had not received a response.

• Test: Clicking the '✓' button will either display a success message or show error messages with further information on missing or misconfigured settings.

• Save: The 'Save' button saves changes made to the connection. 

• The OleDB Datasource can be enabled or disabled by clicking the checkbox.

Troubleshooting Connection Problems

If you have confirmed that your connection string has valid syntax, the most common reasons SQL connections fail when testing or a ReportWORQ Job fails when running are:

1. The specified driver is incorrect or is not installed on the machine where ReportWORQ is running. If a driver version is specified, be sure that the connection string specifies the same version.

2. The credentials specified for the connection are incorrect.

3. The server location is incorrect or firewall settings prohibit you from connecting to that server.

4. If you use a trusted connection/Windows authentication, make sure the ReportWORQ Server service is running as a user that has access to the data source.

5. DSN Connections Only: 

   1. You created a 32-bit connection. You must use the 64-bit version of the ODBC Data Source Administrator tool to define your connection for ReportWORQ to be able to see it since ReportWORQ is a 64-bit application. 32-bit ODBC connections are not visible to ReportWORQ.

   2. You created a User DSN and not a System DSN. The ReportWORQ Server service account may not be able to see User DSNs.

SQL-Specific ReportWORQ Parameters

ReportWORQ has a SQL-specific Parameter type that can be used to select a list of items based on data from SQL Query, for instance, to generate multiple copies of a report for each distinct Product Type in a data warehouse. Please see Creating Jobs for more information on using Parameters in ReportWORQ.