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

niketn
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!

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...