Using Analysis Services MDX Query Designer in Query Mode

When you create a dataset from a Microsoft Analysis Services data source, Report Designer displays the MDX query designer in Design mode. To switch to Query mode, click the 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.

Use Query mode to interactively build an MDX query or to enter MDX text directly. 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 Dataset window as dataset fields.

The generic query designer is not enabled for the Microsoft Analysis Services data processing extension.

Cube Selection Button

Before you create a query, you must select the cube from which to retrieve data. Click Cube Selection to open the Cube Selection dialog box. Microsoft 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 (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. 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. To reference member properties, see Using Extended Field Properties for an Analysis Services Database. Working With Fields in a Report Dataset

Query Pane

You can drag objects onto the Query pane or enter MDX query text directly. For information about writing an MDX query, see Multidimensional Expressions (MDX) Reference.

Use the Prepare Query () 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 being returned so the dataset fields can be presented in the Datasets window. This limited query is saved in the report definition along with MDX query text. You can also add or edit fields manually on the Fields tab of the Datasets dialog box. Use the Refresh 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.

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

If your query contains parameters, Report Designer automatically creates report parameters that correspond to query parameters in the report when you type the query. For example, the following MDX query creates a report parameter named ProductColor:

SELECT NON EMPTY { 
   [Measures].[Internet Order Quantity] } ON COLUMNS, 
   NON EMPTY { ([Product].[Product].[Product].ALLMEMBERS * 
      [Product].[Color].[Color].ALLMEMBERS ) }
   DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM 
   ( SELECT 
      ( STRTOSET(@ProductColor, CONSTRAINED) ) ON COLUMNS 
       FROM [Adventure Works]) 
        CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, 
        FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

To manage the relationship between report parameters and query parameters, click the Edit Selected Dataset () button on the toolbar to open the Dataset dialog box, and click the Parameters tab. 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. To remove a report parameter, from the Report menu, select Report Parameters. Select the parameter you want to remove, and click Remove. For more information, see Working with Parameters in Reporting Services.

See Also

Concepts

Defining Report Datasets for Analysis Services Multidimensional and Data Mining Prediction Data
Using Analysis Services MDX Query Designer in Design Mode
Using Analysis Services DMX Query Designer

Other Resources

Analysis Services MDX Query Designer User Interface

Help and Information

Getting SQL Server 2005 Assistance

Change History