USING LIST OF VALUES TO QUERY DATABASE IN ORACLE BI

BI PUBLISHER (CONTRACT MANAGEMENT/PRIMAVERA P6 EPPM)

DOWNLOAD THE PDF HERE

When you need to bring additional fields into an existing report or form created in Oracle BI, you must first modify the BI Data Model SQL syntax to include those fields.

To do this, you must know the table name and the field name.   If you do not know what these are, you can use the “List of Values” option in BI to temporarily query the database, see what fields are available and create a query output to confirm that the fields selected are the correct ones.  Once you have done this, you can then remove the temporary query and insert the field into your SQL statement and then modify the layout to include it.

This eliminates the need for you to use a direct database tool (like SQL Developer) to do this review.

Steps

1. Open the Data Model for your report you want to modify.
2. Review the data set(s) for your report to determine what is missing.

Note:  do NOT use the “Query Builder” button in the data set as this will overwrite the existing SQL statement with a new query.

3. To determine the new field(s) you need, click on the “List of Values” tab, then click the add icon to create a new query.
4. Click on the “Query Builder” option here.

5. Select the appropriate database in the “Schema” dropdown and begin to type in the table names. Note you can select one table or multiple tables here.
   1. Note: Use the Oracle published list of tables and joins document to determine which tables you need.
   2. If you select multiple tables, you can click and drag the joins from table to table.

6. Review the list and check off all of the fields you want to inspect.
   1. If you select multiple tables, you can click and drag the joins from table to table.

7. On the Conditions tab, enter any conditions that would be appropriate to retrieve your data.

8. On the SQL tab, typically you’ll need to check the “Only use default schema” option.

9. Last, click on the drop down by results to select the number of records you want to retrieve and the results will appear on the Results tab.
   1. Review this information to see which field returns the data you want to add to your report.

10. Once you’ve determined the fields, make a note of their name, click Cancel to exit out of the Query Builder, then click the “Delete” icon to remove the temporary List of Values entry.

11. Return to the Data Set SQL edit screen and type in your new fields and tables into the SQL statement.
12. Last, once saved, modify your report layout to include the new data.

Comments and Considerations

1. If you are selecting multiple tables in the query, you need to be sure that you add the correct joins. Otherwise, you can do separate queries for each table or view you want to look at.
2. Results drop down quantity option—in some browsers this dropdown does not work and you can only retrieve 10 records by default. Using dropdown, maximum number of records is 100.
3. For P6 EPPM, this option ONLY displays actual tables and views. In later versions of P6 EPPM, many stock BI reports use table synonyms for the table names, and these synonyms are not displayed here.  You need to use a true query tool (like SQL Developer) to view these table aliases.