Using Analysis Services MDX Query Designer in Design Mode (Report Builder 2.0)
When you create a dataset from a SQL Server Analysis Services data source, Report Builder 2.0 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 (.gif "Switch to Design mode")
) toggle button on the toolbar. For more information, see Using Analysis Services MDX Query Designer in Query Mode (Report Builder 2.0).
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 (Report Builder 2.0).
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 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 Builder 2.0).
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 (Report Builder 2.0).
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 manually edit the dataset field collection in the Report Data pane. For more information, see How to: Add, Edit, or Delete a Field in the Report Data Pane (Report Builder 2.0).
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 Builder 2.0).
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, Change, or Delete a Report Parameter (Report Builder 2.0).
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 (.gif "Delete")
) button on the toolbar.
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 (.gif "AutoExecute the query")
) toggle button in the query designer toolbar. To manually run the query, click the Run (.gif "Run the query")
) 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 (.gif "Refresh result data")
) button on the query designer toolbar.
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 (.gif "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.
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.