How To Create Icon Indicators In a Pivot Table

Comments

22 comments

  • Avatar
    Joseph Peters

    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
    0
    Comment actions Permalink
  • Avatar
    Alon Ahronberg

    Hi Joseph, that is a good idea. It will require to change the JS code.

    I will try to post a forum post for this scenario as well.

    0
    Comment actions Permalink
  • Avatar
    Joseph Peters

    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= '';
    }
    })
    })

    0
    Comment actions Permalink
  • Avatar
    Ido Darnell

    Awesome Joseph! thanks for that!

    0
    Comment actions Permalink
  • Avatar
    Alon Ahronberg

    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
    0
    Comment actions Permalink
  • Avatar
    Joseph Peters

    It seems that in version 5.7.5 the icon images are stretching with the column width.  See attached image.




    icon stretch.png
    0
    Comment actions Permalink
  • Avatar
    Alon Ahronberg

    Thanks Joseph, i adusjted the script file, 

    Re-attaching the script to define 3 colors icon indicator (green / red / yellow):




    PivotChart-RedGreenYellowIcons.txt
    0
    Comment actions Permalink
  • Avatar
    Ramon

    Please note that some browsers do not support innerText. Use textContent instead.

    0
    Comment actions Permalink
  • Avatar
    Joseph Peters

    Since the latest update I can't get my icons to appear in the pivot, it just shows a blank cell.  Anyone else seeing this issue?

    0
    Comment actions Permalink
  • Avatar
    Ido Darnell

    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 = '
    Arrow
    ';
    		}
    		else if (MyIconValue > 0)
    		{
    			//replace w/ green arrow
    			MyIcon.innerHTML = '
    Arrow
    ';
    		}
    		else
    		{
    			//replace w/ empty space
    			MyIcon.innerHTML = '';
    		}
    		
    	}
    	console.log('custom code finished.');
    })
    0
    Comment actions Permalink
  • Avatar
    Joseph Peters

    That fixed it, thanks Ido and Ramon!

    0
    Comment actions Permalink
  • Avatar
    sumeet vyas

    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.

    0
    Comment actions Permalink
  • Avatar
    Tripti

    Hi Ido,

     

    For one of my dashboard I  applied Java Script which you have provided.Instead of colored icon i am getting the the Icon name like : GrrenArrow.

     

    Thanks

    Tripti

     

     

    0
    Comment actions Permalink
  • Avatar
    Ido Darnell

    Hi Tripti,

     

    You will need to place an image with the respective name in the proper folder for the image to render.

    If you run into any further issues it would be best to submit a ticket

     

    Ido

     

    0
    Comment actions Permalink
  • Avatar
    Andrew Block

    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= '';
    }

    })

    })

    0
    Comment actions Permalink
  • Avatar
    Andrew Block

    Worked with Max from Support to get this to work for 6.4 Loop (see attachment)

     




    6.4Loop.PNG
    1
    Comment actions Permalink
  • Avatar
    Hugo Cabello

    Hi, we are on High Availability and the image is not being displayed on the widget (see below). What is the image path that we are suppose to put ? Currently it is set to 

    var RedArrow = '/Images/arrows/red.png';
    var GreenArrow = '/Images/arrows/green.png';

    0
    Comment actions Permalink
  • Avatar
    Yoni Lerner

    Hi Hugo,

     

    You will need to enter this path and icon for each of your query nodes

    0
    Comment actions Permalink
  • Avatar
    Adi Weisberg

    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.

    0
    Comment actions Permalink
  • Avatar
    Doug Thompson

    What do you do when C:\Program Files\Sisense\PrismWeb\ does not exist? eg in V7.3

    0
    Comment actions Permalink
  • Avatar
    Michael Becker

    Go to Sisense\app folder. That's were those things reside in 7.3.

    0
    Comment actions Permalink
  • Avatar
    Doug Thompson

    as in C:\Program Files\Sisense\app\resources\Images\ ?

    0
    Comment actions Permalink

Please sign in to leave a comment.