Creating Queries

Creating queries is the first step in generating reports:

1. Create a query. See below.

2. Create a template. See Creating Report Templates.

3. Set up a report group. See Managing Report Groups.

4. Set up the report definition. See Creating Report Definitions.

5. Generate the report. See Generating Reports.

To create queries:

1. In your application, select Manage, and then Manage Reports.
2. Select Queries, then Actions, and then New.
3. In New Query, enter:

   • Name

   • Description

   • Type

     The selection of the Type determines where the Query appears in the New and Edit Report dialog boxes:

     • Parameter Query

       A query that is defined as type Parameter Query 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.

     • Report Query

       Select the records to be included in the report. 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:

       Close Manager Queries:$CM_SECURITY_CLAUSE$

       A sample Close Managerr query with the Security Filter applied: SELECT Task_name FROM fcc_tasks WHERE schedule_id = ~Schedule~ AND $CM_SECURITY_CLAUSE$

       A sample Close Manager query with the Security Filter applied: SELECT Task_name FROM fcc_tasks WHERE schedule_id = ~Schedule~ AND $CM_SECURITY_CLAUSE$

       Account Reconciliation Manager Queries: $ARM_SECURITY_CLAUSE$

       Note:

       Because many predefined queries included with Oracle Hyperion Financial Close Management have the Security Filter applied, you can use them as examples when building your own.

       Tip:

       If the report you are designing contains parameters, you can design the report to display no records or all records. Examples of each are provided below. These examples assume that you want to run a report that displays the list of periods that contain the "Monthly" frequency.

       • Parameter Query: Select frequency_id, frequency_name from arm_frequencies

       • Report Query, Option 1: (Return no periods if the user does not provide a frequency value):

         Select p.period_name from arm_periods p, arm_period_frequencies pf where p.period_id = pf.period_id and pf.frequency_id = ~FREQUENCY~

       • Report Query Option 2: (Return all periods if the user does not provide a frequency value):

         Select p.period_name from arm_periods p, arm_period_frequencies pf where p.period_id = pf.period_id and pf.frequency_id = coalesce(~FREQUENCY~,pf.frequency_id)

       In the second option, the coalesce() function returns the first non-null value in the list; therefore, if the FREQUENCY was null, it would return pf.frequency_id and in that case that condition would always be true (pf.frequency_id = pf.frequency_id), causing all records to be returned.

   • Generate Query

     Assists you in creating a query against the database by allowing you to select any existing attribute in the product to be queried and/or filtered against. The dialog then generates the SQL to match the attributes and filters specified, at which time you can modify and enhance it.

   • Query

     To generate a sample XML file from the Query, click Generate Sample XML.

4. To test the query for errors, click Validate.