Creating Queries

Data source types: Oracle Essbase, Oracle Hyperion Financial Management, Oracle Hyperion Planning, Oracle Planning and Budgeting Cloud, Oracle Financial Consolidation and Close Cloud, Oracle Hyperion Enterprise®

To create a query report:

  1. Open a worksheet or an existing report in Excel and connect to a data source.

    Note:

    Workbooks can contain Query Designer worksheets from multiple data sources. However, only one data source can be associated with each worksheet.

  2. From the data source ribbon, select Query, then Query Designer.

    The Query Designer and a query worksheet named “Sheetname - Query” (for example, Sheet1 – Query) are displayed. You design your query on this worksheet.

    The following operations are disabled on the query sheet, but are re-enabled after you run the report:

    • Formulas

    • Asymmetric reports

    • Comments

    • Blank rows or columns

    • Changes to alias tables

    • Ad hoc actions such as zoom in and out, keep and remove only, and double-click

    The following operations are unavailable in both query sheet and report sheet:

    • Filtering of column members

    • Changing data sources

    If you are working with a duplicate member database, qualified member names are displayed on the query sheet, but only the member names are displayed after you run the report.

  3. Use any of the following operations to design your query:
    • To select members for the Rows and Columns dimensions displayed on the Query Designer, click the dimension name to open the Member Selection dialog box.

    • To select members for POV dimensions displayed on the Query Designer, click the arrow next to the dimension name and select the ellipsis to open the Member Selection dialog box.

    • To move a dimension from the POV to the grid, drag and drop it from the POV section to the Columns or Rows section in the Query Designer.

    • To remove a dimension from the grid, drag and drop the dimension from the Columns or Rows section to the POV section in the Query Designer.

    • To add or remove an attribute dimension, select a dimension from the Attributes drop-down menu and drag and drop to the Rows or Columns section of the Query Designer.

    • Enter members directly into the grid.

    Member Name Limitation

    In Query Designer, when working with databases that support duplicate member names, you should avoid using member names containing periods. Smart View detects periods (.) as delimiters in Query Designer, resulting in an unknown member error. If the dimension or member names in your query contain a period, the member will be interpreted as two members, parent and child. For example, the member:

    abcd.wxyz
    

    would be interpreted as:

    abcd
       wxyz
    
  4. From the Query Designer, click Apply Query. The resulting report is displayed in a new report sheet called “Sheetname - Report” (for example, Sheet1 – Report). Operations temporarily disabled in step 2 are re-enabled.

    The report sheet replaces the query sheet, but you can retrieve the query sheet by repeating step 2.

  5. To save the report, save as an Excel.xls or .xlsx file, which in Essbase or Hyperion Enterprise can be used as a data load data source.

Note:

The Query Designer is not designed to work with Smart Slices.