Pivot Tables - Parsing Data Changes

Aspose.Cells Product Family
1

Hey - I am trying to parse a pivot table to understand changes in the data source. I have a couple questions:

Is there a way to tell if a user applied a selection to the column label choices that would filter out a column ?

Also is there a way to understand what cells/data source is affecting a given value in a pivot table? So if a value changes - do we have an easy way to see all the data source cells that are driving that value?

2

@mallain2323,
Could you please elaborate the requirements a bit more. Share your template file and screenshots for our analysis.

3

Book1.zip (15.1 KB)

What I would like to know is if there is a selection change that alters the visible columns:
2020-09-14 11_32_20-Window.png (15.5 KB)

Is there a way to tell that there are columns 2019, 2020, 2021 but currently only 2019 and 2021 are being displayed?

Also - Is there a way to tell what cells are driving the number in the pivot table. For example - in the pivot table we know its row is “x” and the column is 2019 but is there a way to say that these are the cells that are used to calculate that number. What I want to do is loop through the cells from two different versions to show which ones changed and caused the number in the pivot table to change.

4

@mallain2323,
You may use following sample code to achieve the first requirement that how many columns are there and which of these columns are hidden.

Workbook wb = new Workbook(path + "Book1.xlsx");

// Get first pivot table in the worksheet
PivotTable pt = wb.Worksheets[0].PivotTables[0];
foreach (PivotField pvtFld in pt.Fields(PivotFieldType.Column))
{
    if (pvtFld.Name == "Years")
    {
        PivotItemCollection pItems = pvtFld.PivotItems;
        foreach (PivotItem item in pItems)
        {
            Console.WriteLine($"{item.Value} Is Hidden = {item.IsHidden}");
        }
    }
}

Regarding your second query, could you please share how this information can be retrieved in MS Excel. We will analyze your feedback and provide our comments accordingly.

5

The only way I can see that the items are mapped back to the data source is by column and row. But in this scenario the columns are Year, Quarter and Month - and the Data Source has values like 1/2019, 2/2019 3/2019 etc… Is there a way to know (dynamically speaking) how excel is calculating the values. How does excel translate the columns in the pivot table to the data source? (or row?)

6

@mallain2323,
We are afraid that no such document is available which can be referred in this regard. This scenario needs further investigation therefore it is logged in our database for further investigation. You will be notified here once any update is available for sharing.

This requirement is logged as:
CELLSNET-47610 - Parsing data changes in Pivot Tables