Analysis Services MDX Query Designer User Interface
New: 14 April 2006
Reporting Services provides graphical query designers for building Multidimensional Expression (MDX) queries and Data Mining Expression (DMX) queries for an Analysis Services data source. This topic describes the MDX query designer. For more information about the DMX query designer, see Using Analysis Services DMX Query Designer.
The MDX graphical query designer has two modes: design mode and query mode. Each mode provides a metadata pane from which you can drag members from the selected cubes to build an MDX query that retrieves data when the report is processed.
Graphical MDX Query Designer in Design Mode
When you view a report server project in Report Designer in Data view, the default view for the graphical query designer is Design mode. The following figure labels the panes for Design mode.
.gif "Analysis Services MDX query designer, design view")
The following table describes the functions of each pane.
| Pane | Function |
|---|---|
Select Cube button |
Displays the currently selected cube. |
Metadata pane |
Displays a hierarchical list of measures, Key Performance Indicators (KPIs), and dimensions defined on the selected cube. |
Calculated Members pane |
Displays the currently defined calculated members available for use in the query. |
Filter pane |
Use to choose dimensions and related hierarchies to filter data at the source and limit data returned to the report. |
Data pane |
Displays the column headings for the result set as you drag items from the Metadata pane and the Calculated Members pane. Automatically updates the result set if the AutoExecute button is selected. . |
You can drag dimensions, measures, and KPIs from the Metadata pane, and calculated members from the Calculated Member pane, onto the Data pane. In the Filter pane, you can select dimensions and related hierarchies, and set filter expressions to limit the data available to query. If the AutoExecute (.gif "AutoExecute the query")
) toggle button on the toolbar is selected, the query designer runs the query every time that you drop a metadata object onto the Data pane. You can manually run the query using the Run (.gif "Run the query")
) button on the toolbar.
Graphical MDX Query Designer Toolbar in Design Mode
The query designer toolbar provides buttons to help you design MDX queries using the graphical interface. The following table shows the buttons and describes their functions.
.gif "Analysis Services MDX design view toolbar")
| Button | Description |
|---|---|
|
Edit the selected dataset. |
|
Delete the selected dataset. |
|
Refresh fields for the selected dataset. |
|
Switch to the generic query designer. Not enabled for this data provider. |
|
Switch to Command Type MDX. |
|
Switch to Command Type DMX. |
|
Refresh metadata from the data source. |
|
Display the Calculated Member Builder dialog box. |
|
Toggle between showing and not showing empty cells in the Data pane. (This is the equivalent to using the NON EMPTY clause in MDX). |
|
Automatically run the query and show the result every time a change is made. Results are shown in the Data pane. |
|
Show aggregations in the Data pane. |
|
Delete the selected column in the Data pane from the query. |
|
Display the Variables dialog box. When you assign a default value to a variable, a corresponding report parameter is created. |
|
Prepare the query. |
|
Run the query and display the results in the Data pane. |
|
Cancel the query. |
|
Toggle between Design mode and Query mode. |
.gif "Ellipsis (3 dots) to edit selected dataset")
.gif "Delete selected dataset")
.gif "Refresh dataset fields")
.gif "Change to MDX query view")
.gif "Change to DMX query language view")
.gif "Refresh result data")
.gif "Show Aggregations button")
.gif "Icon for the Query Parameters dialog box")
.gif "Prepare Query button")
.gif "Cancel the query")
.gif "Switch to Design mode")
Graphical MDX Query Designer in Query Mode
To change the graphical query designer to Query mode, click the Design Mode button on the toolbar. The following figure indicates the parts of the query designer in Query mode.
.gif "Analysis Services MDX query designer, query view")
The following table describes the function of each pane.
| Pane | Function |
|---|---|
Select Cube button |
Displays the currently selected cube. |
Metadata/Functions/Templates pane |
Displays a hierarchical list of measures, KPIs, and dimensions defined on the selected cube. |
Query pane |
Displays the query text. |
Result pane |
Displays the results of running the query. |
The Metadata pane shows tabs for Metadata, Functions, and Templates. From the Metadata tab, you can drag dimensions, hierarchies, KPIs, and measures onto the MDX Query pane. From the Functions tab, you can drag functions onto the MDX Query pane. From the Templates tab, you can add MDX templates to the MDX Query pane. When you execute the query, the Result pane displays the results for the MDX query.
Graphical Query Designer Toolbar in Query Mode
The query designer toolbar provides buttons to help you design MDX queries using the graphical interface.
The toolbar buttons are identical between Design mode and Query mode, but the following buttons are not enabled for Query mode:
- Generic Query Designer (
.gif "Icon of the Generic Query Designer button")
) - Add Calculated Member (
.gif "Add calculated member")
) - Show Empty Cells (
.gif "Toggle for show empty cells")
) - AutoExecute ()
- Delete (
.gif "Delete")
)
See Also
Other Resources
Using Analysis Services MDX Query Designer in Design Mode
Using Analysis Services MDX Query Designer in Query Mode
How to: Define Parameters in MDX and DMX Query Designer for Analysis Services (Report Designer)
How to: Add a Calculated Member for an Analysis Services MDX Query (Report Designer)
How to: Create a Dataset (Report Designer)
Using Analysis Services DMX Query Designer