Get the dimension fields visible in a pivot table

[fineasfalco]

Hi community,

I have to determine through a macro vb the number of dimensions visible in a pivot table.

The dimensions are dynamically shown / hidden through conditional expressions.

How can I get the current number of dimensions visible in the table?

Unfortunately GetProperties.Dimensions.Count returns all the dimensions , not only the visible ones.

Could you help me?

Tks

BR

Fineas

[marcus_sommer]

I think I wouldn't try it with a macro else counting/summing the conditional expressions. Assuming that each conditional expression returned 0 or 1 you could use: rangesum(cond1, cond2, ...) to get the number of active dimensions.

- Marcus

[fineasfalco · Accepted solution]

Tks for your message. it's a good idea.

Unfortunately, My expression condition return 0 or -1 values (0 enable / -1  disable).

I have found a different solution with a macro.

Following an summary:

set chart = ActiveDocument.GetSheetObject("ObjectID")

set cp = chart.GetProperties

set dims = cp.Dimensions

the instruction condExp = dims(t).EnableCondition.Expression   return the t-th expression condition .

then for each pivot dimension fields I can evaluate the expression condition.

if the condiction is equal to 0 then the dimension fields is visible and a counter can be incremented.

Regards