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 Jedox Administration. Once the connection is configured, you can retrieve data in your spreadsheets with the Paste ODBC Query dialog
Figure 1
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.
Using the ODBC connections in spreadsheets
Open the "Paste ODBC Query" dialog by navigating to Insert→Paste ODBC Query. Alternatively, you can open a DynaRange or Combobox dialog and select "ODBC Query" as a source.
For demonstration purposes, we will assume that an ODBC connection named ODBC_Biker has already been created. In a new spreadsheet, click on cell A2 and go to Insert→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!):
=ODBCDATA($A$3;1;2)
Note that ODBC DynaRange splits multidimensional SQL result arrray into one two-dimensional array per row. As a consequence, ODBCDATA() functions within DynaRanges do not work in combination with ROW() and COLUMN() functions.
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.
Notes
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:
- LC_ALL=de_DE.UTF-8
- LANG=de_DE.UTF-8
- LANGUAGE=de_DE.UTF-8
- NLS_LANG=GERMAN_GERMANY.AL32UTF8
3) When connecting to MSSQL, columns of type NVARCHAR aren't supported.
Updated September 27, 2024