Using Analysis Services MDX Query Designer in Design Mode

Article
12/03/2008

When you create a dataset from a Microsoft SQL Server Analysis Services data source, Report Designer displays the MDX query designer in Design mode. Use Design mode to interactively build an MDX query using graphical elements. You can select a cube and drag dimensions, dimension attributes, levels, hierarchies, measures, and Key Performance Indicators (KPIs) to the Data pane. You can also add calculated members, set default values for variables, and automatically see the result set returned for the query you build as you make changes to the Data pane. You can set filters to limit the data retrieved from the data source set by the query and define parameters. To view or edit MDX query text directly, switch to Query mode by clicking the Design Mode ( Switch to Design mode ) toggle button on the toolbar. For more information, see Using Analysis Services MDX Query Designer in Query Mode.

The graphical query designer in Design mode includes a toolbar, a Select Cube button, 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 Analysis Services MDX Query Designer User Interface.

The generic query designer button is not enabled for this query designer.

Cube Selection button

Before you create a query, you need to select the cube from which to retrieve data. Use the Cube Selection button to open the Cube Selection dialog box. Microsoft Analysis Services data sources provide the list of cubes available for you to use. By default, the first cube in the database is selected.

To view instructions about selecting a cube, see How to: Select a Cube (Report Designer).

Metadata pane

In the Metadata pane, you can browse the selected cube's metadata on the underlying data source, such as dimensions, levels, hierarchies, attributes, measures, and KPIs. The following objects can be dragged from the Metadata pane to the Data and Filter panes:

Dimensions
Levels
Members
Measures
Hierarchies and attributes
KPIs
Named sets and members (to Filter pane only)

You cannot drag member properties to the Query pane. To reference member properties, see Using Extended Field Properties for an Analysis Services Database.

Dragging an object to the Data pane creates one or more columns for the result set. When the query runs, values from the data source are retrieved for these columns. Columns on the Data pane become the fields for a dataset. You can also add or edit fields manually using the Fields tab of the Datasets dialog box. Use the Refresh Fields ( Refresh dataset fields ) button on the toolbar to view the fields defined for the current query in the Datasets window. For more information about the Datasets window, see Working With Fields in a Report Dataset.

Calculated Members pane

In the Calculated Members pane, you can create or edit calculated members to use in your query. Calculated members are custom members that are defined in the query that do not exist in the underlying data source. For example, if a cube contained the members Sales and Cost, you could define a calculated member named Profit that displays the difference between Sales and Cost.

To view instructions about defining a calculated member, see How to: Add a Calculated Member for an Analysis Services MDX Query (Report Designer).

After you have defined a calculated member, you can drag it to the Data pane just as you would an object from the Metadata pane.

Filter pane

In the Filter pane, you can define criteria by which to filter data from the data source. This limits the result set retrieved by running the query and reduces the amount of data the report has to process. In the Filter pane, you can select a dimension and hierarchy on which to filter, and set the operator and filter expression to apply. The filter expression can be a single item, a set of items, or a parameter.

When you define a value or member as the criteria for a filter item, that value or member becomes the default value for the associated report parameter. You can change this default by setting a different default value for the report parameter.

To view instructions about editing a report parameter, see How to: Add, Edit, or Delete a Report Parameter (Report Designer).

You can drag objects from the Metadata pane to the Filter or use the <Select dimension> drop-down list to choose a dimension. You can also drag named sets and members to this pane. Remove filter criteria by selecting it and clicking the Delete ( Delete ) button on the toolbar.

To view instructions about defining filters and parameters, see How to: Add a Filter in MDX Query Designer for Analysis Services (Report Designer).

Note

If you define a filter item which results in no members meeting the filter criteria, the query fails with an "Empty Subcube" or a "No rows found" error.

Data pane

Use the Data pane to interactively build the query results you want. You can drag items from the Metadata pane and Calculated Members pane to add columns to the Data pane.

By default, the results of the query are displayed whenever you modify it. You can turn off this feature by clicking the Auto Execute ( AutoExecute the query ) toggle button in the query designer toolbar. To manually run the query, click the Run () button on the query designer toolbar.

When you click the Run button, only the results in the Data pane are refreshed. To refresh your view of the underlying data source (for example, when the cube changes and you want to view the changes in the Metadata pane), click the Refresh ( Refresh result data ) button on the query designer toolbar. The Refresh Fields button saves the current query to the report definition and refreshes the dataset fields in the Dataset window.

Note

By default, queries that you build using the MDX query designer in design view hide empty cells. (This is equivalent to using the NON EMPTY clause in MDX). To show empty cells, click the Show Empty Cells ( Toggle for show empty cells ) button on the toolbar.

Using Query Parameters

You can add parameters to your query by selecting the Parameters check box for the member in the Filter pane. After selecting this option and switching to Layout view, the query designer automatically creates a report parameter and valid values dataset for the parameter. You can view and edit the valid values dataset in Data view. The name of the dataset is based on the name of the parameterized member in the Filter pane.

To view instructions about defining filters and parameters, see How to: Add a Filter in MDX Query Designer for Analysis Services (Report Designer).

Query Definition

When you create a query in Design mode, the query designer creates a query specification in the report definition. A query specification is an XML representation of the query. The query designer automatically creates an MDX query from the query specification. Both the query specification and the MDX query are stored in the report definition for the report.

To Create a Dataset

On the Data tab, from the Dataset drop-down list, select <New Dataset>.
On the Dataset dialog box, from the Data source drop-down list, select New Data Source.
On the Data Source dialog box, from the Type drop-down list, select Microsoft SQL Server Analysis Services.
Specify a connection string that works with your Analysis Services data source.
Contact your database administrator for connection information. The following connection string example specifies the Adventure Works DW sample data warehouse on an Analysis Services data source on the local server:
```
Data Source=(local);Initial Catalog="Adventure Works DW"
```
Set the database credentials in the Data Source dialog box on the Credentials tab.

For more information, see Connecting to a Data Source.

Change History

Release	History
14 April 2006	Changed content: Updated introduction. Included links to MDX Query Designer UI topic. Updated Metadata pane details.

Share via