Ranet OLAP provides wide options for analysis of OLAP data in the pivot table. Dynamic Pivot Table control allows to create reports by simply dragging and dropping elements in the pivot table area without generating custom MDX queries. End users can manipulate the data using the commands in two context menus, Cell context menu and Rows and Columns context menu.
We already wrote about the main commands for multidimensional data analysis in the Cell context menu and the sorting options it provides.
Now, let’s review the commands that can be applied to rows and columns in the pivot table.
Expand and Collapse
The pivot table lets you expand and collapse OLAP data to any level you need.
Expand command can be applied to any parent element in the pivot table and opens the immediate level of child items for this element. Using the Expand command, you can display the whole hierarchy of OLAP data items for a particular element, down to the final child item(s).
The pivot table receives all OLAP data anew upon Expand command execution (not just the list of child elements). The more nodes are opened, the more data the pivot table receives from the server, which can significantly slow down command execution in case there are a lot of dimension elements. Use Expand command only when it is required, and replace it with the Drilldown command when possible.
Collapse command is opposite to Expand – it rolls the hierarchy data up to the required element.
Drilldown operations
Ranet OLAP offers three Drilldown operations in the pivot table:
- Drilldown
- Drilldown with parent
- Drilldown by
Drilldown command allows you to move to a lower level in the pivot table for a particular element. Unlike Expand that loads the whole hierarchy from OLAP server, Drilldown only shows the child items for the selected element, which helps to speed up the command execution.
In case you need to see the aggregated value, too, use the Drilldown with parent command. In this case, the pivot table will show the selected element and its child items.
In order to go up to a higher hierarchy level, use the Back navigation command.
If you work with a complicated pivot table analyzing OLAP data from different hierarchies, the server has to process a large amount of data, and executing commands like Expand can take a long time. Besides, each time a user expands the hierarchy, the pivot table becomes more and more complicated and hard to work with.
Using Drilldown by helps to quickly switch between different hierarchies or dimensions without the need to change the report layout. End users have to click Drilldown by in the context menu and choose the hierarchy used to detail the data. As a result, the settings in the Selection panel will automatically change – the field to which the Drilldown operation was applied and all fields to its left in the pivot table will move to the Filters area, and the field from the hierarchy used to detail the data will appear in the Rows or Columns area.
Drilldown by command is only available when you use the Dynamic Pivot Table control.
Navigation
User commands that result in changes in the array of data displayed in the pivot table are saved in Navigation history. They include all commands mentioned above, i.e. Expand, Collapse, Drilldown, Drilldown with parent, and Drilldown by.
Users can go back and forth the navigation history to restore previous states of the pivot table using the four navigation commands in the Toolbar. To beginning recovers the initial state of the pivot table, To end restores its latest state. Back and Forward move one step back and forth in the pivot table state history.
You can try pivot table commands in our demo examples.