How can I set PivotField.Calculation in Excel/VSTO?
- by Kang Su
I'm trying to set the Calculation property on an OLAP PivotField with VSTO 3.0. For example:
pivotField.Calculation = XlPivotFieldCalculation.PercentOf;
If I do the above, the value I assign does not stay (Excel appears to revert the change). I suspect the reason is that the BaseField property of the PivotField also needs to be populated (as PercentOf needs a BaseField). But it appears to me that you can't set the BaseField property until you've set the Calculation property (otherwise you get a COMException).
I've tried to set ManualUpdate on the PivotTable to true, but with VSTO this rarely works, as this gets reverted immediately back to false.
Note, that this seems to work fine in VBA as you can assign multiple values in a single statement, like this:
With ActiveSheet.PivotTables("PivotTable1").PivotFields("[Measures].[Reseller Sales Amount]")
.Calculation = xlPercentOf
.BaseField = "[Geography].[Geography].[Country]"
.BaseItem = "[Geography].[Geography].[Country].&[France]"
.NumberFormat = "0.00%"
End With
But with C#/VSTO there's no construct like this (that I know of) and I'm stuck not able to do something like the above.
Further note, Calculation values that don't require a BaseField, e.g., XlPivotFieldCalculation.xlPercentOfTotal, get set just fine.
Any help on this would be greatly appreciated!
