Relational list designer

You can create relational lists on relational data sources, or on OLAP data sources by using a relational data connection with an Infor Olap Server provider.

You specify the provider on the Connection tab of a data connection in EPM Administration.

The Infor Olap Server providers enable you to query OLAP cubes and dimensions with SQL instead of MDX. They can be used to return elements from multiple OLAP dimensions in a single hyperblock.

Relational lists are defined by SQL queries. 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 a SQL statement for each database provider. The provider that is selected for the data connection determines which version of the statement is used.

To help ensure the security of the environment, any SQL statements that you create, and which target an Infor cloud source, are parsed against a list of safe SQL constructs. SQL query syntax must meet SQL ANSI 92 standards. The list is applied only where Infor cloud sources might be affected.

In On-premises environments, the Secure Mode button toggles between secure and insecure modes. In secure mode, queries that you write cannot reference report objects or variables. In secure mode, queries are sent as text and are never interpreted as formulas. In Cloud environments, secure mode is enforced and the button is disabled.

You create and maintain database types in the Database Providers dashboard in EPM Administration.

The Query Editor pane of the list designer has these tabs which contain the tables, parameters, 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
Parameters. For security reasons must not use string operations and variables to construct relational lists. Instead, create parameters and assign variables to them and use the parameters in your SQL. You can use single-value and multiple-value parameters. In SQL statements, reference single-value parameters with @parameter_name. Reference multiple-value parameters with@@parameter_name. Values in multiple-value parameters must be separated by the pipe | character.
Statements. Click to display elements of SQL statements, such a Select, From, etc., that can be used in queries.

Write SQL statements in the Query Editor pane. Or, double-click items on the Tables tab and edit the resulting syntax.

From the Database type list, select the database provider for which to write each statement.

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").

Click Query Preview to show the resulting SQL query, based on the current variable values or function evaluation.

Click Preview in the tool bar of the list designer to enable the Preview pane and display the results of a query.

If the first column of the query is not unique, duplicate values are displayed in the Preview pane. To resolve this, use the Use line number as index field in the Advanced pane of the list designer. 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.

To limit the number of rows returned by a query, enable the row limit in the Advanced pane of the list designer and specify the number of lines to display.

By default, you cannot write back values to relational lists. To enable writeback, select True in the Writable list field in the Advanced pane. To maximize performance, do not enable writeback unnecessarily.