User Guide - Adaptive Pivot Table
Posted by Jaimee van Kampen, Last modified by Shane Collins on 11 September 2023 04:35 pm
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.
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
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 RAPID Pivot plugin.
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.
The designer can add items that their end users can use within their RAPID pivot table.
We can also add formulas as items in this panel.
2. Widget Preview
This panel 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.
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 (such as selecting specific widgets to target or ignore).
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.
In the example below I have added the CountryName in the columns at the top, OrderRevenue as the values displayed in the cells, and Years in Date as the rows on the left.
It is important to add all the fields you would like to feature within your pivot table, which can be changed later.
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.
Switching between Items on Widgets
Once your RAPID pivot table is on your dashboard, you can switch through what is shown on your RAPID pivot table by clicking on the table settings item on the right of the RAPID pivot.
This will bring up the below settings, which will be familar 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.
Here you can also create calculated fields or edit the values you have already selected. For example, below is the options that I might want to show for OrderRevenue, rather than showing the total (sum). This gives the option to use other mathematical and statistical fields to show our values. You can also use a custom formula in the box for more advanced statistics or to show the relationship between different variables.
Once complete, click ok to apply to your pivot table.
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 around values and totals shown below, you can choose to enable or disable any of these options: