1. User's Guide
  2. Working with Management Ledger Profitability
  3. Working with Management Ledger Queries and Reports
  4. Managing Smart View Queries in Management Ledger Profitability Applications
  5. Creating Custom Smart View Queries in Management LedgerProfitability Applications

Creating Custom Smart View Queries in Management LedgerProfitability Applications

Before creating a query, ensure a full Oracle Hyperion Profitability and Cost Management environment is installed, configured, and running, including the following products:

  • Oracle Hyperion Provider Services

  • Oracle Hyperion Shared Services

  • Oracle Essbase

You also need Microsoft Excel installed with Oracle Smart View for Office on your computer.

To create a query:

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

    The Queries screen is displayed, showing all existing queries for all applications for which the user is provisioned as a View User, Interactive User, Power User, or Administrator (Figure 14-1).

    Figure 14-1 Manage Queries Task Area, Queries Screen


    The Queries screen lists available queries and contains icons for creating, deleting, filtering, and running queries.
  2. Click Add Query button or select Actions, and then Create Query.

    This action is not available to View Users.

    The three-step query wizard opens.

  3. In Step 1 of 3: Description, enter the following information for the new query and select Next:

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

    • Enter a Name for the query.

  4. In Step 2 of 3: Program Context, select whether to use a program context (global context, rule set context, or a rule) to define dimensions. For more information about contexts, see Working with Management Ledger Allocations.

    • If you selected Use Program Context?, enter POV information and then click Next.

    • If you did not select Use Program Context?, click Finish, and then skip to step 6, later in this procedure.

  5. If you select Use Program Context? and click Next, Step 3 of 3: Dimensions is displayed. Select which program context to use, and then select from any lists that are presented. When selections are complete, click Finish.

    Context choices are the following:

    • Use Global Context — Applies the default dimensions selected for all rule sets and rule in the model

    • Use Rule Set Context — Applies default dimensions selected for the specified rule set (requires a rule set name)

    • Use Rule — Applies rule-specific dimension information; requires a rule set name, a rule name, and a rule component (Source, Destination, Driver, or Offset)

    After you click Finish, the Queries screen is displayed. The new query is listed.

  6. Select a query and complete the custom query definition as described in the following steps.
  7. Optional: On the Description tab, do the following if appropriate:

    • Enter a Description for the query.

    • Select Use Alias to display any assigned aliases for all dimensions in the query.

    • 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 set only 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. Click Dimensions to review and edit dimension selections (Figure 14-2).

    By default, all dimensions in the application outline are displayed.

    Figure 14-2 Dimensions Tab of the Queries Definition Screen


    This tab lists dimensions selected for the query with their type, or position..
  9. A default Position is assigned to each dimension. To change the position, select a dimension, open the Move To drop-down list, and then select the new position::

    • Row

    • Column

    • Smart View POV

  10. Optional: Use Move Up and Move Down to change the position of a highlighted dimension in the query within its Position.
  11. Optional: Select a dimension and use the Member Selection area to add or delete members and change the column display.

    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.

  12. When the query definition is complete, click Save button to save it for further use.

To run, edit, or delete queries, see the following: