Video, Movie

Download Ranet OLAP video course: pivot_grid_01en_lite.swf

Contents Video


Let's briefly overview features of the dynamic pivot grid included in the Ranet OLAP library.
The Pivot Grid is comprised of:
  • Toolbar,
  • Cube Metadata area,
  • Report Structure area,
  • MDX Query Area area
  • Pivot Grid Area showing the result of the query
We shall use demonstration database Adventure Works build on the Microsoft SQL Server for demonstrating the features of the pivot grid.
The cube metadata are displayed as a hierarchy (in the form of tree). The tree shows: KPI, Measures, Sets, Dimensions.
The Report Structure area is intended for designing the structure of the report. It includes areas:
  • Filters,
  • Columns,
  • Rows,
  • and Data.
The report is designed by simply dragging cube metadata with the mouse and dropping them into the respective area of the report structure.
When selecting a dimension, all members of the dimension are included in the report automatically. In order to limit this set of members, we shall setup a filter and select the dimension members that we need. The result of the selection is shown in the Selected tab. It is generated based on the rules of the MDX query language, and it is a Set.

Customer Calculation

The Pivot Grid has a built-in designer for customer calculations. The designer permits to describe any indicators or sets using all features of the MDX language. It allows to set up any report.
It is possible to determine format parameters for calculated members in the designer: FORMATSTRING, FORECOLOR, BACK_COLOR, etc. In addition, it is possible to write a script which will determine the formatting conditions. Let's add the calculated members to the report structure area and run the query. The result of the design is shown in the Pivot Grid.

Export to Microsoft Excel

The Pivot Grid offers a number important data analysis services to the user.
First, the user can export a report, generated in the Pivot Grid, as a Microsoft Excel file for further analysis. In so doing, the data will be exported in formats based on the formatting settings implemented in the cube and in the MDX query.
It is possible to save the pivot grid data to a local Excel file. This feature gives the user an option to do detailed data analysis using a familiar tool, or while working offline.

Display any MDX query

The Pivot Grid can display any MDX query, including very complex queries. It can be a query designed by the user himself, or a query prepared manually by an IT specialist. This feature is not very common in similar products. In so doing, all services intended for detailed data analysis (such as Drill Down, Drill Up, Expand, Collapse, DrillThrough) are still available to the user.
The user can modify the MDX query in real time and run it immediately. When editing the query, it is possible to use all functions available in the MDX query language and a limited set of Visual Basic functions.

Last edited Sep 26, 2011 at 4:26 PM by Leschenok, version 6