- 07 May 2022
- 3 Minutes to read
- Updated on 07 May 2022
- 3 Minutes to read
Microsoft Excel supports several different types of data connections, data models, and power query modeling features. These data connections are often used to load 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 a proprietary ReportWORQ RWSQL Excel Formula that can extract, load, or refresh data from SQL based datasources, such as Microsoft SQL Server, Oracle, DB2, and more. ReportWORQ currently supports connections that are based on ODBC and OleDB drivers. To define these connections in ReportWORQ you must define a named connection string for ReportWORQ to use.
The RWSQL formula instructs ReportWORQ which named connection string to use, a SQL Query to execute, and a location to place the data. Since the SQL Query is part of the formula it can be manipulated using Excel formula logic or ReportWORQ parameters. For example bursting a report to recipients may adjust the WHERE clause for each report. More information for SQL Connection support is discussed in the topic.
Defining a SQL Connection
Connections to SQL Sources are defined in ReportWORQ Settings, on the Connections tab. To add a new SQL Connection, choose one of these two options depending on the driver that you will be using:
OleDB connections are most often used for Microsoft-supported data sources (like Excel and SQL Server) and ODBC connections are typically available for all relational databases. For both connection types the manner in which you define the connection is to specify a valid connection string:
To validate a connection string, press the Test button on the connection dialog.
Tips for Creating Connection Strings
Connection string syntax for OleDB and ODBC sources come in a variety of options, which are too numerous to list here. For instance, many SQL datasources support the following parameters in a connection string, 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
- Driver-specific connection properties
Crafting these strings by hand can be very challenging, so here are a couple 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, like https://www.connectionstrings.com, for examples of connection string options. The latter has sample connection strings for practically any SQL datasource, and ReportWORQ should support any of themprovided 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, which provides helpful wizards to define and test connections to databases. In this example we have defined a 64-bit AdventureWorks System DSN connection to a SQL Server database, and verified that the connection can be established from the tool:
For ReportWORQ, we can now use a very simple connection string (compared to non-DSN connection strings):
- Windows Authentication/Trusted Connection: DSN=AdventureWorks;Trusted_Connection=yes
- Specific User ID + Password: DSN=AdventureWorks;Uid=sa;Pwd=EyeLoveRep0rtWORQ!;
Troubleshooting Connection Problems
If you've 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:
- 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.
- The credentials specified for the connection are incorrect.
- The server location is incorrect or firewall settings prohibit you from connecting to that server.
- System DSN's Only: You must use the 64-bit version of the ODBC Data Source Administrator tool to define your connection in order for ReportWORQ to be able to see it, since ReportWORQ is a 64-bit application. 32-bit ODBC connections are not visible to ReportWORQ.
- ReportWORQ Server Only:
- You created a User DSN and not a System DSN. The ReportWORQ Server service account may not be able to see User DSNs.
- 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.