Pivot Tables - Show Value As - % of Parent Total

[falcondata]

Read and complete the full issue template

Do you want to request a feature or report a bug?

• [] Bug
• [X] Feature

Version of ClosedXML

e.g. 0.90.0 What is the current behavior?

Feature does not seem to exist

What is the expected behavior or new feature?

Allow percentages in a pivot table to be calculated based on the parent row of the pivot

ie:
pt.Values.Add("Current Balance", "Net Balance") .SetSummaryFormula(XLPivotSummary.Sum) .NumberFormat.Format = "$0,000.00"; pt.Values.Add("Current Balance", "% Balance") .ShowAsPercentageOfParentTotal() // this would be a nice addition .NumberFormat.Format = "0.00%";

I see several of the Show Value As methods, but a few are missing. I understand pivot tables is a work in progress. Is there a way to emulate this function with the current methods?

Did this work in previous versions of our tool? Which versions?

No idea

[gjactat]

Hello, This would be great indeed. I've been digging in both ClosedXML source code and actual OpenXML code (via Open XML SDK Productivity Tool).

The following code sample is what OpenXML do to add a simple "Percent Of Total" field within a pivot table :

DataField dataField5 = new DataField(){ Name = "Pourcentage", Field = (UInt32Value)2U, ShowDataAs = ShowDataAsValues.PercentOfTotal, BaseField = 0, BaseItem = (UInt32Value)0U, NumberFormatId = (UInt32Value);
dataFields2.Append(dataField5);

On the other hand, adding a "Percent Of Parent Row" translates to :

DataField dataField2 = new DataField(){ Name = "Pourcentage", Field = (UInt32Value)2U, BaseField = 0, BaseItem = (UInt32Value)0U, NumberFormatId = (UInt32Value)10U };

DataFieldExtensionList dataFieldExtensionList1 = new DataFieldExtensionList();

DataFieldExtension dataFieldExtension1 = new DataFieldExtension(){ Uri = "{E15A36E0-9728-4e99-A89B-3F7291B0FE68}" };
dataFieldExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
X14.DataField dataField3 = new X14.DataField(){ PivotShowAs = X14.PivotShowAsValues.PercentOfParentRow };

dataFieldExtension1.Append(dataField3);
dataFieldExtensionList1.Append(dataFieldExtension1);
dataField2.Append(dataFieldExtensionList1);
dataFields1.Append(dataField2);

It's a bit more complicated though... I haven't seen any usage of the "PivotShowAsValues" enum in ClosedXML (only ShowDataAsValues enum).

[igitur]

Thanks @gjactat. Would you mind taking it a bit further and contribute a PR?

[gjactat]

I'll have to dig much deeper then ;-) I don't know much about ClosedXML source code right now. Especially the part that handles OpenXML generation under the hood.

[igitur]

We all learnt by getting our hands dirty.