Creating Custom Queries

You can create custom queries in Oracle Hyperion Profitability and Cost Management.

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.

   
   
   
3. Click Add to open the Query wizard.
   
   
   
4. In 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 A-12.

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

   Table A-12 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.

   • 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.

   • Selecting Suppress #Missing in a Query definition returns the error "Cannot perform cube view operation.null" when you run the query. If you deselect this option, the query runs, and you will see data along with missing data rows.

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.

   
   
   
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.

    
    
    
15. Under Dimension Member Selections, use the Add arrowto 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.