What I'm trying to do is the following: I want to be able to dynamically drill a pivot by adding and removing dimensions. But I need a way for the user to choose the dimension to breakdown the pivot by and the order of the dimensions. To that end, I have a data island that lists the dimensions to drill into, the field name of the dimension and the drill order.
I have the macro able to trigger when an option in the listbox is checked (using Windows checkmark box style). The macro sets all unselected dimensions' drill order to 0 and then attempts to add 1 to the drill order of all selected fields. The theory is that a selected field increments in the order of its selection. So, the first field to be selected will always have the highest Drill Order. The last, the lowest.
' Set Drill Order of unselected fields to 0
set p = ActiveDocument.Fields("DrillField").GetAlternativeValues
for i = 0 to p.Count-1
set Result = ActiveDocument.DynamicUpdateCommand("UPDATE * SET DrillOrder = 0 WHERE DrillField='"&p.Item(i).Text&"'")
' Increase Drill order for all selected fields
set s = ActiveDocument.Fields("DrillField").GetSelectedValues
for i = 0 to s.Count-1
set Result = ActiveDocument.DynamicUpdateCommand("UPDATE * SET DrillOrder = DrillOrder + 1 WHERE DrillField='"&s.Item(i).Text&"'")
' Get Selected values in Drill Order
' Add to Pivot using AddDimension()
This really isn't working though. The second DynamicUpdate doesn't seem to work becaue the listbox which is ordered by DrillOrder and is displaying the value of the field, does not change. Can anyone tell me if there is a better way to do this?
I took the Film Example document and added my Drill table and macro to it. Hopefully, it works for everyone. There are lots of extras that could be added to it such as automatically adjusting the column width when a dimension is added or any other Dimension property that exists for that matter. I would just add a column in the DrillPaths table to store that information and then just adjust the macro to apply it when loaded.
Of course, this currently only works on a single Pivot table but it could be expanded to work on any pivot table and any set of dimensions. If anyone has any suggestions, on how to do this better, please let me know. This was my first macro which was made for my first full project in Qlikview so go easy on me.