Relational list designer
You specify the provider on the Connection tab of a data connection in d/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 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 d/EPM Administration.
The Query Editor pane of the list designer has these tabs which contain the tables, parameters, 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
- Parameters.On
this tab you can add parameters and assign values to them. 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. Use of parameters in queries is optional. - 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, 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.
"table_name"). Click to show the resulting SQL query, based on the current variable values or function evaluation.
Click 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.
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.