Programmatically exclude page items in olap pivot

Question

I have a pivot table on an olap cube. I can go into a page field and manually deselect multiple items. How can I do this in VBA based on a list of items I need excluded? (n.b. I do not have a corrresponding list of items I need included)

I know how to exclude these items in other ways, by altering the underlying query for example. I specifically want to replicate the user action of deselecting items in the pivot.

Answer 1

I apologize for this example being in C#, but I really don't know enough VBA to translate it (perhaps someone can edit this entry and add it below).

Are you refering to something like this?

((MOE.PivotField)pivotTableObject.PivotFields("[NAME]")).Delete();

Where MOE is the Microsoft.Office.Interop.Excel namespace and [NAME] is the name of the field you want to remove

Answer 2

I found one not wholly satisfactory solution. In a seperate MDX query I retrieved all the members of the dimension corresponding to the page field. I also built a dictionary of the items to exclude. I then loop through the members like so:

PivotField.CubeField.EnableMultiplePageItems = True
firstTime = True
For Each member In dimensionMembers
    If Not HiddenMembers.Exists(member) Then
        'firstTime = true is the equivalent of unchecking 
        ' the root node of the items treeview
        PivotField.CubeField.AddPageItem "[Dimension].[" & member & "]", firstTime
        firstTime = False
    End If
Next

I say unsatisfactory because each call to AddPageItem triggers a query to Analysis Server making it impractically slow. And it just feels wrong.

Answer 3

You do not have to run an MDX query to list the members of a dimension, you can look at the properties of the cube object in VBA. Start with this and see where it gets you!

Set oCat = New ADOMD.Catalog

loop through this for example: oCat.CubeDefs(sCube).Dimensions(3).Hierarchies(0).Levels(2).Members(i)