Is this a hard constraint on pivoting axis using macro automation?
It took me a while to figure out how to pivot rows into columns when automatically building a new Pivot Table chart. Normally, the only way to do this is by dragging the row into a column, which changes the dimension from being on the vertical axis, to the horizontal axis.
The trick (it would appear) is to use the NumberOfLeftDimensions property. By default, this property is set to the number of dimensions in the Pivot Table. If you set this property to be an explicit value - e.g. 1, then all dimensions after the first dimension will be placed horizontally instead of vertically. Furthermore, if you increase the "NumberOfLeftDimensions" property to be greater than the number of dimensions, then this has the effect of moving expressions from columns to rows.
Therefore it is possible to have near complete control over the Pivot Table axis layout.
That notwithstanding, there is one scenario where I have not been able to figure out a way of controlling the layout through automation. Namely, I cannot find a way to tell the Pivot Table that I want to keep one or more expressions on the vertical axis, but keep one or more of my dimensions on the horizontal axis. It is possible to move expressions to my axis ,but only if all of my dimensions are already on the vertical axis. In other words, I cannot mix dimensions and expressions on my vertical axis, while having a dimension on the horizontal axis. But this is possible through mouse dragging.
However, if you're looking to generate document layouts ahead of time it's possible to achieve whatever layout you desire through XML manipulation. Although this won't help you when you're trying to performing interactive chart manipulations.
That said, I've since changed my approach to QlikView, and generally avoid using VB macros whenever possible to achieve my goals. Instead, it's better to drive everything by way of expressions. In some cases you can achieve your goals by pre-creating chart layouts and then using Show/Hide Expressions. However, there are limits to this approach.
thanks for your fast answer. Iam trying to save a pivot table layout, my users are building via Mouse Drag and Drop. (e.g. expressions on vertical axis, dimensions on horizontal). I save all Dimensions and Expressions in QV Variables with the help of a QV Bookmark. Whenever my users are reloading this Bookmark, their saved Pivot Table is rebuilded via VB Macros. Now i am searching for a way, so save a Drag and Drop Layout in a Bookmark.
