Add Financial Formatting to Pivots/Indicators
Download: negativesFormatter.zip
Introduction
This article explains how to adjust Pivot tables and indicators to use standard financial formatting of numbers. This means showing negative numbers as always RED with parentheses instead of a minus sign, as well as populating empty (null) cells with 0 values.
Before using this plugin:
After applying this plugin:
Steps
The following steps will walk through the process of adding the new chart type and creating a sample funnel chart.
Step 1 - Add the Plugin
Download the attachment and unzip the contents into your C:\Program Files\Sisense\PrismWeb\plugins\ folder. If you are using version 7.2 and higher unzip the contents into your C:\Program Files\Sisense\app\plugins\ folder. If the plugins folder doesn't exist, just create it. After those files have been unzipped there, you may also have to restart the web server. Now when you create or edit pivot or indicator widgets, you should have additional setting options
Step 2 - Create the Chart
On your dashboard, edit or create a new pivot table or indicator and click on the settings menu at the top right of the screen. You should see some new options under Financial Formatting that allow you to decide on if you want negative values to be formatted this way and if you want to replace empty cells with zeros.
References/Notes
- This plugin was developed for just pivot tables and indicator widgets
- The negative formatting is set to only be applied to percentages and currency values, regular numbers will be ignored by this plugin
-
Hi Ganesh,
In order to affect the grand totals only, a small adjustment needs to be made. Open the negativesFormatter.js file and find the pivotFormatter function. Replace that function definition with the code below, and it should work.
// Function to format pivot tables
function pivotFormatter(widget,event){
// Get the widget element
var widgetElement = (prism.$ngscope.appstate == 'dashboard') ? $('widget[widgetid="' + widget.oid + '"]') : $('pivot');
// Make sure there's at least some default setttings
if (typeof widget.options[labelProperty] === "undefined") {
widget.options[labelProperty] = $.extend({},options);
}
// loop through the pivot table's value metadata items
var valueItems = widget.metadata.panel("values").items;
$.each(valueItems,function(){
// Get the fidx
var fidx = this.field.index;
// Is this value formatted as a currency or percent?
var isCurrency = (this.format && this.format.mask && this.format.mask.currency),
isPercent = (this.format && this.format.mask && this.format.mask.percent),
gtRow = $('td.p-grand-total-row-head',widgetElement).parent();
// Run the code to show zeros instead of blank cells
if (widget.options[labelProperty]["Show Zeros"]){
// Find all the table cells, that have a null value
/*
var cells = $('td[fidx=' + fidx + ']:has(.p-value)',widgetElement).filter(function(){
return $(this).attr('val') === "N\\A" ;
})
*/
var cells = $('td[fidx=' + fidx + ']:has(.p-value)',gtRow).filter(function(){
return $(this).attr('val') === "N\\A" ;
})
// Determine the new text to include
var newText = "0";
if (isCurrency) {
newText = "$0";
} else if (isPercent) {
newText = "0%";
}
// Write in a zero for these cells
cells.text(newText);
}
// Run the code to format data values
if (widget.options[labelProperty]["Format Negatives"]){
// Only applies to currency and percentage formatting
if (isCurrency || isPercent){
// Find all the table cells, that have a value less than 0
/*
var cells = $('td[fidx=' + fidx + ']:has(.p-value)',widgetElement).filter(function(){
return $(this).attr('val') < 0;
})
*/
var cells = $('td[fidx=' + fidx + ']:has(.p-value)',gtRow).filter(function(){
return $(this).attr('val') < 0;
})
// Get a series type
var sType = isCurrency ? 'currency' : 'percent';
// Loop through each cell and run the formatter
$.each(cells, function(){
valueFormatter(this, sType, $(this).text());
})
}
}
})
}
Please sign in to leave a comment.
Comments
11 comments