Using Hyperion Essbase MDX Query Designer in Query Mode

New: 12 December 2006

When you create a dataset based on a Hyperion Essbase data source, Report Designer opens the MDX graphical query designer in Design mode. To switch to Query mode, click the Design Mode (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.

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.

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 insertion point.

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 insertion point.

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 gets 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.

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 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 refreshes 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 edit query text through the query designer, the matching query specification is automatically generated and saved to the report definition.

See Also

Concepts

Defining Report Datasets for Multidimensional Data from a Hyperion Essbase Database
Defining Report Datasets

Other Resources

Hyperion Essbase Query Designer User Interface

Help and Information

Getting SQL Server 2005 Assistance