Creating a Query

Creating a query is the first step in creating a custom report.

There are three ways to approach creating a query:

• Use a predefined report as a starting point and duplicate the query, give it a new name and use the SQL and modify it as needed
• Use Generate Query and select existing attributes to be queried and/or filtered against and using the information in the Oracle Enterprise Performance Management Cloud Tables and Views for Account Reconciliation Guide, select more columns to add and/or modify filter conditions. See Overview of Tables and Views
• Create the SQL query yourself, if you are already familiar with the table information. See Overview of Tables and Views

To create a query:

1. From Home, select Application, then Report Configuration.
2. Select Queries, then Actions, and then New.
3. In New Query, enter Name and Description.

4. In Type, select one of the following types:

   • Parameter Query

     A Parameter Query type is used to present a list of options that you can specify for this parameter's value. Parameter Query allows you to present a list of options used when filling in a parameter's value for a Report Query, where the list of options is not a simple Attribute already defined, but is instead a complex query that you need to define.

     See Adding a Parameter Query to Reports for an example on selecting a parameter query to a report.

   • Report Query

     Select the records to be included in the report. If the report you are designing will contain parameters, you can design the report to display no records or all records.

     For Reconciliation Compliance, you can apply a security filter, so users see only the data that they are authorized to see based on their roles and the reconciliations to which they are assigned. To apply a Security Filter to a report query, add the following syntax to the end of the query WHERE CLAUSE statement:

     Reconciliation Compliance: $ARM_SECURITY_CLAUSE$

     WHERE $ARM_SECURITY_CLAUSE$ AND ReconciliationEO

     When using $ARM_SECURITY_CLAUSE$ in the query, the ARM_RECONCILIATIONS must be aliased to ReconciliationEO.

     Note:

     Because many predefined queries included with Account Reconciliation have the Security Filter applied, you can use them as examples when building your own.

     See Reconciliation Compliance Examples below for query examples.

5. Click Generate Query to generate the report query. See Generating a Query for details.

   After you provide the required details and close the Generate Query dialog, the generated report query is displayed in Query.

   
   
   

   Note:

   If you need parameters in your report, add the parameters to the report query SQL. The parameter name can be any name, but it must be enclosed in tildes (~). See the examples in the sections below.

6. Perform one of the following actions:

   • Click Validate to test the query for errors.

   • Click Validate and Explain Plan to test the query for errors and generate the plan that will be used to run this query. The generated plan is stored in a .txt file that uses the same name as the query. You can view or download the generated plan.

     The generated plan contains the set of steps used to run the query. Use the plan to identify issues that may cause suboptimal query performance. Subsequently, you can tune the query to improve its performance.

   Note:

   By default, generation of execution plans is only available for OCI (Gen 2) environments. To enable it for Classic environments, contact Oracle using a service request.
7. You can either Save or Save and Close this query.

8. To generate a sample XML file from the Query to use as a template with BI Publisher, click Generate Sample XML.

Note:

You can easily delete a query, or duplicate a query using the Action menu.