Pivot Table - Shraes

[#1]

I want to calculate the shares in a Pivot Table.

My dimensions are Product, Classification, and Month

As we know in a pivot table we can move the position of the dimension. So I can have any combination like Classification, Month, and Product

Question: I would like to know if there is a way I can find the order or position of my dimension in a pivot table when I change the order of my dimensions.

I would also like to know the dimensions are available when I expand and collapse my dimensions in a pivot table.

Thanks for your time!

Thanks

[#2 · Accepted solution]

Working with Dimensions using the API is somewhat difficult.

I have attached a sample app and a macro that should tell you the order of the Dimensions. This macro requires that each Dimension have a Title (<use field name> will not work). I couldn't find the property which told me the field or expression defining the Dimension, as that would have been preferrable.

Here's the macro:

Sub Dims
 set cht=ActiveDocument.GetSheetObject("CH01")
 set Prop = cht.GetProperties
 set dimz = Prop.Dimensions
 ActiveDocument.Variables("vDim1").SetContent dimz.Item(0).Title.v,true
 ActiveDocument.Variables("vDim2").SetContent dimz.Item(1).Title.v,true
 ActiveDocument.Variables("vDim3").SetContent dimz.Item(2).Title.v,true
End Sub

This macro sets three variables, which gives you the order (Dim1 = first, Dim2 = second...). I'm not sure I know what you mean by figuring out which dimensions are available. I don't know how much more info you can get from the API at this level.

[#3]

Thanks a lot for taking time and replying . That is what I need.

My second half of my question was - If I have 3 dimensions in my pivot and when I collapse one of them I can see only two. So,is there any way we can find which dimensions are Expanded and which dimension is collapsed.

Thanks again Miller

[#4]

I don't think there is a way to tell using the API. I believe this is because individual values can be expanded. For Product, you could have A expanded and B collapsed. There isn't an element for each value of Product, only one for the entire Product dimension. Basically, there would be no way to handle having different statuses between records. I'm not sure if that makes it more or less confusing.

Within the chart, there is some ability to determine whether the dimensions of the records in question are expanded or collapsed. Use the function Dimensionality(). Add another expression to my chart and set the expression to:=Dimensionality().

If the first dimension is collapsed, that expression should equal 1. If the second is collapsed, then it should equal 2. If they are both expanded, then it should equal 3. I'm not sure what exactly you could use that for though.