The external references have no bearing on the data source of the Top 10 (Rolling) tab, but I’ve cleaned up the file and removed all of the irrelevant parts. Small charts’ pivot tables are to the right, and the source of the bottom pivot table in question is on the tab “Rolling Disbursements”.
I cannot provide a complete sample code since it is well over 4000 lines of code, but the most pertinent part is this:
for (int u = 2; u < wb.Worksheets["Rolling Disbursements"].Cells.MaxDataRow; u++)
{
Cell c = wb.Worksheets["Rolling Disbursements"].Cells[$"AC{u}"];
String errorValue = (String)c.StringValue;
if (errorValue == "") {
Chart rsxChart = wb.Worksheets["Top 10 (Rolling)"].Charts["Chart 10"];
Chart dltChart = wb.Worksheets["Top 10 (Rolling)"].Charts["Chart 8"];
rsxChart.ChartObject.X = dltChart.ChartObject.X;
rsxChart.ChartObject.Y = dltChart.ChartObject.Y;
rsxChart.ChartObject.Width += dltChart.ChartObject.Width;
rsxChart.Title.Text = "Top 20 Rolling CPU by NPM";
// Remove the chart to begin with.
wb.Worksheets["Top 10 (Rolling)"].Charts.RemoveAt(wb.Worksheets["Top 10 (Rolling)"].Charts.IndexOf(dltChart));
PivotTable pivotTable = wb.Worksheets["Top 10 (Rolling)"].PivotTables["PivotTable3"];
PivotFilter filter = pivotTable.PivotFilters[0];
filter.AutoFilter.FilterTop10(0, true, false, 20);
pivotTable.RefreshData();
pivotTable.CalculateData();
// force the bottom table to match the new bottom chart
pivotTable = wb.Worksheets["Top 10 (Rolling)"].PivotTables["PivotTable12"];
// this works to change the item count to 20, but I need to change the filter from "Rolling Delta" to "Rolling CPU" column
filter = pivotTable.PivotFilters[0];
filter.AutoFilter.FilterTop10(0, true, false, 20);
// Right here I need to change the sort to "Rolling CPU", the same way you would open the "More Sort Options..." menu in Excel and change it to sort by "Rolling CPU" descending instead of "Rolling Delta" descending
// ...?
pivotTable.RefreshData();
pivotTable.CalculateData();
RefreshWorksheet(wb.Worksheets["Top 10 (Rolling)"]);
break;
}
}
The function “RefreshWorksheet()” is as follows:
private static void RefreshWorksheet(Worksheet sheet)
{
sheet.RefreshPivotTables();
for (int i = sheet.PivotTables.Count - 1; i >= 0; i--)
{
PivotTable pt = sheet.PivotTables[i];
try
{
PivotField pivotField = pt.RowFields[0];
//Setting the field auto sort.
pivotField.IsAutoSort = false;
//Setting the field sorting in ascending order.
pivotField.IsAscendSort = true;
//Setting the field auto sort using the field itself.
pivotField.AutoSortField = -1;
pivotField.IsAscendShow = true;
pt.FieldListSortAscending = true;
pt.CustomListSort = true;
pt.RefreshDataFlag = true;
pt.RefreshData();
pt.CalculateData();
pt.RefreshDataFlag = false;
}
catch (Exception x)
{
Environment.Exit(1);
}
pt.RowFields[0].HideDetail(true);
pt.RefreshDataOnOpeningFile = true;
}
}
At a high level overview, I am simply grabbing data from a database and loading it into the Rolling Disbursements tab data then refreshing the data connections in the workbook. Afterwards, in this code, I am scanning a specific column to see if there is no data (errorValue == “”), which indicates that we need to remove the one chart I mentioned and make these changes. I resize the chart object based on the size of the other charts, change the title to say 20 instead of 10, adjust the items with FilterTop10(), and that all works. The problem is where I’ve noted before, I now need to change PivotTable12 to filter by a different column, and sort by that same column in descending order. However, whenever I try to change the filters, either the whole thing breaks, or nothing changes.