Share via

  • Article

Using the Analysis Services MDX Query Designer in Query Mode (Reporting Services)

When you create a dataset from a SQL Server Analysis Services data source, Report Designer displays the MDX query designer in Design mode. To switch to Query mode, click the Design Mode (Switch to Design mode) toggle button on the toolbar.

The graphical query designer in Query mode includes a toolbar, a Select Cube (...) button, and three panes: a tabbed Metadata/Functions/Templates pane, a Query pane, and a Data pane. For a description of the user interface, see Analysis Services MDX Query Designer User Interface.

Note

Changes that you make in Query mode are lost if you switch back to Design mode.

Use Query mode to interactively build an MDX query. You can select a cube and drag dimensions, dimension attributes, levels, hierarchies, named sets, members, measures, and Key Performance Indicators (KPIs) to the Query pane. You can select functions or MDX templates to include in your query. You can also set default values for variables, and automatically preview the results returned for the query as you make changes to the Query pane. You can set filters to limit the data retrieved from the data set by the query and define parameters. Whenever you change the query in the Query pane, Reporting Services processes the MDX text to determine the columns to retrieve from the data source. These columns appear in the Report Data pane as dataset fields.

The text-based query designer is not available to build MDX queries. You must build MDX queries for report datasets using the MDX query designer in graphical or query mode.

Cube Selection Button

Before you create a query, you must select the cube from which to retrieve data. Click Select Cube (...) to open the Cube Selection dialog box. Analysis Services data sources provide the list of available cubes. By default, the first cube in the database is selected.

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

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. Use the metadata browser to drag objects to the Query pane.

The Metadata pane is located below the cube name on the left side of the query designer. This pane displays the objects stored in the current cube, which you can drag to the MDX Query pane. When you drag an object to the Query pane, the name of the object is placed in the query.

The following objects can be dragged from the Metadata pane to the Query pane:

  • Levels

  • Measures

  • Hierarchies and attributes

  • KPIs

You cannot drag member properties to the Query pane. For more information about referencing member properties, see Using Extended Field Properties for an Analysis Services Dataset and Working with Fields in a Report Dataset

Query Pane

Use the Query Pane to interactively build an MDX statement. A default statement is provided for you. Drag measures and dimensions from the Metadata pane to a valid location in the MDX statement. For more information about writing an MDX query, see Multidimensional Expressions (MDX) Reference.

Use the Prepare Query (Prepare Query button) button to validate the MDX syntax and check that any query optimization or other query processing completes successfully. Reporting Services analyzes the MDX text to identify the columns in the result set. These appear as the field collection for the dataset in the Report Data pane. This query is saved in the report definition along with MDX query text. You can also add or edit fields manually by using the Dataset Properties dialog box. For more information, see Working with Fields in a Report Dataset.

Result Pane

Use the Result pane to preview the query results. To run the query, click the Run button on the query designer toolbar.

Using Query Parameters in Query Mode

To add parameters to your MDX query, use the Query Parameters (Icon for the Query Parameters dialog box) button on the toolbar. This opens the Define Query Parameters dialog box. Specify a name and the details for the parameter. When you close the query designer, a report parameter is automatically created for every query parameter that you specify.

To manage the relationship between report parameters and query parameters, use the Dataset Properties dialog box. Although report parameters are created automatically from query parameters, you can manage report parameters separately. Also, if you change the name of a query parameter, or delete a query parameter, the report parameter that corresponds to the query parameter is not automatically changed or deleted. For more information, see Adding Parameters to Your Report and Working with Report Data How-to Topics.