Ranet OLAP Simple Pivot Table Control

HTML, WPF

When the pivot table works separately from the pivot table designer, it serves as a Simple Pivot Table control.

In this case, the pivot table is initiated with a custom MDX query that is not connected to the designer in any way and cannot be modified through the Selection Panel. Using this control allows generating reports based on more complex queries than the ones created using the pivot table designer. Custom MDX queries enable retrieving and analyzing data slices that can be unavailable otherwise.

Ranet OLAP pivot table offers a lot of opportunities to manipulate and analyze OLAP data. Most functionality is the same in both Dynamic and Simple pivot table controls. However, there are slight differences.

Users can apply commands to rows/columns elements and cells (cell areas) using the context menu and cell context menu, respectively.

Working with Rows and Columns in the Pivot Table

Ranet OLAP offers a number of options to manage elements in the rows and columns areas of the pivot table.

HTML

WPF

Users can sort pivot table rows and columns by property or caption, in ascending and descending order. Read more about sorting in the pivot table in our blog.

Users can also view custom properties for elements in the rows and columns, which provide additional information about the elements in the pivot table.

Expand and Collapse commands allow showing (hiding) child items for the selected element in the pivot table. If the element has a lot of child items, Expand can significantly slow down the application, so it is sometimes better to use Drilldown commands instead. They change the structure of the pivot table, showing either child items for the selected element (Drilldown) or child items with the selected element (Drilldown with parent).

HTML

WPF

Users working in the Dynamic Pivot Table can also drill down the data by selected measures. This option is not available in the Simple Pivot Table.

WPF

Ranet OLAP for WPF offer more commands in the rows and columns context menu.

Several commands from the Toolbar can also be applied using the context menu.

Users can refresh the pivot table’s layout, hide empty rows/columns, show MDX query, navigate back and forth in the pivot table history, and export the pivot table to Excel or XML.

Users can set auto width for columns and select different member group modes.

Working with Cells in the Pivot Table

Cell context menu in Ranet OLAP allows users to apply commands to the data in particular cells and cell areas.

HTML

DrillThrough shows all detailed data used to calculate the cell’s value in a separate table.

Build filter axis and Leave current structure commands automatically create pivot table filters based on the cell(cells) they are applied to (only available when working with the Dynamic Pivot Table).

WPF

The options in the cell context menu in WPF version are different from the HTML version.

The menu replicates a large portion of the rows and columns menu while adding some cell-specific commands. In particular, it allows to apply DrillThrough to the cell values, copy cell values to the clipboard, and apply custom styles to the cells in the pivot table.

Custom Cell Styles

For better data visualization in the pivot table, users have an opportunity to create custom styles for the pivot table cells, which can enhance the usability of the table and enable better data analysis.

A special Cell Style Designer allows users to create conditional formatting rules for cells. Users can set up different background colors, font colors, as well as use visual elements in the cells. WPF versions allow visualizing the data in the pivot table using data bars and sparklines.

What-If Analysis

If the OLAP cube you use as the data source with Ranet OLAP has write-back partitions, the pivot table supports what-if analysis option. All changes to the pivot table data are stored in a separate data table and are not mixed with the factual one. The option is available in all Ranet OLAP versions.

When a user works with the pivot 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, thus ensuring the necessary level of data security.

Pivot Table data can be exported to Microsoft Excel or a .xml file. Users can also generate a URL to open the saved report later in the browser.