DYNAMIC PIVOTGRID WITH MDX QUERY DESIGNER

The MDX Visual Designer control permits 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.

Structure of the dynamic summary table (MDX Designer)
RUI_MDXDesign_01_.png

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. Learn more Customer Calculation Editor
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.

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.

Designing a report, MDX query
RUI_MDXDesign_01_2.png

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 members by setting up filters; the sequence of corteges in the resulting set will be determined by 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. Learn more Change Filter

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.

Last edited Oct 6, 2016 at 8:13 AM by Leschenok, version 14