Using Hyperion Essbase MDX Query Designer in Design Mode
When you create a dataset based on a Hyperion Essbase data source, Report Designer displays the MDX graphical query designer in Design mode. Use the Design mode to interactively build an MDX query with graphical elements. You can select a cube, drag members to the Data pane, add calculated members, and immediately view the result set. To view or change the MDX query text directly, switch to Query mode by clicking the Design Mode (.gif "Switch to Design mode")
) toggle button on the toolbar. For more information about Query mode, see Using Hyperion Essbase MDX Query Designer in Query Mode.
The graphical query designer in Design mode includes a toolbar, a Select Cube drop-down list, and four panes: a Metadata pane, a Calculated Members pane, a Filter pane, and a Data pane. For more information about the user interface, see Hyperion Essbase Query Designer User Interface.
Database Selection
Before you can create a query, you must select a cube from which to retrieve the data. Use the Select Cube drop-down list to select the appropriate cube from the Hyperion Essbase database. When you select a cube, all MDX query text in the Query pane is cleared.
Metadata Pane
In the Metadata pane, you can browse the selected cube's metadata, such as dimensions and measures. You can drag objects from the Metadata pane to the Data pane, where a corresponding column is added for that metadata. A vertical red line indicates the position where the column will be added. Dimensions are kept together on one side of the Data pane and data (measures, calculated members) are kept together on the other side of the Data pane.
Calculated Members
The Calculated Members pane appears under the Metadata pane. Use the Calculated Members pane to create or edit calculated members to add to the Data pane as additional columns. In this pane, you can set a Solve Order property to greater than or equal to 0. The member with the lowest solve order value will be resolved first. The default value is 0.
Calculated members do not exist in the data source but are the result of calculations you create during the query execution. For example, if a cube contained the members "Sales" and "Cost", you can define a calculated member named "Profit" that displays the difference between Sales and Cost.
Filtering Data
Parameterized queries are not supported by the Microsoft .NET Framework Data Provider for Hyperion Essbase because the data source does not support parameterized queries.
To filter data from a Hyperion Essbase data source, specify dimensions and members to include or exclude in your query. Each row in the Filter pane specifies a dimension and members, and an operator and filter expression that defines whether to include or exclude the members. If you select a range operator for a filter row, you must include both the start and end values. Null is not supported as a start or end value. You can enter multiple rows that refer to the same dimension or different dimensions. Available values for each column in the filter pane can be influenced by previous rows in the pane. You can specify dimensions in the filter that are not included in the query itself. If a filter dimension is not part of the MDX query, you can only specify a filter condition using the equal (=) operator and a filter expression that specifies a single selection. Each row you define in the Filter pane adds an element in the MDX WHERE clause. To view the corresponding MDX query, click the Design mode toggle button on the query designer toolbar.
Extended Properties
The Hyperion Essbase data processing extension supports extended field properties for multidimensional data sources. For the list of supported fields, see Using Extended Field Properties for a Hyperion Essbase Cube.
Results
Results from the MDX query are shown in the Data pane. If the AutoExecute toggle button in the toolbar is on, every change to the Data pane automatically triggers the MDX query to run. If the AutoExecute toggle button is off, click the Run button on the toolbar to execute the MDX query. By default, the AutoExecute button is on.
When you run a query the results are refreshed in the Data pane. To display changes to the cubes on the data source, use the Refresh button on the toolbar to refresh the Metadata pane.
Note
By default, MDX query results hide empty cells. (This is the equivalent to using the NON EMPTY clause in MDX). To show empty cells, click the Show Empty Cells toggle button on the query designer toolbar.
Query Definition
MDX queries are saved to the report definition file as straight MDX query text and as an MDX query specification. As you change query text through the query designers, the matching query specification is automatically generated and saved to the report definition.