How To Create Icon Indicators In a Pivot Table
Introduction
The following article describes the steps needed to create icon indicators in a pivot table.
For example - Red icon for negative values and green for positive.
Implementation Steps
-
Create a pivot chart with at least 1 value
-
Create the “Images” directory under C:\Program Files\Sisense\PrismWeb\Images and place the green.png & red.png images in it. (example icons attached below)
-
Edit the widget’s script, and paste the bellow JavaScript code
-
In the script, define the variable ColumnNumber with the column you want to convert to an icon
-
(Optional) - Default configuration is set for 0 as the barrier between green & red indicator. In the script, change the variable MyIconValue to the new value. (need to change in two places: MyIconValue < 0 and MyIconValue > 0)
- Save the script, refresh the widget and click apply
Java Script Code:
widget.on('ready', function(sender, ev){
console.log('starting custom code');
/**********************************************
*** Add icons for positive/negative ***
*** values ***
**********************************************/
//define path to images
var RedArrow = '/Images/arrows/red.png';
var GreenArrow = '/Images/arrows/green.png';
//pick a column to replace with icons
var ColumnNumber = 4;
//get array of elements to replace w/ icons
var MyIcons = $('td.p-value[fidx=' + ColumnNumber + ']', element);
var L = MyIcons.length;
console.log(MyIcons);
var MyIcon;
var MyIconValue;
//loop through each bar
for (i=0; i<L; i++)
{
//look at the specific icon
MyIcon = MyIcons[i];
//debugger
//get the numerical value for this cell
MyIconValue = MyIcon.innerHTML
MyIconValue = MyIcon.innerHTML.replace(/[^\d.-]/g, '');
MyIconValue = MyIconValue.slice(1);
//is value positive or negative
if (MyIconValue < 0)
{
console.log('below 0')
//replace w/ red arrow
MyIcon.innerHTML = '<div class="p-value"><img src="'+RedArrow+'" alt="Arrow" max-width="18px" max-height="18px" style="display:block; margin-left:auto; margin-right:auto;" /></div>';
}
else if (MyIconValue >= 0)
{
//replace w/ green arrow
MyIcon.innerHTML = '<div class="p-value"><img src="'+GreenArrow+'" alt="Arrow" max-width="18px" max-height="18px" style="display:block; margin-left:auto; margin-right:auto;" /></div>';
}
else
{
//replace w/ empty space
//MyIcon.innerHTML = '';
}
}
console.log('custom code finished.');
})
-
Thanks Alon! Is it possible to apply these icons if the pivot has a 'Column' field? For instance I have a pivot table which shows a value across a range of dates. I'd like to have an icon for each date:
Pivot Example.png -
You can set an icon or string value to every cell in a similar fashion. We wanted to show employee schedule information in a pivot and did it using the code below. The strings can easily be replaced with the icon paths
widget.on('ready', function(){
$('td[class*=p-value] div').map(function(i, cell) {
cell.innerText.replace(/[^\d.-]/g, '')
if (cell.innerHTML == 0)
{
cell.innerHTML = 'Off';
}
else if (cell.innerHTML == 1)
{
cell.innerHTML = 'Office Work';
}
else if (cell.innerHTML == 2)
{
cell.innerHTML = 'Traveling';
}
else if (cell.innerHTML == 3)
{
cell.innerHTML = 'Half Day';
}
else
{
//replace w/ empty space
cell.innerHTML= '';
}
})
}) -
Thanks Joseph, this is absolutely great !!
Please note, that your script will convert all pivots in the dashboard. I tweaked it a bit to affect only the specific pivot and to work on big ints.
In the example below i defined it to have a green icon on months with revenue above 10,000 and red icon for revenues lower than 10,000.
And this is the script (of course you need to download the icons folder from the above):
widget.on('ready', function(){
$('td[class*=p-value] div', element).map(function(i, cell) {
cell.innerText.replace(/[^\d.-]/g, '')
debugger
if (parseFloat(cell.innerHTML.removeCommas()) > 10000)
{
cell.innerHTML = '<img src=/Images/arrows/green.png />';
}
else if (parseFloat(cell.innerHTML.removeCommas()) < 10000)
{
cell.innerHTML = '<img src=/Images/arrows/red.png />';
}
else
{
//replace w/ empty space
cell.innerHTML= '';
}
})
})
IconIndicatorOnAllPivot.dash -
It seems that in version 5.7.5 the icon images are stretching with the column width. See attached image.
icon stretch.png -
Thanks Joseph, i adusjted the script file,
Re-attaching the script to define 3 colors icon indicator (green / red / yellow):
PivotChart-RedGreenYellowIcons.txt -
Hi Joseph,
As Ramon recommended, try replacing the innerText with textContent.
For V5.8 and up, please use the following script:
widget.on('ready', function(sender, ev){ console.log('starting custom code'); /********************************************** *** Add icons for positive/negative *** *** values *** **********************************************/ //define path to images var RedArrow = '/Images/arrows/red.png'; var GreenArrow = '/Images/arrows/green.png'; //pick a column to replace with icons var ColumnNumber = 2; //get array of elements to replace w/ icons var MyIcons = $('td.p-value[fidx=' + ColumnNumber + ']', element); var L = MyIcons.length; //console.log(MyIcons); var MyIcon; var MyIconValue; //loop through each bar for (i=0; i<L; i++) { //look at the specific icon MyIcon = MyIcons[i]; //get the numerical value for this cell MyIconValue = MyIcon.textContent.replace(/[^\d.-]/g, ''); //console.log(MyIconValue); //is value positive or negative if (MyIconValue < 0) { //replace w/ red arrow MyIcon.innerHTML = ''; } else if (MyIconValue > 0) { //replace w/ green arrow MyIcon.innerHTML = ''; } else { //replace w/ empty space MyIcon.innerHTML = ''; } } console.log('custom code finished.'); }) -
Hi Alon,
I have import dashboard which you have provide also keep images in place which you have shown and apply javascript which you have provided, I have found this error given below.
error initializing dashboard extension - ReferenceError: widget is not defined
base.js?g=5e6f1c9e8ef70e01320214d055febbc1:1
Thanks
Rajneesh Sharma.
-
Can someone tell me if this is supposed to work in V6.4 beta? Currently the following script is not working anymore and previously was:
widget.on('ready', function(){
$('td[class*=p-value] div', element).map(function(i, cell) {
cell.innerText.replace(/[^\d.-]/g, '')
debugger
if ((parseFloat(cell.innerHTML.removeCommas()) > 0) & (parseFloat(cell.innerHTML.removeCommas()) < 2))
{
cell.innerHTML = '<img src=/Images/arrows/ritchie_green.png />';
}
else if (parseFloat(cell.innerHTML.removeCommas()) < 1)
{
cell.innerHTML = '<img src=/Images/arrows/ritchie_red.png />';
}
else if (parseFloat(cell.innerHTML.removeCommas()) > 1){
cell.innerHTML = '<img src=/Images/arrows/ritchie_na.png />';
}
else
{
//replace w/ empty space
cell.innerHTML= '';
}})
})
-
Worked with Max from Support to get this to work for 6.4 Loop (see attachment)
6.4Loop.PNG -
Hi,
I've added the following script, and the desired behavior for the widget isn't reflected in the dashboard.
When running directly on the console, I do see the expected result,
widget.on('ready', function(sender, ev){
/**********************************************
*** Add icons for positive/negative ***
*** values ***
**********************************************/
$ = jQuery;
$('#grid tr td:nth-child(4) ').each( function(){
if( $(this).html() === "1" ) {
$(this).html( '<img src="/Images/red.png" alt="Arrow" width="18px" height="18px" style="display:block; margin-left:auto; margin-right:auto;" />');
console.log("red");
} else {
$(this).html( '<img src="/Images/green.png" alt="Arrow" width="18px" height="18px" style="display:block; margin-left:auto; margin-right:auto;" />');
console.log("green");
}
});
})
Would be happy for your insights, please advise,
Thanks,
Adi.
Please sign in to leave a comment.
Comments
22 comments