I think there is not such event, that u can catch.
One of our customer had the same requirement but I think it is not possible, to get the current position in the tree in a pivot table for each row....
Try to give a look on this post:
Use the dimensionality() function in your expressions to detect how exactly your pivot table has been collapsed/expanded.
- dimensionality() = 0 means Totals row
- dimensionality() = 1 means level 1 (first dimension value line or one dimension level without aggregation)
- and so on.
See the QV Desktop help article on dimensionality(). It has a very clear explanation and a link to some very good examples.
Note that this works only for horizontal dimensions. For vertical dimensions, you have to use secondarydimensionality()
This is really a nice function. Thought I can not use it as expression condition to hide the column (it dose not work), but I can define new expression value by using value of dimensionality() as conditions , like 'N/A' to the expression columns to identify the data can not be used.
Thanks very much
No indeed, you can't use it in a conditional show for an expression as the function wouldn't know what to return.
However, you can use it in the expressions themselves. For example,
=IF (dimensionality() <= 1; Sum(stuff))
will force an arbitrary expression to return all NULL values in every cell when a pivot table is expanded. That's already one step in the right direction.
The next one is a bit tricky. Pivot tables won't make expression columns disappear, even if they contain nothing more than NULL values. There is a technique however (using an INLINE Table or ValueList()) to create dynamic expressions instead of fixed-column expressions. And those will disappear instantaneously as soon as every cell contains a NULL-value.
See Re: Hide a column in Pivot Table for more information and examples.