I was wondering if there is a drilldown for bar charts on aspose.cells. This is what I currently have,
image.png (11.3 KB)
What I wanted to do is, when I click a series, for example CPE, then it would go to another chart where it only shows CPE records base on the month and year. Would that be possible?
Thank you!
This Topic is created by Amjad_Sahi using Email to Topic tool.
Thanks for the screenshot.
Could you please create your desired linked charts in MS Excel manually, zip the Excel file and provide us. We will check it on how to do it via Aspose.Cells APIs.
Please see Create Pivot Tables and Pivot Charts | Documentation (aspose.com)(Create Pivot Tables and Pivot Charts|Documentation) for your reference.
Sample code:
Workbook wb = new Workbook();
Worksheet ws = wb.Worksheets[0];
ws.Cells["A1"].PutValue("Category");
ws.Cells["A2"].PutValue("Power");
ws.Cells["A3"].PutValue("CPE");
ws.Cells["A4"].PutValue("Mait");
ws.Cells["A5"].PutValue("Rad");
ws.Cells["A6"].PutValue("Tab");
ws.Cells["A7"].PutValue("Power");
ws.Cells["A8"].PutValue("CPE");
ws.Cells["A9"].PutValue("Mait");
ws.Cells["A10"].PutValue("Rad");
ws.Cells["A11"].PutValue("Tab");
ws.Cells["A12"].PutValue("Power");
ws.Cells["A13"].PutValue("CPE");
ws.Cells["A14"].PutValue("Mait");
ws.Cells["A15"].PutValue("Rad");
ws.Cells["A16"].PutValue("Tab");
ws.Cells["B1"].PutValue("Year");
ws.Cells["B2"].PutValue("2017");
ws.Cells["B3"].PutValue("2017");
ws.Cells["B4"].PutValue("2017");
ws.Cells["B5"].PutValue("2017");
ws.Cells["B6"].PutValue("2017");
ws.Cells["B7"].PutValue("2017");
ws.Cells["B8"].PutValue("2017");
ws.Cells["B9"].PutValue("2017");
ws.Cells["B10"].PutValue("2017");
ws.Cells["B11"].PutValue("2018");
ws.Cells["B12"].PutValue("2018");
ws.Cells["B13"].PutValue("2018");
ws.Cells["B14"].PutValue("2018");
ws.Cells["B15"].PutValue("2018");
ws.Cells["B16"].PutValue("2018");
ws.Cells["C1"].PutValue("Month");
ws.Cells["C2"].PutValue("Jan");
ws.Cells["C3"].PutValue("Jan");
ws.Cells["C4"].PutValue("Jan");
ws.Cells["C5"].PutValue("Jan");
ws.Cells["C6"].PutValue("Jan");
ws.Cells["C7"].PutValue("Feb");
ws.Cells["C8"].PutValue("Feb");
ws.Cells["C9"].PutValue("Feb");
ws.Cells["C10"].PutValue("Feb");
ws.Cells["C11"].PutValue("Feb");
ws.Cells["C12"].PutValue("March");
ws.Cells["C13"].PutValue("March");
ws.Cells["C14"].PutValue("March");
ws.Cells["C15"].PutValue("March");
ws.Cells["C16"].PutValue("March");
ws.Cells["D1"].PutValue("Tick");
ws.Cells["D2"].PutValue(5);
ws.Cells["D3"].PutValue(1);
ws.Cells["D4"].PutValue(3);
ws.Cells["D5"].PutValue(7);
ws.Cells["D6"].PutValue(9);
ws.Cells["D7"].PutValue(10);
ws.Cells["D8"].PutValue(5);
ws.Cells["D9"].PutValue(2);
ws.Cells["D10"].PutValue(1);
ws.Cells["D11"].PutValue(3);
ws.Cells["D12"].PutValue(9);
ws.Cells["D13"].PutValue(11);
ws.Cells["D14"].PutValue(0);
ws.Cells["D15"].PutValue(12);
ws.Cells["D16"].PutValue(8);
int i = ws.PivotTables.Add("A1:D15", "G1", "PivotTable1");
PivotTable pivotTable = ws.PivotTables[i];
pivotTable.AddFieldToArea(PivotFieldType.Row, "Category");
pivotTable.AddFieldToArea(PivotFieldType.Column, "Year");
pivotTable.AddFieldToArea(PivotFieldType.Column, "Month");
pivotTable.AddFieldToArea(PivotFieldType.Data, "Tick");
PivotField field = pivotTable.ColumnFields["Year"];
field.SetSubtotals(PivotFieldSubtotalType.Automatic, false);
pivotTable.ColumnGrand = false;
pivotTable.RowGrand = false;
pivotTable.RefreshData();
pivotTable.CalculateData();
i = ws.Charts.Add(ChartType.Column, 18, 8, 28, 15);
Chart chart = ws.Charts[0];
chart.PivotSource = "Sheet1!PivotTable1";
wb.Save("PivotChart.xlsx");
Hope this help a bit.
Hi @Amjad_Sahi,
Attached is the sample I wanted to create using Aspose.cells.
Notice how you can drilldown thru the chart using the Type Axis. That is my desired outcome.
ChartDrilldown.zip (14.9 KB)
Thank you and regards!
Hi @Eric.wang,
I already had the code on how to create the pivot table and the chart. What I wanted to discover is how to allow my chart a Drilldown capabilities using the Type Axis. I have attached a sample excel file of the outcome I desire. Hope you can help me. Thank you!
ChartDrilldown.zip (14.9 KB)
Thanks for the sample file.
You need to set some Boolean options and other attributes on for the pivot chart for your needs. See the following sample code segment for your reference:
e.g.
Sample code:
.......
PivotField field = pivotTable.ColumnFields["Year"];
field.SetSubtotals(PivotFieldSubtotalType.Automatic, false);
pivotTable.ColumnGrand = false;
pivotTable.RowGrand = false;
pivotTable.RefreshData();
pivotTable.CalculateData();
i = ws.Charts.Add(ChartType.Column, 18, 8, 28, 15);
Chart chart = ws.Charts[0];
chart.PivotSource = "Sheet1!PivotTable1";
chart.PivotOptions.DropZoneSeries = true;
chart.HidePivotFieldButtons = false;
chart.PivotOptions.DropZoneFilter = true;
wb.Save("e:\\test2\\PivotChart.xlsx");
Let us know if you still have any issue or confusion.
Hi @Amjad_Sahi,
If the chart is created that way (chart.PivotSource = “Sheet1!PivotTable1”;), how can I access each series inside it for custom formatting? Like the Series.Border.Color or Series.Marker.ForegroundColor.
Thank you!
Please call Chart.Calculate() method before manipulating series formatting, e.g.
.......
chart.Calculate();
chart.NSeries[0].Border.Color = Color.Red;
Hi @Amjad_Sahi,
How can you access the ‘Hide Report Filter Buttons on Chart’ in Aspose.Cells. image.png (156.2 KB)
I wanted to set it to false so that it wont be visible in the chart.
Thank you!
Hi, the chart.HidePivotFieldButtons = true; hides everything in the chart. I only need to hide the ‘Report Filter Buttons’ and not the ‘Axis Field Buttons’ since I need it for my Chart drilldown.
Thank you!
Please try using the lines of code:
e.g.
Sample code:
chart.HidePivotFieldButtons = false;
chart.PivotOptions.DropZoneSeries = true;
chart.PivotOptions.DropZoneCategories = true;
chart.PivotOptions.DropZoneFilter = false;
...