Adding Lists of Values
You can create lists of SQL Query or Fixed Data values .
- In the Data Model components pane, click List of Values and then click Create new List of Values.
Creating a List from a SQL Query
The data engine expects a (display) name-value pair from the list of values query. In the list of values select statement, the column listed first is used as the display name and the second is used for the value that is passed to the parameter in the data set query by the data engine.
If the query returns only one column, then the same column value is used both as the list of values display name shown to the user and as the value that is passed to the parameter.
- Select a Data Source from the list.
- In the lower pane, select Cache Result (recommended) if you want the results of the query cached for the report session.
- Enter the SQL query or use the Query Builder. See Using the SQL Query Builder for information on the Query Builder utility. The figure below shows a SQL query type list of values.
The SQL query shown below selects only the DEPARTMENT_NAME column from the DEPARTMENTS table. In this case the list of values both displays the results of the query in the list and passes the same value to the parameter in the data set. The figure below shows the list of values display entries and the values passed to the data set. The menu items and the values shown for P_DEPT are the DEPARTMENT_NAME values.
If instead you wanted to pass the DEPARTMENT_ID to the parameter in the data set, and display the DEPARTMENT_NAME in the list, construct your SQL query as follows:
Select "DEPARTMENTS"."DEPARTMENT_NAME" as "DEPARTMENT_NAME",
"DEPARTMENTS"."DEPARTMENT_ID" as "DEPARTMENT_ID"
from "DEMO"."DEPARTMENTS" "DEPARTMENTS
The figure below shows the list of values display entries and the values passed to the data set. The menu lists the DEPARTMENT_NAME while the values shown for P_DEPT are the DEPARTMENT_ID values.
Creating a List from a Fixed Data Set
Create a list from a fixed data set for each label-value pair required.
When you create a label-value pair, the label is displayed to the user in the list. The value is passed to the data engine.
- In the lower pane, click the Create new List of Values icon to add a Label and Value pair.
- Repeat for each label-value pair required.
The figure below shows fixed data type list of values.
