cancel
Showing results for 
Search instead for 
Did you mean: 
intapiuser
Community Team Member
Community Team Member

Introduction

This article describes how to dynamically color the Data Bars within a pivot table based on the cell values.
 Presented here are two methods:
  1. Use stock gradients to provide different colors based on cell values
  2. Define RGB shade magnitudes based on cell values

Examples

Method 1
method_1_formatting.png
Method 2
Method_2_Formatting.png

Implementation Steps

Method 1

Utilizing D3.JS Sequential and Diverging Color Scales

This method uses D3 version 5's interpolate methods to select a color based on the bar width.
The "d3.interpolate<color code>" method accepts a float value between 0 and 1 and maps the number to a color within a selected gradient.
number_line.png
A complete list of gradients can be found here under Diverging and Sequential:
Steps
  1. Navigate to Advanced Widget View
  2. Copy the snippet below into the Widget custom JavaScript editor to see the results above.
  3. Save the code
  4. Refresh the Widget Advanced Editor Page
  5. Apply Changes
Editing Lightness and Darkness of the Gradient
In this snippet example the range highlighted in green refers to the entire range of the gradient, as 0 represents the start of the gradient, and 1 the end. However often times, you will not want the minimum value represented by 0, because it is too light. The solution is to replace 0 with a slightly higher value, such as 0.2, until you are satisfied with the shade.
Editing Color of the Gradient
In this snippet example the method highlighted in yellow controls the color. To change which color gradient is used, navigate Here and select a d3.interpolate method of a color range you like. Replace the gradient for positive values, and negative values.
If you choose a diverging gradient method, be mindful that you will also need to create two scales, one from 0-.5, and .5 to 1 for negative and positive values respectively
Paste This Snippet into Widget JavaScript Editor 
widget.on('ready', function(sender, ev) {
console.log('starting custom code');
// Update D3 to version 5 to allow use of interpolation
$.getScript('https://d3js.org/d3.v5.min.js', function(data, textStatus) {
return true
});
// Convert percentage range to 0 to 1 or any range in between.
myScale = d3.scaleLinear().domain([0, 100]).range([0, 1]);
/**********************************************

Color bars red or green for
positive/negative values.
**********************************************/
//get an array of bar elements
var MyBars = $('.bar', element);
//find how many bars are visible
var L = MyBars.length;
//init variables
var MyBarSign;
var MyBar;
//loop through each bar
for (i = 0; i < L; i++) {
//gets bar width and converts number to a float value
var PercentStringWidth = MyBars[i].style.width;
var IntegerStringWidth = PercentStringWidth.slice(0, -1);
var FloatWidth = parseFloat(IntegerStringWidth);
//apply scale function to the bar width
var ScaledWidth = myScale(FloatWidth);
//look at a specific bar
MyBar = MyBars[i];
//get the class name
MyBarSign = MyBar.parentNode.className.substring(0, 3);
//if the class name starts w/ 'pos', color it using the green gradient. Otherwise color it using the red gradient.
if (MyBarSign == 'pos') {
MyBar.style.backgroundColor = d3.interpolateYlGn(ScaledWidth);
} else {
MyBar.style.backgroundColor = d3.interpolateYlOrRd(ScaledWidth);
}
}
});
Method_1_Code.PNG

Method 2

Creating Self-Defined RGB Color Formulas

This method takes advantage of D3.JS's scaleLinear domain and range functions. The method converts a percentage to a scale that the RGB color values are based on - 0 to 255. For example, 50% is converted to 127.5, or half brightness of a red, green, or blue value. The background color is calculated by converting the width value to a RGB value.
Steps
  1. Navigate to Advanced Widget View
  2. Copy the snippet below into the Widget custom JavaScript editor to see the results above.
  3. Save the code
  4. Refresh the Widget Advanced Editor Page
  5. Apply Changes
Editing Lightness and Darkness and Color of the Gradient
In this snippet we are using the RGB values 0 - 255. High values represent lighter tones and lower values darker tones. You can define a function for how the color changes depending on how the width changes, as seen in the yellow highighted text. When using division remember to wrap the function with ParseInt as RGB values cannot be floats.
To select RGB colors you can Google "RGBcolor picker" and use the color picker widget, or use this W3schools page.
Paste This Snippet into Widget JavaScript Editor
widget.on('ready', function(sender, ev) {
console.log('starting custom code');
myScale = d3.scale.linear().domain([0, 100]).range([0, 255]);
/**********************************************

Color bars red or green for
positive/negative values.
**********************************************/
//get an array of bar elements
var MyBars = $('.bar', element);
//find how many bars are visible
var L = MyBars.length;
//init variables
var MyBarSign;
var MyBar;
//console.log(MyBars);
//loop through each bar
for (i = 0; i < L; i++) {
//gets bar width
var PercentStringWidth = MyBars[i].style.width;
var IntegerStringWidth = PercentStringWidth.slice(0, -1);
var FloatWidth = parseFloat(IntegerStringWidth);
//Convert to a 0 - 255 scale
var ScaledWidth = parseInt(myScale(FloatWidth));
//look at a specific bar
MyBar = MyBars[i];
//get the class name
MyBarSign = MyBar.parentNode.className.substring(0, 3);
//if the class name starts w/ 'pos', color it green. Otherwise color it red
if (MyBarSign == 'pos') {
MyBar.style.backgroundColor = 'rgb(' + parseInt(ScaledWidth / 3) + ',' + ScaledWidth + ', ' + 200 + ')';
} else {
MyBar.style.backgroundColor = 'rgb(' + ScaledWidth + ',' + parseInt(ScaledWidth / 3) + ', ' + 200 + ')';
}
}
});
method_2_code.PNG
Comments
vaibhav_j
9 - Travel Pro
9 - Travel Pro

@Community_Admin Under Admin > System Management > Configuration > Pivot administration settings, the only option I see is "Allow rendering Pivot Table content as HTML". I do not see the option to enable "Allow Pivot data bars feature". How do enable data bars in pivot? 

Ophir_Buchman
Sisense Team Member
Sisense Team Member

Hi @vaibhav_j,

 

The "Data Bars" feature is an out-of-the-box feature and doesn't require special enablement.

Please visit this page and navigate to the 3rd bullet under "Adding Data to a Pivot Table":

 

https://documentation.sisense.com/docs/pivot

 

Ophir

Version history
Last update:
‎03-02-2023 08:42 AM
Updated by:
Contributors
Community Toolbox

Recommended quick links to assist you in optimizing your community experience:

Developers Group:

Product Feedback Forum:

Need additional support?:

Submit a Support Request

The Legal Stuff

Have a question about the Sisense Community?

Email [email protected]

Share this page: