If the cell styles defined in the cube are not enough for efficient data visualization, Ranet end users can create custom pivot table cell styles. They significantly increase usability and simplify data analysis in the pivot table. The figures below show HTML and Silverlight pivot table with applied custom styles.

pivot table styles ranet olap

Custom cell styles

Custom cell styles in the pivot table are based on conditional formatting rules that allow highlighting cells or cell areas that correspond to certain conditions. Cell styles are set up via a special pivot table cell styles designer.

Cell Styles Designer

Ranet OLAP pivot table cell styles designer is available in HTML and WPF/Silverlight versions, with certain functionality differences. It uses conditional formatting to set up criteria for custom cell styles in the pivot table. Users can create several styles for the pivot table and several conditions for the same style. It is worth noticing that conditions can only be created for numeric values. If the data in the cells is not numeric, users cannot apply custom styles to these cells.

Cell style designer allows highlighting pivot table cells or cell areas using:

  • background and font colors
  • images
  • data bars(WPF, Silverlight)
  • sparklines (WPF, Silverlight).

One style can contain different types of conditional formatting rules.

Custom Pivot Table Cell Styles

As mentioned above, there are several ways to create custom pivot table cell styles.

Background and Font Color

Users can set up different background and font color depending on the cell’s value. The color palette is fixed and cannot be changed by the user. In the WPF and Silverlight versions, users can also choose to apply color to the cell border. The figures show how to apply background and font colors in Silverlight and HTML pivot tables.

Customizing cell appearance in Ranet OLAP Silverlight

Customizing cell appearance in Ranet OLAP HTML.

Images

Users can choose to use images in the cells, together with (or instead of) values. The set of images in the tools are fixed and cannot be changed by the user. Using images in pivot table cell styles helps visualize data trends and changes over a period of time, indicate if values are above or below the critical level, etc.

Using images in cell styles (Silverlight)

Using images in cell styles (HTML)

Data Bars (WPF, Silverlight)

Data bars help the user to see the difference between cell values vividly - the bigger the value the longer the data bar.

Users can customize the data bar to see maximum and minimum values, use several colors, gradients, etc. (see figure below). Settings applied to parent items are inherited by all their child.

Data bars are especially helpful when users need to analyze important indicators when working with large amounts of data.

Data bar settings in Ranet OLAP Silverlight.

Sparklines (WPF, Silverlight)

Sparklines are small charts that are shown inside a cell in the pivot table. They are useful in demonstrating the dynamics of data in time or during different seasons, identifying peak values, etc.

Pivot table cell style designer in Ranet OLAP allows users to customize the view of the sparkline and select its type (line, bar), whether to show points, markers, positive and negative areas, and the average value (see figures below).

Sparkline settings in Ranet OLAP Silverlight.

Using sparklines (bar and line) in Ranet OLAP Silverlight.

Using sparklines and data bars is now available in WPF and Silverlight versions. We are planning to add this functionality to the HTML version in the future releases. In WPF and Silverlight versions, users can save custom pivot table cell styles and use them later.

We hope this article was useful and you have a better understanding how to use the pivot table cell styles designer and create custom cell styles in Ranet OLAP.

Feel free to contact us if you have any question or leave comments below.