The MDX Visual Designer control helps creating interactive reports based on Microsoft Analysis Services OLAP cube data. The reports can be simple or more complex, depending on the user’s skill.
The MDX Visual Designer control permits viewing cube metadata and creating an MDX query by visual dragging and dropping cube metadata description elements to the filter areas, columns, rows, and data. One can use cube dimensions, levels, hierarchies, and cube measures when creating an MDX query. The cube metadata used for the query will be shown in bold font.
It is possible to copy settings across areas, group settings in certain order, thus forming CrossJoins for rows and columns and embedded cubes.
The visual elements is comprises of the following areas:
- Toolbar - the control panel
- Report Designer Area - the report designer, the query designer function
- MDX Query Area
- Pivot Grid Area - Summary table, the result of the query
Report designer, query designer function - Report Designer Area
The Designer helps building an interactive report by dragging and dropping cube metadata description elements to the filter areas, rows and columns of the summary table.
Any dimensions, dimension hierarchies, hierarchy levels, and cube measures can be used in the report. When doing so, they are highlighted with bold font in the metadata description. The user can setup a filter for a group of measures for metadata descriptions, in order to limit the cube area used for designing the report.
The dimension hierarchies included in the rows and columns area, form a cross product of their members. The Crossjoin function of the MDX language is used for this. In so doing, it is possible to limit the list of hierarchy dimension elements by setting up filters; the sequence of corteges in the resulting set will determine the order of dimension hierarchies.
The commands shown in the table below are user for detailed setup of reports and MDX queries.
| Command | Description |
| Move up | Moves the dimension to a higher level in the cortege. |
| Move down | Moves the dimension to a lover level in the cortege. |
| Remove Field | Removes the dimension from the report configuration and the MDX query configuration. |
| Change Filter | Calls a filters setup dialog allowing to modify the filter, where a list of elements included in the report is determined. |
Toolbar - the control panel
The toolbar is used for control of the dynamic summary table. The toolbar includes commands that control the report generation rules and MDX query generation rules.
| Command | Description |
| Show/Hide query designer | Shows or hides the MDX query designer area. |
| Show/Hide MDX query | Shows or hides the MDX query area. |
| Edit MDX query | Allows manual editing of the query. |
| Generate MDX | Generates the MDX query based on the current settings determined in the designer. A manually edited query will be lost. |
| Customer Calculation Editor | Calls up a customer calculation editor dialog. |
| Run MDX query automatic | Automatically runs the MDX query after each user’s change in the designer. |
| Execute MDX query | Runs the current MDX query and shows the result. |
| Import query layout setting | Imports query settings from an XML file. |
| Export query layout setting | Exports the query settings to an XML file. |
By default, changing the settings in the designer would automatically change the MDX query and generate a report based on the query.
MDX Query Area
The query area is intended for viewing and editing the MDX query. The MDX query is generated automatically after changing the settings in the designer. If the user has sufficient skills, he can modify the MDX manually and use all capabilities of the MDX query language in order to achieve the most efficient solution. It is possible to save the edited query for loading again later.
Pivot Grid Area - the Summary table
A detailed description of the Pivot Grid Area is given in the Pivot Grid section.
