Dashboards & Visualizations

Change Cell Color Based on Range

dojiepreji
Path Finder

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?

0 Karma
1 Solution

vnravikumar
Champion

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

table

View solution in original post

0 Karma

vnravikumar
Champion

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

table

0 Karma

vnravikumar
Champion

@dojiepreji Whether it works?

0 Karma

damonmanni
Path Finder

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?

0 Karma

vnravikumar
Champion

Hi

Are you getting any js error in the background

0 Karma

dojiepreji
Path Finder

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

0 Karma

niketnilay
Legend

@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...

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

vnravikumar
Champion
0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...