Relational lists

You can create relational lists on relational data sources, or on OLAP Server data sources by using a relational database alias with the OLAP driver.

See Database alias types.

The OLAP driver enables you to query OLAP cubes and dimensions with SQL instead of MDX, for example. It can be used to return elements from multiple OLAP dimensions in a single hyperblock.

To create a relational list, right-click a relational database alias in the Database Structure and select New > Relational List. Relational lists can be global or local.

See Lists.

To edit a global relational list, right-click a list in the Database Structure and select Edit. The Relational List Designer is displayed.

To edit a local relational list, open the Relational List Designer through the Object Properties dialog of a list object. Then click List Designer in the List section of the dialog.

See List Objects

In the Relational List Designer, you write SQL queries to select list elements.

Relational List Designer

The Relational List Designer has three panes.

The left pane has these tabs which contain the tables, variables, and statements from which you can build queries:

Tables. Click to display the hierarchical structure of database tables and views.
Within the structure, these data types are indicated by icons and by tooltips:
- Boolean
- Integral numeric
- Decimal numeric
- Date and Time
- String
- Binary
Variables Click to display the available global variables and global lists. You can reference objects that are valid in the report but are not part of the relational database. Multidimensional and relational data in a report can be combined through global variables.
Statements. Click to display elements of SQL statements that can be used in statements. For example, SELECT, FROM, WHERE etc.

Create SQL Statements in the Query Editor pane manually, or by double-clicking items in the left pane and editing the syntax.

Click Query Preview in the tool bar to enable the Query Preview pane. It can be helpful to see a preview if your query contains variables or formulas. The preview shows the resulting SQL query, based on the current variable values or function evaluation.

Click Preview elements in the tool bar to enable the Element Preview pane. The pane displays the results of a query.

You can preview a relational list in the Preview pane, outside the relational list designer.

See Displaying the Preview Pane.

Displaying data fields from a relational selection

Only the first column returned by an SQL query is displayed in View mode . To display the remaining columns you can use one of these methods:

Multiple output cells in a hyperblock.
See Multiple output cells
Multiple RDB formulas
See RDB
A formula list
See formula list

Note: Oracle databases are case-sensitive. Standard practice is that all table and column names are in UPPERCASE. If an Oracle database contains mixed-case table or column names, you must enclose the table/column names in quotation marks in your SQL queries (for example, "table_name").

Database type: A relational list is defined by an SQL query. You might need different versions of the same query to account for differences in the way in which different database providers implement SQL. You can write multiple list SQL queries and store them as a single list. On the Advanced tab of a relational database alias, you can specify which version of the queries will be used.; See Advanced.; You create and maintain database types in the Database Types dialog.; See Managing database types.

The Calculate button: Click Calculate to display the result of a query in the bottom right pane.; If the first column of the query is not unique, duplicate values are displayed in the Preview pane. To resolve this, select the Use line number as index check box. This adds an ascending numeric index to the relational selection in the preview pane, which is displayed in place of the duplicate values. It does not affect the display of the results in the Relational List Designer.
Note: This index is not identical to the indexes in the relational database.
Reduce number of lines in the result: Select the Reduce number of lines in the result to check box and specify the maximum number of rows to be displayed in the Preview section.
Writable list: By default, you cannot write back values to relational lists created in the current version of Application Studio. To enable write back, select the Writable check box.; Relational lists created in previous versions of Application Studio are writable by default. Clear the Writable list check box to disable write back.; To maximize performance, do not enable write back unnecessarily.