Using SAP NetWeaver BI MDX Query Designer in Query Mode

New: 14 April 2006

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

The graphical query designer in query mode includes a toolbar with buttons, a Select Cube drop-down list, and four panes: a Metadata pane that includes tabs for Metadata and Functions, a Variables pane, a Query pane, and a Result pane. For more information about the user interface, see SAP NetWeaver BI Query Designer User Interface.

Before you can use Query mode to create a query, you must choose a cube from which to retrieve the data. SAP NetWeaver Business Intelligence data sources provide InfoCubes, MultiProviders, or 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 a 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.

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 Query pane where corresponding MDX query text is added for that metadata. The text is added at the cursor insertion point. Metadata names are converted to the names defined on the data source.

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.

In the Functions pane, you can browse available functions to use in an MDX query. You can drag functions to the Query pane. The function is inserted at the cursor insertion point.

To filter data from an SAP NetWeaver Business Intelligence data source, use 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 subquery is created. When you select a cube, variables defined for that cube appear in the Variables window.

To view existing variables and to set static values, on the toolbar, click the Variables (Icon for the Query Parameters dialog box) 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 a 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 name is a Common Language Specification (CLS) compliant name based on the name used by the SAP NetWeaver Business Intelligence data source. The label for the report parameter defaults to the name of the parameter but can be changed in the Report Parameters dialog box.

If you change a variable on a 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.

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 from the MDX query are shown in the Result pane when you click the Run (Run the query) button on the toolbar.

To refresh the Metadata pane to display changes to the cubes on the data source, click Refresh (Refresh dataset fields) on the toolbar. Running a query refreshes only the Result pane.

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 for show empty cells) toggle button on the query designer toolbar.

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.

Community Additions