Introduction

The Adaptive Pivot Table is a powerful alternative to the standard pivot functionality in Sisense. It allows users to easily set columns, values, and rows, and provides flexibility to manipulate and change the view on-the-fly. It offers the ability to collapse and expand data, transpose data, and create custom calculation fields. These features are not available to Viewers with the standard Sisense pivot.

The Adaptive Pivot Table enables Viewers to access and make changes without needing Designer access. It is a highly customizable pivot table that helps to keep staff engaged on the dashboard and eliminates the need to download data into Excel. It's an ideal tool for customers who use dashboards and need to change their view or look at the data in a specific way.

Getting Started

Before Installing this Add-on, please make sure you've installed and configured the RAPID Plugin Manager.

Download the latest RAPID BI Plugins from the Members Area.

More information can be found here: https://www.rapidbi.com.au/members/index.php?/Knowledgebase/Article/View/getting-started

Installation

1. Download the plugin

2. Extract the zip file to `\Plugins` folder in Sisense (File Manager can be accessed via. Admin > Server & Hardware > System Management > File Manager (top right hand corner))

3. Ensure that the RAPID Plugins Manager has also been installed and enabled in the addons page.

4. After rebuild, the plugin should be accessible through the advanced widget editor. You will need to refresh your browser.

How to use

Adding Widget to Dashboard

1. On a dashboard, select the + Widget button in the top right hand corner

2. When the New Widget Prompt appears, select Advanced Configuration in the bottom left hand corner.

3. When the Widget Editor appears, select the widget type in the top left hand corner and select the Adaptive Pivot Table widget.

Widget Overview

Below is an image outlining the key areas of the initial Widget Editor view.

1. Items Panel

This panel can contain either Dimensions or Measures.

Anything you would like the user to be able to group by should be added as a Row or a Column (the user can change which they are in later).

Any values should be added under Values along with the appropriate aggregation.

You can add a great deal of items to this panel to give the end user a lot of choice in what they would like to see. Hint: Add a lot of itemsto give your users rich data analysis capability, but disable most of them (by pressing the pivot settings cog right top of pivot and unticking). This way you can set a default state that users might expect for the Adaptive Pivot, but also allow them to include additional items or remove them as they see fit.

2. Widget Preview

This area previews what the Adaptive Pivot will look like on the dashboard. It is affected by the styling changes the designer makes on the design panel, items panels or pivot options (bar on top and the settings cog).

3. Design Panel

This panel allows configuration of the Adaptive Pivot.

It allows the designer to change styling settings, header details and configure advanced functionality.

Adding Items to Widgets

To add items to the widget, navigate to the left hand panel of the editor. Here you will be able to add columns, values (including custom formulas) and Rows.

As observed there are 3 options to add your items to the adaptive pivot table widget, columns, values and rows. 

Add all the fields you would like an end user to be able to use within the pivot table. These can then be turned on/off, placed in row/cols/vals or filtered by within the Adaptive Pivot by pressing the cog icon (also available to Viewers). 

A great feature of the RAPID pivot, is that you can add many items under each of these headings, and they will be 'nested' within the others.

For example, below I have added an additional item 'VendorName' to the Column, you can see now that nested within each country, the OrderRevenue is broken down by VendorName.

Some additional choices you can make when adding your items to the widget are whether you would like Sub and Grand Totals. 

The first tab on the top left of your table allows you to customise the subtotals shown:

The second tab on the top left of your table allows you to customise the grand totals shown:

The third tab on the top left of your table allows you to format the numbers (values) shown.

This give you options of selecting which values are formatted in which ways. For example you may have currency, decimal and whole number values:

The last tab on the top left of your table allows you to conditionally format the cells within the pivot table:

This allows the user to add conditional formatting to the cells, using text fonts, text colours and highlight cell colours based upon the values within the cell.

Below I have added the conditional formatting that if the OrderRevenue is equal to or less than $8,000, the cell will be highlighted red.

I have also added a condition that if the OrderRevenue is equal to or greater than $12,000, the cell will be highlighted green. 

Many conditions may be added to the same pivot table. 

Once you have finished you can click apply, to have your pivot table added to your dashboard.

Changing rows/cols/vals, grouping order, and filtering

Once your RAPID pivot table is on your dashboard, a users can change what is shown on the Adaptive Pivot table by clicking on the table settings cog on the right of the RAPID pivot.

This will bring up the below settings, which will be familiar if you have worked with pivot table in excel. This is where you can change your variables from columns to rows, filters or values and vice-versa, by clicking and dragging the variable on the 6 dots to the left of each variable and dragging to the new area you would like it to go. You can also enable and disable fields, and even filter on members by pressing the filter icon next to the member you want to filter. Please note that only member based filtering is available to end users.

Once complete, click Save Layout to apply to your pivot table. Any changes made outside of Widget Editor will apply to the user only. Any changes you make in Widget Editor will apply as the Initial State of the pivot when it is first accessed by a user or when they choose RESTORE INITIAL STATE.

Custom Fields and how to use averages

It is best to include anything that can be summed up as a Value in the values panel that the pivot Viewer can choose from.

However, what if it cannot be summed. What if it is an average?

In this case, including a average as a Sisense formula is not the best solution. This is because as you drill up/down in the resultset you will be seeing an average of an average or a sum of an average.

A better approach is to bring in the summed up fields that create an average. E.g. Sales and Quantity. You can then use a Custom field to create an average that always holds true regardless of the level of grouping.

In the below example, we have made Qty and Sales available through the Sisense Values panel in widget editor.

By pressing the Pivot settings cog, and then Calculated Field, we can create an average unit price. The interface allows simple drag/drop for fields and drop-downs for aggregation type.

Now, our Avg Unit Price will be correct regardless of the level of grouping. Because we had total Sales and total Quantity, it will not be an average of an average.

Sales and Qty are visible in the image below to show that this approach works, however you do not have to have those fields visible in the final pivot for the Calculated Field to display correctly.

Design Options

In addition to the usual widget design within Sisense, you can edit your pivot table with the following options:

Header Title: Give your pivot table a relevant title.

Row Styling: Change the background and text colour for your rows.

Column Styling: Change the background and text colour for your columns.

Value Styling: Change the background and text colour for your values.

Additional design options are shown below. These effect how the pivot displays and allow you to set the maximum data points to balance performance with capability: