Using Hyperion Essbase MDX Query Designer in Query Mode (Report Builder 2.0)
When you create a dataset based on a Hyperion Essbase data source, Report Builder 2.0 opens the MDX graphical query designer in Design mode. To switch to Query mode, click the Design Mode (.gif "Switch to Design mode")
) toggle button on the query designer toolbar. Use Query mode to view or edit MDX query text directly, or to interactively build an MDX query. You can select a cube, drag members to the Query pane, add calculated members, and run the query to see results. For more information about Design mode, see Using Hyperion Essbase MDX Query Designer in Design Mode (Report Builder 2.0).
The graphical query designer in Query mode includes a toolbar, a Select Cube drop-down list, and three panes: a Metadata pane that includes tabs for Metadata and Functions, a Query pane, and a Result pane. For more information about the user interface, see Hyperion Essbase Query Designer User Interface (Report Builder 2.0).
Database Selection
Before you can use Query mode to create a query, you must use the Select Cube button to select the cube from which to retrieve data. When you select a cube, any existing 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.
The hierarchical display for cubes shows hierarchies, levels, members, and member properties. You can drag objects from the Metadata pane to the Query pane where corresponding MDX query text is added for that metadata. The text is added at the cursor.
On the Functions tab, you can browse available functions to use in an MDX query and drag them to the Query pane. The function is inserted at the cursor.
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 in Query mode, enter the MDX clause to restrict data that is retrieved from the data source.
Note
Not all MDX syntax is supported by all data processing extensions. For more information about how to filter data for this data provider, see the "Filtering Data" section in Using Hyperion Essbase MDX Query Designer in Design Mode (Report Builder 2.0).
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 (Report Builder 2.0).
Results
Results from the MDX query are shown in the Result pane when you click the Run button on the toolbar.
To refresh the Metadata pane to display changes to the cubes on the data source, click Refresh on the toolbar. Running a query updates only the Result 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 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 designer, the matching query specification is automatically generated and saved to the report definition.