Using SAP NetWeaver BI MDX Query Designer in Design Mode (Report Builder 2.0)
When you create a dataset based on an SAP NetWeaver Business Intelligence data source, Report Builder 2.0 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 (.gif "Switch to Design mode")
) toggle button on the toolbar. For more information about Query mode, see Using SAP NetWeaver BI MDX Query Designer in Query Mode (Report Builder 2.0).
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 (Report Builder 2.0).
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 (Report Builder 2.0).
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 Builder 2.0).
Data Source 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.
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 (Report Builder 2.0).
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.
For more information, see Filtering Data in a Report (Report Builder 2.0) and Using Parameters to Control Report Data (Report Builder 2.0).
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 (Report Builder 2.0).
Results
Results from the MDX query are shown in the Data pane. If the AutoExecute (.gif "AutoExecute the query")
) 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 (.gif "Run the query")
) 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 (.gif "Refresh dataset fields")
) 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 (.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.