Export (0) Print
Expand All

Using Analysis Services MDX Query Designer in Query Mode

Updated: 14 April 2006

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

ms155824.note(en-US,SQL.90).gifNote:
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 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.

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

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

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

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

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 (Ellipsis (3 dots) to 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.

Release History

14 April 2006

New content:
  • Updated introduction.
  • Included links to MDX Query Designer UI topic.
  • Updated Metadata pane details.

Community Additions

ADD
Show:
© 2014 Microsoft