Pivot 2.0 - Manipulating a Pivot Chart

 

 

widget.transformPivot(
  {
    type: ['value']
  },
  function (metadata, cell) {
    // Manuipulation code
  }
);

widget.transformPivot(
  {},
  function (metadata, cell) {
    if (metadata.type.includes('value')) {
      // Manuipulation code
    }
  }
);

 

 

 

H2 - Cell Indexes

Each cell is represented by three indexes:

• Metadata Index - Representing the logical column ID in the table (aligns with the selected rows/values/columns)
• Column Index - Representing the column number in the table
• Row Index - Representing the row number in the table

Metadata Index

See the following pivot table, the pivot configuration pane, and the corresponding metadata indexes:

Original Table	Metadata Index

Here is an example of how to manipulate a cell based on its metadata index:

 

 

widget.transformPivot(
  {},
  function (metadata, cell) {
    if (metadata.index == 1) {
      // Manuipulation code
    }
  }
);

 

 

 

Column/Row Index

See the following pivot table and the corresponding row/column indexes

Original Table	Row/Column Indexes

Here is an example of how to manipulate a cell based on its columns/row index:

 

 

widget.transformPivot(
  {},
  function (metadata, cell) {
    if (metadata.colIndex == 3 && metadata.rowIndex == 2) {
      // Manuipulation code
    }
  }
);

 

 

Cell Row/Column/Measure Name

Each cell may be affiliated with three metadata values:

• Measure - The measure calculated in this cell (name & formula)
• Column(s) - The column(s) this cell is under (field, title, & value)
• Row(s) - The rows(s) this cell belongs to (field, title, & value)

Manipulating a Cell (Based on their Measure)

Here is an example of how to manipulate a value cell based on the measure's name:

 

 

 

widget.transformPivot(
  {},
  function (metadata, cell) {
    if (metadata.measure.title === 'SUM') {
      // Manuipulation code
    }
  }
);

 

 

Manipulating a Cell (Based on Their Row)

Here is an example of how to manipulate a value cell based on the row's value:

 

 

widget.transformPivot(
  {},
  function (metadata, cell) {
    // Format based on the value of the a row's name and value
    metadata.rows.forEach(function(row) {
      if (row.title === 'Year' && row.member === '2012-01-01T00:00:00.000') {
        // Manuipulation code
      }
    })
  }
);

 

 

Manipulating a Cell (Based on Their Column)

Here is an example of how to manipulate a value cell based on the column's value:

 

 

widget.transformPivot(
  {},
  function (metadata, cell) {
    // Format based on the value of the a row's name and value
    metadata.columns.forEach(function(column) {
      if (column.title === 'Online' && column.member === 'False') {
        // Manuipulation code
      }
    })
  }
);

 

 

 

Cell Manipulation

The possible manipulation options of a cell include:

• value - Raw value of the cell from query response (manipulating this value is useless)
• content - The HTML contents of this cell
• style - The cell formatting

Here is an example of how to manipulate value cells' style:

 

 

widget.transformPivot(
  {},
  function (metadata, cell) {
    cell.style = {
      backgroundColor : 'lightgray',
      fontSize : 14,
      fontWeight : 'bold',
      fontStyle : 'italic',
      textAlign : 'center',
      color : 'black',
      borderColor : 'black',
      borderWidth : '3px',
      minWidth : '150px',
      maxWidth : '200px'
    };
  }
);

 

 

Here is an example of how to manipulate value cells' value:

 

 

widget.transformPivot(
  {},
  function (metadata, cell) {
    if (cell.content == '') cell.content = '---'
  }
);

 

 

Check out this related content