Forum Discussion

DustinSmith's avatar
DustinSmith
Data Storage
03-29-2022
Solved

Highlight 1st column in Pivot using values from another column

Hello,

I'm using Sisense Linux and I have a pivot table that I'm using to stoplight certain locations with green, yellow, or red.  My 1st column is location(like NYC), my 2nd column is text status(GREEN), and my 3rd column is number status(3).  I would like to use either the 2nd or 3rd column to determine what color to highlight the 1st column.

What I have:

What I'd like:

I know pivots are highly customizable in Linux via widget scripts, but I haven't been able to get it working.

Thanks

Dashboards & Reporting
  • harikm007's avatar
    harikm007
    03-31-2022

    DustinSmith 

    Please check if this script works for you:

    widget.on('domready', function(se, ev){
	
	ColorValueColumnIndex = '2' //Column index based on which color to choose
	ApplycolorToColumnIndex = '0' //Index of the column in which the color should be applied
	
	if (prism.activeWidget == null)
		parentElement = $('[widgetid="' + widget.oid + '"] pivot2');
	else
		parentElement = $('pivot2')

	$(parentElement).find('.pivot-scroller .pivot-scroller__view .table-grid__table tbody tr').each(function(index){
		
		if(index == 0)
			return
			
		colorValue = $(this).find(`.table-grid__cell--col-${ColorValueColumnIndex}`).text().trim()

		if(colorValue == '4')
			color = '#d0f7da'
		else if(colorValue == '12')
			color = '#d0e4f7'
		else if(colorValue == '14')
			color = '#f7d0f3'
		else if(colorValue == '6')
			color = '#f7d0d6'
		else if(colorValue == '8')
			color = '#f7efd0'
		else
			color = '#FFFFFF'

		$(parentElement).find(`.multi-grid .table-grid table tbody tr.table-grid__row-${index} .table-grid__cell--col-${ApplycolorToColumnIndex}`).css('background-color', color)
	}) 
})

    -Hari

2 Replies

Replies have been turned off for this discussion
  • harikm007's avatar
    harikm007
    Data Warehouse
    03-31-2022

    DustinSmith 

    Please check if this script works for you:

    widget.on('domready', function(se, ev){
	
	ColorValueColumnIndex = '2' //Column index based on which color to choose
	ApplycolorToColumnIndex = '0' //Index of the column in which the color should be applied
	
	if (prism.activeWidget == null)
		parentElement = $('[widgetid="' + widget.oid + '"] pivot2');
	else
		parentElement = $('pivot2')

	$(parentElement).find('.pivot-scroller .pivot-scroller__view .table-grid__table tbody tr').each(function(index){
		
		if(index == 0)
			return
			
		colorValue = $(this).find(`.table-grid__cell--col-${ColorValueColumnIndex}`).text().trim()

		if(colorValue == '4')
			color = '#d0f7da'
		else if(colorValue == '12')
			color = '#d0e4f7'
		else if(colorValue == '14')
			color = '#f7d0f3'
		else if(colorValue == '6')
			color = '#f7d0d6'
		else if(colorValue == '8')
			color = '#f7efd0'
		else
			color = '#FFFFFF'

		$(parentElement).find(`.multi-grid .table-grid table tbody tr.table-grid__row-${index} .table-grid__cell--col-${ApplycolorToColumnIndex}`).css('background-color', color)
	}) 
})

    -Hari

  • DustinSmith's avatar
    DustinSmith
    Data Storage
    03-31-2022

    Excellent! Thank you so much! This is what it looks like now without any manual intervention:

    I was working with Anton from support and we got close.  That solution was highlighting all columns except the first one.  I'll post that script below(while obscuring the specific locations) in case anyone has a similar use case:

    Script:

    const target = {
    rows: [
    {
    dim: "[LocationStoplight.status]",
    members: ["GREEN"],
    },
    ],
    };

    function setCellBackground(metadata, cell) {
    cell.style = cell.style || {};
    cell.style.backgroundColor = 'green';
    }

    widget.transformPivot(target, setCellBackground);


    const target2 = {
    rows: [
    {
    dim: "[LocationStoplight.status]",
    members: ["RED"],
    },
    ],
    };
    function setCellBackground2(metadata, cell) {
    cell.style = cell.style || {};
    cell.style.backgroundColor = 'red';
    }

    widget.transformPivot(target2, setCellBackground2);

    const target3 = {
    rows: [
    {
    dim: "[LocationStoplight.status]",
    members: ["YELLOW"],
    },
    ],
    };
    function setCellBackground3(metadata, cell) {
    cell.style = cell.style || {};
    cell.style.backgroundColor = 'yellow';
    }

    widget.transformPivot(target3, setCellBackground3);