Working with Query Definitions

Two types of SQL queries can be used in a report definition: a base query and a parameter query.

A base query enables users to pull data from various tables and display the data as report output. The base extends the definition of a standard report, and can be reused with multiple reports. For example, using one query definition, you can show different columns or groupings. In one report, you can list amounts by account and group by entity, and in another list amount by entity and group by account.

The parameter SQL query enables you to run a query against the parameters in the report definition. For example, use the query to select the Location, Period, Category, or Account.

For information on the tables and joins you can use to build your SQL queries, see TDATASEG Table Reference and TLOGPROCESS Table Reference in Appendix G. The TDATASEG table is used to store the data loaded by the user, the transformation between the source dimension members, and the results of the mapping process. The TLOGPROCESS table is used to store the workflow process status for a location, category, and period.

You can save a query definition as an XML file, which, in turn, you can use to create custom templates using Oracle Business Intelligence Publisher or the BI Publisher desktop addin for Microsoft Word.

To add a query definition:

1. On the Setup tab, under Reports, select Query Definition.
2. In Query Definition, click Add.
3. In Name, enter the name of the query definition.

   Oracle recommends that you assign a name that corresponds to the report definition in which the SQL is embedded.

4. In Select Clause, specify the SQL Select clause used to query the database and return the data that corresponds to your selected criteria.
5. In Where Clause, specify the SQL Where clause used to restrict the data that is returned to only the specific criteria that you specify.
6. In Group by/Order by Clause, specify the Group by or Order by clause.

   The ORDER BY clause sorts the records in the result set. The ORDER BY clause can be used only in SQL SELECT statements.

   The GROUP BY clause fetches data across multiple records and returns the results grouped by one or more columns.

7. Click Validate Query.

   If the query definition is validated, Oracle Hyperion Financial Data Quality Management, Enterprise Edition returns the message: "Query validation successful."

   If the query definition is not validated, FDMEE indicates that an error was found in the SQL. You must fix the error before validating the query again.

8. Click Save.
9. Optional: To save the query definition to an XML file, click Generate XML.