Hi experts, is it possible to implement the "drill down" in pivot grid? I'm imaging user can click on a cell of pivot table, then I get all the dimension information of that cell, then I can do a search against my datasrouce and show user some data.
For example, http://www.igniteui.com/pivot-grid/overview, in this link, if user clicked a cell, how can I get which seller,which year/month/date of that cell user just clicked?
Hello Sathya,
There is no easy way to get all the dimension information. What you can get is cell, column header and row header values using the following cell click handler.
$("#pivotGrid").igPivotGrid("grid").element.on("iggridcellclick", function (evt, ui) {
var colHeader, rowHeader, cellValue;
colHeader = ui.owner.columnByKey(ui.colKey).headerText;
rowHeader = ui.row.find(".ui-igpivotgrid-header .ui-iggrid-headertext").text();
cellValue = $(ui.cellElement).text();
});
Hope this helps,Martin PavlovInfragistics, Inc.
Martin, thank you for your reply. I have two more questions for you. my fiddle is here, http://jsfiddle.net/zhangming870/5vy4eab6/
1. As long as I expand rows and columns, this event does not get fired?
2. I see the aggregator function has two parameters, function aggregatorFunc (items, cellMetadata), Is it possible to get dimension from cellMetadata?
Actually I have another question.. Based on your experience, what's your suggestion to do this "drill down" feature?
About question 1. Yes that's expected. The event will fire only for data and not for header cells.
About question 2. Yes you can.
From the cellMetadata you can get the following information:
cellMetadata.measureIndex();
cellMetadata.rowIndex();
cellMetadata.rowTuples()
cellMetadata.measures();
cellMetadata.columnIndex();
cellMetadata.columnTuples();
cellMetadata.cellOrdinal();
Here is an example code:
aggregator: function (items, cellMetadata) {
// gets only the first measure
// cellMetadata.measures() returns an array of measures i.e. http://igniteui.com/help/api/2016.1/ig.Measure
var measure = cellMetadata.measures().item(0).uniqueName();
}
The igPivotGrid provides "drill down" feature out of the box using the (+/-) buttons. What is the use case you're trying to achieve?
i'm trying to implementing a drill down feature just like what you get in MS Excel pivot table. If you click cell of MS Excel pivot table, it will list lines which contribute to that cell value. What i'm planing to do is whenever a cell is clicked, get the dimensions of this cell first. But hard...
When the aggregator callback is executed you have the underline data in the "items" parameter. What you can do is to store that data in an object with an unique property name generated from the "cellMetadata" object. Store the unique property name in the cell DOM as well, so that you can get it from the JavaScript Event object when the user clicks on the cell. Then use that property to lookup the data in the object.
I'll need some time to try this approach to see if it will work.
Best regards,Martin PavlovInfragistics, Inc.
I'm attaching a sample that demonstrates the approach described in my previous post. The sample contains igPivotGrid and igGrid. When you click on a data cell the igGrid is populated with the records that participate in the aggregated data.
Martin, you are right. My scenarios is slightly different from your example. Please go through my plan and give me suggestion if anything is wrong.
My pivot and grid does not share the same datasource/schema, so my aim is to get pivot's dimension and correspondence dimension value of this cell. So I guess I can put dimension/dimensionValue pair into data-dimensions like this:
var rowDimensions= $("#pivotGrid").igPivotGrid("option", "dataSource").rowAxis(); var colDimensions= $("#pivotGrid").igPivotGrid("option", "dataSource").columnAxis();
//myDimensionSeperateByUnderscore = Product_Datevar myDimensionSeperateByUnderscore = MyFancyFunction(rowDimensions,colDimensions);
arr["" + c] = items[0];
return "<span data-ix='" + c++ + " data-dimensions= "+myDimensionSeperateByUnderscore +" '>" + unitSoldSum(items, cellMetadata) + "</span>";
Note: arr only store the first item.
And then in iggridcellclick event, I can get acitve dimensions of pivot grid, and perform a search operation, show result in grid.
$("#pivotGrid").on("iggridcellclick", function (evt, ui) { cellValue = $(ui.cellElement).find("span").attr("data-ix");var cellDimensions = $(ui.cellElement).find("span").attr("data-dimensions");
var dimensions = MyFancyDimensionsSplitFunction(dimensions); // i get ['Product', 'Date']
for(var d in dimensions){ alert(cellValue[d]); //show every dimension value