Using SAP NetWeaver BI MDX Query Designer in Design Mode

New: 14 April 2006

When you create a dataset based on a SAP NetWeaver Business Intelligence data source, Report Designer displays the MDX graphical query designer in Design mode. Use the Design mode to interactively build an MDX query with graphical elements. You can select a cube, drag members to the Data pane, add calculated members, set default values for variables, and immediately view the result set. To view or edit the MDX query text directly, switch to Query mode by clicking the Design Mode () toggle button on the toolbar. For more information about Query mode, see Using SAP NetWeaver BI MDX Query Designer in Query Mode.

The graphical query designer in Design mode includes a toolbar, a Select Cube drop-down list, and three panes: a Metadata pane, a Calculated Members pane, and a Data pane. For more information about the user interface, see SAP NetWeaver BI Query Designer User Interface.

Cube Selection

Before you can create a query, you must choose a cube from which to retrieve the data. SAP NetWeaver Business Intelligence data sources provide InfoCube, MultiProvider, and Web-enabled queries to supply the metadata (for example, dimensions and key figures) needed for a query. Only subqueries created by Web-enabled queries support the use of variables in an MDX query. Use the Select Cube drop-down list to choose the appropriate InfoCube, MultiProvider, or Web-enabled query from your data source. When you select a cube, all MDX query text in the Query pane is cleared.

Metadata Pane

In the Metadata pane, you can browse the selected cube's metadata, such as dimensions and key figures. The hierarchical display for cubes shows Web-enabled queries nested under their corresponding InfoCube or MultiProvider. You can drag objects from the Metadata pane to the Data pane, where a corresponding column is added for that metadata. A vertical red line indicates the position where the column will be added. Dimensions are kept together on one of the Data pane and data (key figures, calculated members) are kept together on the other side of the Data pane.

The Metadata pane displays objects using their friendly names. The corresponding name used on the data source appears as a ToolTip when you pause the mouse pointer over a metadata object.

Calculated Members

The Calculated Members pane appears under the Metadata pane. Use the Calculated Members pane to create or edit calculated members to add to the Data pane as additional columns. Calculated members do not exist in the data source but are the result of calculations you create during the query execution. 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 creating a calculated member, see How to: Add a Calculated Member for a SAP NetWeaver BI MDX Query (Report Designer).

Variables and Filtering Data

Filtering data from an SAP NetWeaver Business Intelligence data source is accomplished by using variables defined on the data source as part of a Web-enabled query definition. Variables are defined on the data source at the time the query is created.

To view existing variables and to set static values, on the toolbar, click the Variables () button to open the Variables dialog box. For an example of setting a variable value, see How to: Set a Variable for an SAP NetWeaver BI MDX Query (Report Designer).

When you select or enter a static value for an existing variable in the Variables dialog box, a corresponding report parameter is created. The graphical query designer uses the friendly names for variables. The corresponding report parameter names are Common Language Specification (CLS) compliant names based on the names used on the data source. The label for the report parameter defaults to the name of the parameter.

Note

If you change a variable on a Web-enabled query at the data source, you must manually delete or edit the corresponding report parameter.

To view report parameters, in Report Designer, click the Report menu, and then click Report Parameters. For more information about the relationship between variables (also known as query parameters) and report parameters, see Filtering Data in a Report.

Extended Properties

The SAP NetWeaver Business Intelligence data processing extension supports extended field properties for multidimensional data sources. For the list of supported fields, see Using Extended Field Properties for an SAP NetWeaver BI System.

Results

Results from the MDX query are shown in the Data pane. If the AutoExecute () toggle button in the toolbar is on, every change to the Data pane automatically triggers the MDX query to run. If the AutoExecute toggle button is off, you can manually click the Run () button in the toolbar to execute the MDX query. By default, the AutoExecute button is on.

When you run a query the results are refreshed in the Data pane. To display changes to the cubes on the data source, use the Refresh () button on the toolbar to refresh the Metadata pane.

Note

By default, MDX query results hide empty cells. (This is the equivalent to using the NON EMPTY clause in MDX). To show empty cells, click the Show Empty Cells () toggle button on the query designer toolbar.

Query Definition

MDX queries are saved to the report definition file as straight MDX query text and as an MDX query specification. As you edit query text through the query designers, the matching query specification is automatically generated and saved to the report definition.

See Also

Concepts

Using SAP NetWeaver BI MDX Query Designer in Query Mode

Other Resources

SAP NetWeaver BI Query Designer User Interface

Help and Information

Getting SQL Server 2005 Assistance