Style Pivot Column Base On Another Column

[intapiuser]

Introduction

The following article will explain how to style a column in a pivot widget based on another column values, using custom JavaScript.

Purpose/Benefits

This script allows you to style a column in a pivot widget base on another column values and hide this column.

Example

In the Example below we want to set "Revenue" to Red, if "# of unique Brand ID" is greater than 30 and hide "# of unique Brand ID" column.

Result:

Steps

Use the JavaScript below to style the column.

/********************************************************/
/********Style pivot column based on another pivot column**********/
/********************************************************/
widget.on("ready", function(widget, args) {
  
   var CUR_COL_FIND_CRITERIA = "[fidx=2]";
   var FORMAT_COL_FIND_CRITERIA = "[fidx=1]";
  
   //format a cell according to a condition on the current cell
   function formatCell(){
    if (30 > $(this).attr('val')){
     $(this).siblings(FORMAT_COL_FIND_CRITERIA).css("color", "red");
    }
   }

   //get all column cells and format other cells
   var cells = $('[id="' + widget.oid+ '"]').find('td' + CUR_COL_FIND_CRITERIA).each(formatCell);

   //hide current column and set new width for the pivots
   $('[id="' + widget.oid+ '"]').find(CUR_COL_FIND_CRITERIA).html("");
   $('[id="' + widget.oid+ '"]').find(CUR_COL_FIND_CRITERIA).css("width", "0px");
 });

Set CUR_COL_FIND_CRITERIA to [fidx=XXX]﹡of the column you would like to base your criteria on.

Set FORMAT_COL_FIND_CRITERIA to the [fidx=XXX]﹡of the column you would like to style.

Change the criteria in formatCell function to the one you would like. The one in the example checks if the value is greater then 30.

Change the style in formatCell function. In the example above we set the color to red.

﹡fidx is the number of column, starting from 0. In the example above fidx for "Category" column is '0', for "Revenue" '1' and for "# of unique Brand ID" is '2'.

Use the following script in order to change the column style based on 2 criterias:

widget.on("ready", function(widget, args) {
                               
                                                var CUR_COL_FIND_CRITERIA_red = "[fidx=5]";
                                                var CUR_COL_FIND_CRITERIA_green = "[fidx=6]";
                                                var FORMAT_COL_FIND_CRITERIA = "[fidx=4]";
                               
                                                //format a cell according to a condition on the current cell
                                                function formatCell(){
                                                                if ($(this).attr('val') <= $(this).siblings(CUR_COL_FIND_CRITERIA_red).attr('val')){
                                                                                $(this).css("color", "red");
                                                                }
                                                                else if($(this).attr('val') <= $(this).siblings(CUR_COL_FIND_CRITERIA_green).attr('val')){
                                                                                $(this).css("color", "green");
                                                               
                                                                }
                                                }
 
                                                //get all column cells and format other cells
                                                var cells = $('[id="' + widget.oid+ '"]').find('td' + FORMAT_COL_FIND_CRITERIA).each(formatCell);
 
                                                //hide current column and set new width for the pivots
                                                $('[id="' + widget.oid+ '"]').find(CUR_COL_FIND_CRITERIA_red).html("");
                                                $('[id="' + widget.oid+ '"]').find(CUR_COL_FIND_CRITERIA_red).css("width", "0px");
                                                $('[id="' + widget.oid+ '"]').find(CUR_COL_FIND_CRITERIA_green).html("");
                                                $('[id="' + widget.oid+ '"]').find(CUR_COL_FIND_CRITERIA_green).css("width", "0px");
                });

To sort by a hidden value (1st column) you can use this script.

Note that the css width is 1px instead of 0.  Using 0 didn't hide the column but made the contents appear blank.

/********************************************************/
/*Style pivot column based on another pivot column*******/
/********************************************************/

// Ensure that the sort column is the first one, and the visible column is the second one

widget.on("ready", function(widget, args) {
 
 var CUR_COL_FIND_CRITERIA = "[fidx=0]";
 var FORMAT_COL_FIND_CRITERIA = "[fidx=1]";

//hide current column and set new width for the pivots
 $('[id="' + widget.oid+ '"]').find(CUR_COL_FIND_CRITERIA).html("");
 $('[id="' + widget.oid+ '"]').find(CUR_COL_FIND_CRITERIA).css("width", "1px");
 });