Creating Custom Queries

To create queries:

  1. Before creating the query, ensure the following products are installed, configured and running:

    • Oracle Hyperion Provider Services

    • Oracle Hyperion Shared Services

    • Oracle Essbase

    • Microsoft Excel is installed with Oracle Smart View for Office on the client machine

  2. From the Task Area, select Reporting, and then Manage Queries.

    The Manage Queries screen is displayed, showing all existing queries for all applications for which the user is provisioned as an Admin or Power user.


    The Queries screen displays existing queries.
  3. Click Add Add button to open the Query wizard.

    Enter the Query options for the Smart View query.
  4. On Step 1: Query Options, enter the following information for the new query:

    • Name for the query

    • Optional: Description of the query

    • Select the Application to be used for the query from the drop-down list

    • Select the Database to be used for the query from the drop-down list:

      • Calculation (BSO)

      • Reporting (ASO)

    • Optional: Enter a descriptive Search Tag to be used to sort queries in the main Smart View Query screen.

  5. Under Type, select the type of query to be created:

    • Driver Measures

    • Stage Measures

    • Contribution

    • Custom

    Depending on the Type that is selected, the additional query options change to reflect the choice. See Table 11-1.

  6. Select the Query Options based on the selected Type:

    Table 11-1 Query Type Options

    Query Type Selected Type Query Options
    Driver Measures Assignment Select the Source and Destination stages.
    Driver Measures Source

    Select the Source stage.
    Driver Measures Destination Select the Destination stage.
    Driver Measures Global N/A
    Stage Measures   Select the Stage to be used for the query.
    Contribution Direct Allocation Select the Source and Destination Stages.
    Contribution Genealogy Select the Contribution Path (for example, 1-3-5).
    Custom   Select as required. No default is expected.
  7. Optional: Under Smart View Options, select Suppress #Missing to set the data suppression option for the first query in Smart View, if required.

    Note:

    If you select Suppress #Missing, the Smart View option is only set for the first query run, not all queries. To set the option for subsequent drills into the data, set the option manually in Smart View.

  8. Optional: Under Smart View Options, select Use Dimension Aliases to display any assigned aliases for all dimensions in the query.
  9. Click Next.

    Step 2 - Dimension Layout is displayed.


    Select the Dimensions to be used in the query, and the appropriate Dimension Position.
  10. Under Dimensions, for each Dimension, select the Position for the placement of the dimension under the Dimension Position column on the screen using the drop-down list:

    • POV

    • Rows

    • Columns

    By default, all dimensions in the application outline are displayed, and selections are set to defaults that are appropriate for the type of query you are creating.

    For example, if you select stage measures for a specific stage, the dimensions for that stage display in the Rows section, and the top member of the first hierarchy is pre-selected for each of the stage dimensions. The other stage dimensions are placed in the Smart View POV section, and the "NoMember" member from each stage is selected.

  11. Optional: Under Dimension Position, in the Smart View POV section, use the Up and Down arrows to change the position of a highlighted dimension in the query.
  12. Optional: Under Dimension Position, in the Rows section, use the Up and Down arrows to change the position of a highlighted dimension in the query.
  13. Optional: Under Dimension Position, in the Columns section, use the Up and Down arrows to change the position of a highlighted dimension in the query.
  14. Click Next.

    Step 3 - Member Selections is displayed. All dimensions from the Smart View POV, Rows and Columns are displayed in the order defined on the Dimension Layout screen. Use the arrows to scroll up or down through the list.


    Select dimension members that are to be used in the query.
  15. Under Dimension Member Selections, use the Add arrow Add arrow to move the dimension members to be included in the query to the Selected Dimension Members column.

    The list displays all dimension members, including alternate hierarchies and the NoMember member. Because there are no restrictions on level, alternate hierarchy, shared or base member, any member may be selected.

    Use the Ctrl key to select multiple dimensions, or the Shift key to select the first and last members in a range.

  16. Click Finish.

    The new query is added to the Manage Queries screen.

    Note:

    Before running the query, the database must be deployed. Although the database does not need to be calculated before running the query, results will be missing if it is not.