Is that possible i can set the subtotals by myself?

Question

i have some metric which is the distinct count of something, so any of the excel subtotal function will not work. so can i set the subtotal value when i create a pivottable in C# using vsto?
i know when using olap service, this can be done, but i dont know how it implements it .

Answer 1

Hello Kevin,

Subtotal is controlled by the PviotField's SubTotals proeprty. The document is,
http://msdn.microsoft.com/en-us/library/bb209315.aspx 

 

So if you want to show SUM subtotals for a PivotaField, you can use some codes similar as, (you may need to align it to real C# codes)

ActiveSheet.PivotTables["Pivottable1"].PivotFields["[Sales Date].[Date]"].Subtotals[2] = true.



Ji Zhou

MSDN Subscriber Support in Forum

If you have any feedback on our support, please contact [e-mail]

 

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

hi, ji.Zhou, then how come when i create pivot table using my olap cube, this subtotal is none of these subtotal function? for example, the aggregation of some metric in my olap cube is distinct count something, then excel can NEVER calculate this metric automatically, it can only set the subtotal the value it retrieved from the cube.
i wanna do the same thing, but no luck yet.

my current workaround is a little tricky, i set the custom number format with a string, which is the value i calculate by my own function.