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(...
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

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);