Change Cell Color Based on Range

[dojiepreji]

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?

[vnravikumar · Accepted solution]

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

[vnravikumar]

@dojiepreji Whether it works?

[damonmanni]

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?

[vnravikumar]

Hi

Are you getting any js error in the background

[dojiepreji]

It worked, thank you.
What if I want the Percentage column to be colored instead?

[niketn]

@dojiepreji refer to the following answer by @kamlesh_vaghela to add table cell color of one field depending on other field value:

https://answers.splunk.com/answers/661894/how-to-color-cell-contents-with-css-and-js.html#answer-661...

[vnravikumar]

Hi @dojiepreji

Please check this solution

https://answers.splunk.com/answers/661894/how-to-color-cell-contents-with-css-and-js.html#answer-661...