cancel
Showing results for 
Search instead for 
Did you mean: 

Highlight 1st column in Pivot using values from another column

DustinSmith
7 - Data Storage
7 - Data Storage

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:

stoplight_ex.png

What I'd like:

stoplight_ex2.png

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

Thanks

1 ACCEPTED SOLUTION

harikm007
13 - Data Warehouse
13 - Data Warehouse

@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

View solution in original post

2 REPLIES 2

harikm007
13 - Data Warehouse
13 - Data Warehouse

@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
7 - Data Storage
7 - Data Storage

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

stoplight_ex5.png

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:

stoplight_ex6.png

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