Iterate through Pivot Items from Cube

Can really noone help me with this problem?
I think I already tried each and every possible way to get the data I need without any success.

The only way I see now is to get the information directly from the cube using a MDX query.
Is someone in (dis-)agreement? I can use every opinion I can get.

Thanks!

Is this issue related to a pivot field that is set up as a page/report filter?

I found that if the cube field's orientation is set as a page field that getting the list of item under "All" is an elusive effort.  You could try using an MDX query. Another approach would be to change the orientation of the page pivot fields to row field orientation.  From there getting the list of pivot items can be done using your existing code.  Afterwards, change the pivot field's orientation back to a page field.
For example, using your code, maybe something like the following code would work:

Dim prompt As String = ""
Dim blnSwitchOrientation As Boolean
 
Try
    For Each pf As Excel.PivotField In CType(pt.PivotFields, Excel.PivotFields)
        blnSwitchOrientation = (pf.Orientation = 3)
        If blnSwitchOrientation Then pt.CubeFields(Mid(pf.Name, InStr(pf.Name, ".") + 1)).Orientation = 1
            For Each pi As Excel.PivotItem In CType(pf.PivotItems, Excel.PivotItems)
                prompt &= pi.Name & vbCrLf
                ' pi.Value doesn't work as well 
            Next
        If blnSwitchOrientation Then pt.CubeFields(Mid(pf.Name, InStr(pf.Name, ".") + 1)).Orientation = 3
    Next
Catch ex As Exception
    ' Do nothing
Finally
    MsgBox(prompt)
End Try

Select allOpen in new window

Yes, see attached screenshot...

I have this report filter that's including countries -> regions -> cities -> ...

What I want to do is search for a certain string and output all possible hierarchy items.

For example:
If I'm searching for "York" I want (at least) these results:
-New York City
-New York State
-York (the city in the UK)

To do so I need a list of all items and then compare the name of the item with the search string, but that shouldn't be a problem.
scr-pivot.jpg

I see, okay, well give the above (http://www.experts-exchange.com/Microsoft/Development/VSTO/Q_24680252.html#25186788) modified code a try and see if that works for you.

Ah! We're getting somewhere! Thanks a lot so far!

I changed the code to what you're seeing in the attached code. I also attached a screenshot of the msgbox I recieve.

For clarification::
-"combox_search_in.Text" describes the hierarchy to search in.
-"Globals.ThisAddIn.Application.ScreenUpdating = False" hides the page -> row -> page thing from the user and speeds things up a little
-and I needed to replace some integers with the orientation objects, because I'm working with Option Strict On.

There's just one more problem:
As you can see in the attached screenshot I only get the elements on the first level of the hierarchy. Is there a way I can descend to the n-th or last level?
I assume what I need to do is expand all members at the line marked by a comment, though I haven't found out how to do that...

It's kind of weird that you can't access the fields if the orientation is set to pageField and there's no problem doing so with rowField...

Dim prompt As String = ""
Dim blnSwitchOrientation As Boolean
Globals.ThisAddIn.Application.ScreenUpdating = False
 
        Try
            For Each pf As Excel.PivotField In CType(pt.PivotFields, Excel.PivotFields)
                If pf.Name.Contains(combox_search_in.Text) Then
                    blnSwitchOrientation = (pf.Orientation = Excel.XlPivotFieldOrientation.xlPageField)
 
                    If blnSwitchOrientation Then pt.CubeFields(combox_search_in.Text).Orientation = Excel.XlPivotFieldOrientation.xlRowField
                    ' Expand all members here?
                    For Each pi As Excel.PivotItem In CType(pf.PivotItems, Excel.PivotItems)
                        prompt &= pi.Name & vbCrLf
                    Next
 
                    If blnSwitchOrientation Then pt.CubeFields(combox_search_in.Text).Orientation = Excel.XlPivotFieldOrientation.xlPageField
                End If
            Next
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            MsgBox(prompt)
        End Try
 
        Globals.ThisAddIn.Application.ScreenUpdating = True

Select allOpen in new window

scr-prompt.jpg

Repost of the code with the comment closed for better readability...

(I hope it works this time!)

Dim prompt As String = ""
Dim blnSwitchOrientation As Boolean
Globals.ThisAddIn.Application.ScreenUpdating = False
 
        Try
            For Each pf As Excel.PivotField In CType(pt.PivotFields, Excel.PivotFields)
                If pf.Name.Contains(combox_search_in.Text) Then
                    blnSwitchOrientation = (pf.Orientation = Excel.XlPivotFieldOrientation.xlPageField)
 
                    If blnSwitchOrientation Then pt.CubeFields(combox_search_in.Text).Orientation = Excel.XlPivotFieldOrientation.xlRowField
                    ' Expand all members here?'
                    For Each pi As Excel.PivotItem In CType(pf.PivotItems, Excel.PivotItems)
                        prompt &= pi.Name & vbCrLf
                    Next
 
                    If blnSwitchOrientation Then pt.CubeFields(combox_search_in.Text).Orientation = Excel.XlPivotFieldOrientation.xlPageField
                End If
            Next
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            MsgBox(prompt)
        End Try
 
        Globals.ThisAddIn.Application.ScreenUpdating = True

Select allOpen in new window

Hmm, kind of difficult for me to visualize.  Can you post a screen shot of the Customer Geography drop list in expanded view so that I can get a better idea of how it looks?

Sure, I hope this helps.
scr-hier-expanded-1.jpg
scr-hier-expanded-2.jpg

Hmm, well I don't have a cube sample that organizes data in more than one level like you have so I'm at a bit of a disadvantage.  Maybe you could get those items by seeing if the pivot item has a ChildItems.Count > 0 and if so get the value via ChildItems(x) where x is the index value.

Also, if the above does not work, then the better option may be to get the info via an MDX query.

For some reason childitems.count is always 0.

I was able to expand to the 2nd level using the code below, but the code in the comment box, which is supposed to drill down even further, messes up the whole Pivot Table and eventually crashes Excel.

I might look into finding the information I need using a MDX query as you suggested. I hope that'll work out.

For Each pi As Excel.PivotItem In CType(pf.PivotItems, Excel.PivotItems)
                        pi.DrilledDown = True
                        prompt &= pi.Name & vbCrLf
                        'For Each ci As Excel.PivotItem In CType(pi.ChildItems, Excel.PivotItems)
                            ci.DrilledDown = True
                            prompt &= ci.Name & vbCrLf
                        Next'
                    Next

Select allOpen in new window

I wasn't too hopeful with the childitems.count, but thought I'd throw it out there just in case.  You should definitely look into the MDX query option, since getting the information via the above approach does seem like a lot of pivot table manipulation and overhead.

As a side note, I think when you set the DrilledDown to True for a pivot item, the number of pivot fields in the cube field object increases and to drill down to the next level you need to look at the next pivot field and its related pivot items upon which you could drill down.  This in turn will increase the number of pivot fields in the cube field object, and so on until there is no more drill downs.  Your code is trying to drill down within the pivot item object which is why Excel is likely crashing.  Having said all of this, doing the above caused a lot of overhead on my system when I tried it (and also crashed Excel). So, the MDX query option is the way to go.  Good luck with it!

Glad to see that you found a solution...great work!