cancel
Showing results for 
Search instead for 
Did you mean: 

Force Pivot Table Rows/Columns to Show When No Data Present

sneak
9 - Travel Pro
9 - Travel Pro

I'm working on a custom pivot table implementation that has rules for coloring to recreate the concept of a risk matrix. Here's the code:

 

widget.on('domready', function(sender, ev) {
var widgetId = 'widget[widgetid*=' + sender.oid + ']';

//console.log('START')
	
var i = 0
$('div.table-grid__content', widgetId).parent().parent().parent().css('border', 'black solid 1.5px')
$('div.table-grid__content', widgetId).parent().css('border', 'black solid 1.5px')
$('div.table-grid__content', widgetId).parent().css('text-align', 'center')

$('tr', widgetId).each(function() {

if ($(this).index() == 7) {
$(this).hide()
}
else {
	$(this).css("display", "")
}

if ($(this).index() > 1) {
$(this).find('td', widgetId).each (function() {


if ($(this).index() == 1) {
$(this).css('background-color', 'green')
$(this).css('color', 'white')
}

if ($(this).index() == 2) {
if ($(this).parent().index() > 4) {
$(this).css('background-color', 'green')
$(this).css('color', 'white')
}
else {
$(this).css('background-color', 'yellow')
$(this).css('color', 'black')
}
}

if ($(this).index() == 3) {
if ($(this).parent().index() < 3) {
$(this).css('background-color', 'red')
$(this).css('color', 'black')
}
else if ($(this).parent().index() < 6) {
$(this).css('background-color', 'yellow')
$(this).css('color', 'black')
}
else {
$(this).css('background-color', 'green')
$(this).css('color', 'white')
}
}

if ($(this).index() == 4) {
if ($(this).parent().index() < 4) {
$(this).css('background-color', 'red')
$(this).css('color', 'black')
}
else if ($(this).parent().index() < 6) {
$(this).css('background-color', 'yellow')
$(this).css('color', 'black')
}
else {
$(this).css('background-color', 'green')
$(this).css('color', 'white')
$(this).css('fontSize', '24')
}
}


if ($(this).index() == 5) {
if ($(this).parent().index() < 5) {
$(this).css('background-color', 'red')
$(this).css('color', 'black')
}
else if ($(this).parent().index() < 7) {
$(this).css('background-color', 'yellow')
$(this).css('color', 'black')
}
else {
$(this).css('background-color', 'green')
$(this).css('color', 'white')
}
}
if ($(this).parent().index() >= 7) {
$(this).css('background-color', 'white')
$(this).css('color', 'black')
}

})  
}
});

//console.log('FINISH')

})

 

The above code works and produces the table as desired as seen here: 

sneak_0-1674151062009.png

However, when an applied filter causes some rows/columns to not have any data present, they are hidden, which messes up the whole matrix. I need it to remain as a 5x5 matrix, regardless of what records are filtered out. 

Here's what it looks like when some records are filtered out (notice that Consequence = 4 and Likelihood = 2 are now missing from the table).

sneak_1-1674151163706.png

Here is what the data panel looks like for the pivot table:

sneak_2-1674151428739.png

Any ideas on some javascript / jQuery that can override the default Pivot behavior and ensure the Rows/Columns stay visible even no applicable data fits in those cells?

Thanks in advance for your help. Hopefully this could be re-used and improved for others as well, to create Risk Matrix widgets.

1 REPLY 1

sneak
9 - Travel Pro
9 - Travel Pro

I considered creating fake records that have null values for either Likelihood or Consequence, then force-hiding those N/A rows/columns from the pivot table. I had mild success with this method for preserving the 5x5 matrix, but it is messy and not a robust long-term solution. Was hoping someone with more JS experience than me might think of a straightforward, if not simple, solution.