Add Lists of Values

You can create lists of SQL Query or Fixed Data values .

1. In the Data Model components pane, click List of Values and then click Create new List of Values.
   
   

   
   Description of the illustration xdo11g_dm_parm_lov1a.gif

   
   
2. Enter a Name for the list and select a Type.

Create 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 dataset 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.

1. Select a Data Source from the list.
2. In the lower pane, select Cache Result (recommended) if you want the results of the query cached for the report session.
3. Enter the SQL query or use the Query Builder. The figure below shows a SQL query type list of values.

Description of the illustration xdo11g_dm_parm_lov2a.gif

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 dataset. The figure below shows the list of values display entries and the values passed to the dataset. The menu items and the values shown for P_DEPT are the DEPARTMENT_NAME values.

Description of the illustration lov_sql1a.gif

If instead you wanted to pass the DEPARTMENT_ID to the parameter in the dataset, 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 dataset. The menu lists the DEPARTMENT_NAME while the values shown for P_DEPT are the DEPARTMENT_ID values.

Description of the illustration lov_sql2a.gif

Create a List from a Fixed Dataset

Create a list from a fixed dataset 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.

1. In the lower pane, click the Create new List of Values icon to add a Label and Value pair.
2. Repeat for each label-value pair required.

The figure below shows fixed data type list of values.

Description of the illustration xdo11g_dm_parm_lov3.gif

Create a List from an MDX Query

You can use an MDX query to create a list of multidimensional values by specifying the slice of an Essbase cube.

To create a list from an MDX query:

1. In the Data Model components pane, click List of Values.
2. Click Create New List of Values.
3. Enter a name for the list in the Name field.
4. Select MDX Query from the Type list.
5. Select an OLAP data source from the Data Source list.
6. In the lower pane, select Cache Result (recommended) if you want the results of the query cached for the report session.
7. Select the POV / Slicer for the MDX query.
   1. Click the POV / Slicer search icon.
   2. Select a cube.
   3. Select a dimension.
   The POV / Slicer field displays the selected cube and dimension in the cube.dimension format.