Using SAP NetWeaver BI MDX Query Designer in Query Mode
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 (.gif "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.
For more information about creating an SAP NetWeaver Business Intelligence data source and dataset, see How to: Retrieve Data from a SAP NetWeaver BI Data Source.
Cube Selection
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.
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 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.
Data Source Variables and Filtering Data
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. When you select a cube, data source variables defined for that cube appear in the Variables window.
Note
Do not confuse report variables and data source variables. Report variables are defined in the report, calculated once at first reference, and used as read-only values in expressions in a report. Data source variables are defined on the data source at the time the subquery is created and are part of the query.
To view existing variables and to set static values, on the toolbar, click the Variables (.gif "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.
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.
Note
If you change a variable on a query at the data source, you must manually delete or edit the corresponding report parameter.
For more information, see Filtering Data in a Report and Using Parameters to Control Report Data.
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 Data Source.
Results
Results from the MDX query are shown in the Result pane when you click the Run (.gif "Run the query")
) button on the toolbar.
To refresh the Metadata pane to display changes to the cubes on the data source, click Refresh (.gif "Refresh dataset fields")
) on the toolbar. Running a query refreshes only the Result 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 (.gif "Toggle for 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.