- Working with Oracle Smart View for Office
- Query Designer and MDX Queries
- Working with Query Designer
- Creating Queries
Creating Queries
Data source types: Oracle Analytics Cloud - Essbase, Planning, Financial Consolidation and Close
To create a query report:
-
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.
-
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.
-
-
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.
Note:
When working with databases that support duplicate member names, avoid using member names containing periods. Oracle Smart View for Office 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
-
-
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.
-
To save the report, save as an Excel
.xls
or.xlsx
file.Note:
In Oracle Analytics Cloud - Essbase ,
.xls
or.xlsx
files can be used as a data load data source.
Note:
The Query Designer is not designed to work with Smart Slices.