Editing Custom Queries

To edit queries:

  1. 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, Power.

  2. Click the Edit Query button Edit Query button to open the query wizard.

    Modify query options for the selected query.
  3. On Step 1: Query Options, modify any of the following fields to modify the query:

    • Name for the query

    • Optional: Description of the query

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

      Note:

      You cannot modify the Query Type or Query Options. To change the Type, create a new query.

  4. Optional: Under Smart View Options, select Suppress #Missing to set the data suppression option for the first query in Oracle Smart View for Office, if required.

    Note:

    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.

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

    Step 2 - Dimension Layout screen is displayed.


    Select the Dimensions to be used in the query, and the appropriate Dimension Position.
  7. 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:

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

  8. Optional: Under Dimension Position, in the Smart View POV section, use the Up and Down arrows to fine tune the position of a highlighted dimension in the query.
  9. Optional: Under Dimension Position, in the Rows section, use the Up and Down arrows to fine tune the position of a highlighted dimension in the query.
  10. Optional: Under Dimension Position, in the Columns section, use the Up and Down arrows to fine tune the position of a highlighted dimension in the query.
  11. 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.
  12. 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.

  13. Click Finish.

    The modified query is saved and available on the Manage Queries screen.