Jedox Web Spreadsheets can use ODBC connections as a data source for elements such as DynaRanges. To use these data sources, you must first define an ODBC connection in the Jedox Administration panel. Once the connection is configured, you can retrieve data in your spreadsheets with the Paste ODBC Query dialog (figure 1) or the ODBC source option in the DynaRange dialog (figure 2).
When using ODBC queries in a spreadsheet, several restrictions must be considered. For more information, see Data Sources for Data Validation, DynaRanges, ComboBoxes, and Lists.
System DSNs on Windows systems
On Windows, the data source names, or DSNs, to which Jedox Web will connect are defined in the “Data Sources (ODBC)” dialog. The name chosen here for the DSN is also the name that must be used in the connection settings in the Jedox Web Connection Manager.
To create a DSN for your source database system, please refer to that system’s documentation.
System DSNs on Linux systems
On Linux, the DSNs to which Jedox Web will connect (using the unixODBC standard) are defined in two configuration files in the Jedox installation directory (e.g. /opt/jedox/ps/): etc/odbcinst.ini and etc/odbc.ini.
In the file odbcinist.ini, general settings for the specific ODBC drivers are specified, such as the name for the ODBC connection type and the driver file’s location. In the file odbc.ini, the settings for a specific data source are defined, such as the host, driver to use, and database/schema name.
See Note 3 at the end of this article for setting up the Oracle driver on Linux.
Creating an ODBC connection in Jedox Web
After setting up the DSN on the system where Jedox is installed, you can create a connection to the DSN in the Jedox Web Connection Manager.
As an example, the connection DWH_Biker is supplied with the Jedox Web installation.
In the Administration panel you should first test this connection if it works:
If it doesn’t work, it may help to install Microsoft Access Database Engine 2010 Redistributable, because the connection DWH_Biker uses an ACCESS database.
Using the ODBC connections in spreadsheets
Open the “Paste ODBC Query” dialog by navigating to Tools→Paste ODBC Query. Alternatively, you can open a DynaRange or ComboBox dialog and select “ODBC Query” as a source.
To demonstrate, we will use the DWH_Biker connection. In a new spreadsheet, click on cell A2 and go to Tools→Paste ODBC Query. Select the ODBC connection “DWH_Biker” and enter the following SQL statement:
Select * from Orders where Years = "2013" and Months = "Apr" and Products = "Trekking-2000 Blue 60"
Click “Preview” to view the first data records. Click on the button “Use This Query” to enter the ODBC start formula into cell A2:
To display all returned rows and columns of the SQL statement in the spreadsheet, enter the following formula in all corresponding cells (do not overwrite A2!):
If you enter the formula in more cells than there are records for the SQL statement, you will receive the entry “#VALUE!”.
Examples with ODBC connections and DynaRanges can be found in the workbooks “Order Analysis(Drill Through)” and “Time Analysis(relational)” in the folder “Demo Spreadsheets/Bikers Best/Workbooks” of the Report Designer.
The advantage of using ODBC formulas in DynaRanges is that you have only to fill the first line with formulas. In User Mode, the DynaRange will automatically display all required lines for all the records of the SQL statement.
1.) ODBC sources for Jedox Web must use /*comment */ for comments. Using “#” for comments will not be recognized and leads to errors.
2.) Regarding handling of special characters in ODBC data, it may be necessary to define certain settings on the system where Jedox is installed. For example, when connecting to Oracle servers and using German characters, the following environment variables have to be set:
3.) When connecting to MSSQL from Linux, columns of type NVARCHAR aren’t supported
4.) To connect to Oracle DB on Linux,
- download the latest Oracle drivers for unix from oracle.com. At the time of writing version 18.104.22.168.0:
- unzip both packages to usr/lib for 32bit setup or usr/lib64 for 64bit setup
- edit etc/odbcinst.ini to add entry for Oracle driver:
[Oracle 12g ODBC driver]
Description = Oracle ODBC driver for Oracle 12g
Driver = /usr/lib64/libsqora.so.12.1
FileUsage = 1
- edit etc/odbc.ini to add DSN for Connection to Oracle DB:
Application Attributes = T
Attributes = W
BatchAutocommitMode = IfAllSuccessful
BindAsFLOAT = F
CloseCursor = F
DisableDPM = F
DisableMTS = T
Driver = Oracle 12g ODBC driver
DSN = TestOracle
EXECSyntax = T
Failover = T
FailoverDelay = 10
FailoverRetryCount = 10
FetchBufferSize = 64000
ForceWCHAR = F
Lobs = T
Longs = T
MetadataIdDefault = F
QueryTimeout = T
ResultSets = T
ServerName = :/
SQLGetData extensions = F
Translation DLL =
Translation Option = 0
DisableRULEHint = T
Make sure HOST_IP, PORT, and SERVICE_NAME match your Oracle setup, e.g. 192.168.2.130:1521/test.BikerDB