I have a table that lists some percentages.
Priority | Minimum | Percentage
1 95.00% 95.23%
2 98.00% 97.23%
I want to color the cells in the Percentage column based on the values from Minimum.
For example, in Priority 1, I need to color the Percentage cell with green because it has exceeded minimum baseline.
In Priority 2, I need to color the cell grey because it did not meet minimum percentage.
I've seen people do something like this,
<format type="color" field="Percentage">
<colorPalette type="expression">case(value == "No Data", "#898989", value > 90.00, "#36D734")</colorPalette>
</format>
However, due to that each Priority has a different minimum percentage, I need to set rules per Priority.
How should I achieve this?
Hi @dojiepreji
Here is the sample code
table_cell_color_demo.xml
<dashboard script="table.js" stylesheet="table.css">
<label>Table Cell Color Demo</label>
<row>
<panel>
<table id="custom_table">
<search>
<query>| makeresults
| eval Priority=1, Minumum="95.00%", Percentage = "95.23%"
| append
[| makeresults
| eval Priority=2, Minumum="98.00% ", Percentage = "97.23%"]
| eval min = Minumum, per = Percentage
| convert rmunit(min), rmunit(per)
| eval result = if(min<per,"high","low") | eval Priority = Priority."|".result
| table Priority, Minumum, Percentage, min, per,result</query>
<earliest>-24h@h</earliest>
<latest>now</latest>
</search>
<option name="count">5</option>
<option name="drilldown">none</option>
<option name="refresh.display">progressbar</option>
<fields>Priority, Minumum, Percentage</fields>
</table>
</panel>
</row>
</dashboard>
table.js
require([
'underscore',
'jquery',
'splunkjs/mvc',
'splunkjs/mvc/tableview',
'splunkjs/mvc/simplexml/ready!'
], function(_, $, mvc, TableView) {
var CustomRangeRenderer = TableView.BaseCellRenderer.extend({
canRender: function(cell) {
return _(['Priority','Minumum','Percentage']).contains(cell.field);
},
render: function($td, cell) {
var label = cell.value.split("|")[0];
var val = cell.value.split("|")[1];
if(val=="high" || val=="low" )
{
$td.html("<div class='css_for_"+label+"_"+val+"'>"+label+"</div>")
}
else
{
$td.html("<div class='align_center'>"+label+"</div>")
}
}
});
//List of table IDs to add icon
var tableIDs = ["custom_table"];
for (i=0;i<tableIDs.length;i++) {
var sh = mvc.Components.get(tableIDs[i]);
if(typeof(sh)!="undefined") {
sh.getVisualization(function(tableView) {
// Add custom cell renderer and force re-render
tableView.table.addCellRenderer(new CustomRangeRenderer());
tableView.table.render();
});
}
}
});
table.css
/*Define color as per your requirement*/
/*Priority 1 Minumum < Percentage */
.css_for_1_high {
background-color:#008000;
}
/*Priority 1 Minumum > Percentage */
.css_for_1_low {
background-color:#004d4d;
}
/*Priority 2 Minumum < Percentage */
.css_for_2_high {
background-color: #00e6e6;
}
/*Priority 2 Minumum > Percentage */
.css_for_2_low {
background-color:#A8A8A8;
}
Sample Screen
Hi @dojiepreji
Here is the sample code
table_cell_color_demo.xml
<dashboard script="table.js" stylesheet="table.css">
<label>Table Cell Color Demo</label>
<row>
<panel>
<table id="custom_table">
<search>
<query>| makeresults
| eval Priority=1, Minumum="95.00%", Percentage = "95.23%"
| append
[| makeresults
| eval Priority=2, Minumum="98.00% ", Percentage = "97.23%"]
| eval min = Minumum, per = Percentage
| convert rmunit(min), rmunit(per)
| eval result = if(min<per,"high","low") | eval Priority = Priority."|".result
| table Priority, Minumum, Percentage, min, per,result</query>
<earliest>-24h@h</earliest>
<latest>now</latest>
</search>
<option name="count">5</option>
<option name="drilldown">none</option>
<option name="refresh.display">progressbar</option>
<fields>Priority, Minumum, Percentage</fields>
</table>
</panel>
</row>
</dashboard>
table.js
require([
'underscore',
'jquery',
'splunkjs/mvc',
'splunkjs/mvc/tableview',
'splunkjs/mvc/simplexml/ready!'
], function(_, $, mvc, TableView) {
var CustomRangeRenderer = TableView.BaseCellRenderer.extend({
canRender: function(cell) {
return _(['Priority','Minumum','Percentage']).contains(cell.field);
},
render: function($td, cell) {
var label = cell.value.split("|")[0];
var val = cell.value.split("|")[1];
if(val=="high" || val=="low" )
{
$td.html("<div class='css_for_"+label+"_"+val+"'>"+label+"</div>")
}
else
{
$td.html("<div class='align_center'>"+label+"</div>")
}
}
});
//List of table IDs to add icon
var tableIDs = ["custom_table"];
for (i=0;i<tableIDs.length;i++) {
var sh = mvc.Components.get(tableIDs[i]);
if(typeof(sh)!="undefined") {
sh.getVisualization(function(tableView) {
// Add custom cell renderer and force re-render
tableView.table.addCellRenderer(new CustomRangeRenderer());
tableView.table.render();
});
}
}
});
table.css
/*Define color as per your requirement*/
/*Priority 1 Minumum < Percentage */
.css_for_1_high {
background-color:#008000;
}
/*Priority 1 Minumum > Percentage */
.css_for_1_low {
background-color:#004d4d;
}
/*Priority 2 Minumum < Percentage */
.css_for_2_high {
background-color: #00e6e6;
}
/*Priority 2 Minumum > Percentage */
.css_for_2_low {
background-color:#A8A8A8;
}
Sample Screen
@dojiepreji Whether it works?
Hi guys,
I am struggling to get this solution to work. I copy&pasted the code above exactly as is but cannot get any colors to show in the table. Here is the order I followed, is there something out of step I am missing?
1) Placed xml in /opt/splunk/etc/apps/search/local/data/ui/views/table_cell_color_demo.xml
2) Placed jss and css in:
/opt/splunk/etc/apps/search/appserver/static/table.js
/opt/splunk/etc/apps/search/appserver/static/table.css
3) splunk restart splunkweb
4) refreshed dashboard
* table displays but no color
What am i doing wrong?
Hi
Are you getting any js error in the background
It worked, thank you.
What if I want the Percentage column to be colored instead?
@dojiepreji refer to the following answer by @kamlesh_vaghela to add table cell color of one field depending on other field value:
Hi @dojiepreji
Please check this solution