1. Home
  2. Forum
  3. ASP.NET Core - EJ 2
  4. Passing data from datasource in Tooltip, Hyperlink and for Conditional formatting

Passing data from datasource in Tooltip, Hyperlink and for Conditional formatting

Hello,


I am building a Pivot table and I would like to pass some fields from the datasource feeding the Pivot table to:

  1. a Tooltip 
  2. an Hyperlink (for value cells)
  3. for conditional formatting.

While excluding those fields from showing in the Rows, Columns and Value fields of the Pivot table.

How would I achieve this (if it's even possible)?

For example, in your demo Pivot Table (https://ej2.syncfusion.com/aspnetcore/PivotTable/Default#/bootstrap5 ), let's say that :

- I want the "Country" not to show in the Pivot Table but in a tooltip for the value cell

- I want the "Country" value to be  used in an Hyperlink

- I want the "Sale amount" not to show in the Pivot Table but be used in conditional formatting logic so the value cell for "Units sold" shows as green background if " Sale amount" > $1,000,000.

Thank you !


6 Replies 1 reply marked as answer
AP AngelinFaithSheeba PaulvannanRajadurai Syncfusion Team September 21, 2022 02:23 PM UTC

Hi Loic,


Please find the details below.


Query

Comments

I want the "Country" not to show in the Pivot Table but in a tooltip for the value cell

Please create a custom tooltip in the pivot table's dataBound event to meet your requirements. You can add specific field information from the current cell's data to the tooltip content that will be displayed while hovering on each values in the pivot table. Please refer the below code example.

 

Code Example:

function onDataBound(args) {

  var pivotGridObj = document.getElementById("pivotview").ej2_instances[0];

  tooltip = new ej.popups.Tooltip({

    target: "td.e-valuescontent",

    showTipPointer: false,

    mouseTrail: true,

    enableRtl: pivotGridObj.enableRtl,

    beforeRender: setToolTip,

    beforeOpen: function (args) {

      args.element.classList.add("e-pivottooltipwrap");

    },

  });

  tooltip.appendTo(pivotGridObj.element);

}

 

function setToolTip(args) {

  var pivotGridObj = document.getElementById("pivotview").ej2_instances[0];

  let colIndex = Number(args.target.getAttribute("aria-colindex"));

  let rowIndex = Number(args.target.getAttribute("index"));

  let cell = pivotGridObj.dataSourceSettings.values.length > 0 &&

    pivotGridObj.pivotValues && pivotGridObj.pivotValues[rowIndex] && pivotGridObj.pivotValues[rowIndex][colIndex]

      ? pivotGridObj.pivotValues[rowIndex][colIndex - 1] : undefined;

  var aggregatedValues = 0;

  for (var i = 0; i < Object.keys(cell.indexObject).length; i++) {

    let dataIndex = Object.keys(cell.indexObject)[i];

    if (pivotGridObj.dataSourceSettings.dataSource && pivotGridObj.dataSourceSettings.dataSource[dataIndex] &&

      // here you can specify the field (example: "In_Stock" ) from the cell set(data) that you need to add it to the tooltip content

      pivotGridObj.dataSourceSettings.dataSource[dataIndex].In_Stock) {

        aggregatedValues += pivotGridObj.dataSourceSettings.dataSource[dataIndex].In_Stock; // here you can get the values of specific value field from each conbination cell set(data) for the hovered cell from the given data source that can to be aggregated based on you needs.

    }

  }

  tooltip.content = "";

  let aggregateType;

  let caption;

  let hasField = false;

  if (cell && pivotGridObj.engineModule.fieldList[cell.actualText]) {

    let field = pivotGridObj.engineModule.fieldList[cell.actualText];

    aggregateType = field.aggregateType;

    caption = field.caption;

    hasField = true;

  }

  if (cell && hasField) {

    let rowHeaders = pivotGridObj.getRowText(rowIndex, 0);

    let columnHeaders = pivotGridObj.getColText(0, colIndex, rowIndex);

    let value = cell.formattedText;

    tooltip.content =

      "<div class=" +

      "e-pivottooltip" +

      "><p class=" +

      "e-tooltipheader" +

      ">" +

      pivotGridObj.localeObj.getConstant("row") +

      ":</p><p class=" +

      "e-tooltipcontent" +

      ">" +

      rowHeaders +

      "</p></br><p class=" +

      "e-tooltipheader" +

      ">" +

      pivotGridObj.localeObj.getConstant("column") +

      ":</p><p class=" +

      "e-tooltipcontent" +

      ">" +

      columnHeaders +

      "</p></br>" +

      (cell.actualText !== ""

        ? "<p class=" +

          "e-tooltipheader" +

          ">" +

          (pivotGridObj.dataType === "olap"

            ? ""

            : pivotGridObj.localeObj.getConstant(aggregateType) +

              " " +

              pivotGridObj.localeObj.getConstant("of") +

              " ") +

          caption +

          ":</p><p class=" +

          "e-tooltipcontent" +

          ">" +

          value +

          "</p></br>" +

          "<p class=" +

          "e-tooltipheader" +

          ">" +

          "In_Stock" +

          ":</p><p class=" +

          "e-tooltipcontent" +

          ">" +

          aggregatedValues +

          "</p>" +

          "</div>"

        : "");

  } else {

    args.cancel = true;

  }

}

 

Screenshot:

In_Stock field is not bind in report but shown in tooltip.

I want the "Country" value to be  used in an Hyperlink

Using HyperlinkCellClick event, you can customize the specific cell’s hyperlink based on your needs. Please refer the code example below.

 

Code Example:

function hyperlinkCellClick(args){

      if (args.currentCell.querySelector('a')) {

        args.currentCell.querySelector('a').setAttribute('data-url','www.ggogle.com');

        args.cancel = false;

     }

  }

 

Please refer the below UG document to know more about HyperlinkCellClick event

 

Document: https://ej2.syncfusion.com/aspnetcore/documentation/pivot-table/hyper-link#event

- I want the "Sale amount" not to show in the Pivot Table but be used in conditional formatting logic so the value cell for "Units sold" shows as green background if " Sale amount" > $1,000,000.

Using conditional formatting, you can apply formatting to the specific field with that field data alone. However, you can use queryCellInfo event to apply the custom styles for the cells based on the field value which is not bind in the report. Please refer the code example below and screenshot.

 

Code Example:

function queryCellInfo(args) {

  var pivotGridObj = document.getElementById("pivotview").ej2_instances[0];

  let colIndex = Number(args.cell.getAttribute("aria-colindex"));

  var rowIndex = Number(args.cell.getAttribute("index"));

  var InStockValues = 0;

  if (pivotGridObj && pivotGridObj.pivotValues && pivotGridObj.pivotValues[rowIndex] &&

    pivotGridObj.pivotValues[rowIndex][colIndex - 1]) {

    for (var i = 0;i < Object.keys(pivotGridObj.pivotValues[rowIndex][colIndex - 1].indexObject).length;i++) {

      var dataIndex = Object.keys(pivotGridObj.pivotValues[rowIndex][colIndex - 1].indexObject)[i];

      if (pivotGridObj.dataSourceSettings.dataSource && pivotGridObj.dataSourceSettings.dataSource[dataIndex] &&

        // here you can specify the field (example: "In_Stock" ) from the cell set(data) that you want to use it as a condition for applying custom styles.

        pivotGridObj.dataSourceSettings.dataSource[dataIndex].In_Stock) {

            //In_stock field is not bind in the pivot table. So we need to aggregate its value.

            InStockValues += pivotGridObj.dataSourceSettings.dataSource[dataIndex].In_Stock; // here you can get the values of specific value field from each combination cell set(data) for the value cell from the given data source that can to be aggregated based on you needs.

      }

    }

  }

  //Apply custom styles based on the condition you need.

  if (InStockValues < 400) {

    args.cell.classList.add("e-custom");

  }

}

 

Screenshot:

Apply custom styles to the Sold and Amount field based on In_Stock values.

However, we didn’t recommend this method because this leads to performance constraint.


Meanwhile, we have prepared a sample for your reference below.


Sample: https://www.syncfusion.com/downloads/support/directtrac/general/ze/PivotTable538456651


Please let us know if you have concerns.


Regards,

Angelin Faith Sheeba


Marked as answer
LR Loic Roger September 22, 2022 12:50 AM UTC

Hi Angelin.

I was not able to use your sample.

When looking through the debugger, I see that  the "indexObject" is null for me for all columns except the first one :


image_5.png

That IndexObject gives me the index of the objects from the datasource array for the whole row.

So, I cannot "map" a cell to its associated array from the datasource, which I would need to retrieve the appropriate data from the datasource.

Potentially I could loop through the indexObject of the first column for a particular row and identify those index from the datasource that match the current column but that seems inefficient. 

How can I simply "map" a cell to the associated datasource objects ?

Note that I am using .NET 6 (your sample was on .NET Core 3.1).


Thank you.


LR Loic Roger replied to Loic Roger September 22, 2022 12:55 AM UTC

Also, I forgot to add, I am using EJ2 AspNet Core version 20.2.0.48.


AP AngelinFaithSheeba PaulvannanRajadurai Syncfusion Team September 22, 2022 03:26 PM UTC

Hi Loic,


We believe the you missed enabling the drillthrough feature, which allows you to obtain the "indexObject" of each pivot value in the "queryCellInfo" event. Please see the code example below for more information.


Code example:

<ejs-pivotview id="pivotview" allowDrillThrough="true">

</ejs-pivotview>


However, we don’t have any other easiest way to map a cell to the associated datasource objects. And the version is not a problem for this.


Please let us know if you have any concerns.


Regards,

Angelin Faith Sheeba.


LR Loic Roger September 22, 2022 03:35 PM UTC

Hi Angelin,

you are right !! Adding  allowDrillThrough="true"    now shows the "indexObject" for each cell correctly.

You're the best ! Thanks for all your help !   


AP AngelinFaithSheeba PaulvannanRajadurai Syncfusion Team September 23, 2022 06:13 AM UTC

Hi Loic,


Please contact us if you have any other queries. We are always happy to assist you.


Regards,

Angelin Faith Sheeba.


Loader.
Up arrow icon