The Pivot Grid control shows a summary table which is a result of running the MDX query.

View of the summary table

What is the functionality of the Pivot Grid control?

The Pivot Grid control can:

  • Show values using the visual parameters of the cells determined in the cube or in the MDX query (BACKCOLOR, FORECOLOR, FORMAT_STRING, etc.)
  • Display icons and indicators, show gradient filling and formatted text in the cell
  • Display custom properties of dimension members (for example: color, size, price, etc.)
  • Generate element names in the row or column title areas using various algorithms (for example: Caption, Caption+Key, Key);
  • Control the data detail levels using commands Expand, Collapse, Drill down
  • Show or hide empty columns or rows ;
  • Translate (rotate) rows and columns
  • Arrange (sort) elements in rows and columns based on various conditions (see also Sorting)
  • Group elements within rows and columns (see also Members Group)
  • Navigate the query history
  • Scale the displayed data (Zoom)
  • Copy cell values (a selected portion) to the Clipboard
  • Show pop-up descriptions and context menus for the dimension members and cells
  • Export data to the Microsoft Excel format for further processing or analysis ;
  • Show the detailed data behind the value of an indicator shown in a cell, in a special window (DrillThrough)
  • Show system properties (Member Properties) and Custom Properties of the dimension members
  • Show the MDX query used for generating the pivot grid table in a special window (see also Show MDX)

If the group of cube measures supports editing (if it has a writeback partition), then the pivot grid table permits:

  • Edit the cube data using a special entry field and an MDX expression describing the cube renewal algorithm (the UPDATE CUBE command)
  • Highlight editable cells and the cells changed by the user with special background colors
  • Calculate the value of the edited cell based on the current data context, using operators and functions available in the MDX language
  • Paste cell values from the clipboard
  • Undo individual changes or whole block of changes (restore table data in initial state or state before recalculation)
  • Save the changed data in a cache memory and send them to the server for synchronization and recalculation on a user's command
  • Highlight the cells containing the changes synchronized with the server in a special background color
  • Isolate the changes within a user session (keeping the changes inaccessible for other users) until the transaction is fixed
When showing the query result and further processing the query by the user in the pivot grid table, settings of the SQL Server security model for the Analysis Services data, the user's role, and the current data context are taken into account. This ensures the necessary level of data security.

Last edited Jan 4, 2013 at 11:41 AM by Leschenok, version 10