Force Pivot Table Rows/Columns to Show When No Data Present
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:
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).
Here is what the data panel looks like for the pivot table:
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.