Hi,

I think i posted this in the wrong forum...here we go again...

I've got some VBA code that basically builds drillthrough MDX from a pivot table connected to an OLAP cube.
The problem is that it can handle mutliple page fields but cannot handle multiple page field items. IE: Mutlpile items selected in the page field dropdown:

Heres the code (I've highlited the offending code in red):

' Given a reference to a PivotTable data cell this function
' will create the appropriate MDX drillthrough statement to
' find the detailed records.
Private Function CreateDrillMdx(oPTCell As PivotCell) As String
Dim sDrillMdx As String
Dim i As Integer
Dim iAxisNum As Integer
Dim iRowCol As Integer
Dim oPTList As PivotItemList

' The start of the query
sDrillMdx = "DRILLTHROUGH MAXROWS 1000 SELECT "

' Determine the dimension members on the row and column headers matching this cell
iAxisNum = 0
For iRowCol = 1 To 2

If iRowCol = 1 Then
Set oPTList = oPTCell.RowItems
Else
Set oPTList = oPTCell.ColumnItems
End If

Dim sDimName As String ' Unique name of dimension
Dim sMemberName As String ' Unique name of dimension member

' Step through each of the header cells for this axis (rows or columns)
' (If we're working on a row header then we're stepping through columns)
For i = 1 To oPTList.Count
' If the previous label showed a member from a different dimension
' then add that member to the drillthrough list because it is the lowest
' (hierarchically) member displayed from that dimension
If (i > 1) And (sDimName <> oPTList(i).Parent.CubeField.Name) Then
sDrillMdx = sDrillMdx & _
"{" & sMemberName & "} ON " & iAxisNum & ", "

iAxisNum = iAxisNum + 1
End If

sDimName = oPTList(i).Parent.CubeField.Name
sMemberName = oPTList(i)

' If this is the last label displayed then be sure to include that member
If (i = oPTList.Count) Then
sDrillMdx = sDrillMdx & _
"{" & sMemberName & "} ON " & iAxisNum & ", "

iAxisNum = iAxisNum + 1
End If

Next i

Next iRowCol

Dim oPT As PivotTable
Dim oPageFields As PivotFields

Set oPT = oPTCell.PivotTable
Set oPageFields = oPT.PageFields

' Add the member names for any paged dimensions
For i = 1 To oPageFields.Count
sDrillMdx = sDrillMdx & "{" & oPageFields(i).CurrentPageName _
& "} ON " & iAxisNum & ", "

iAxisNum = iAxisNum + 1
Next i

' Trim off the extra comma left by the last item appended to the query
sDrillMdx = Left$(sDrillMdx, Len(sDrillMdx) - 2)

' Now add the cube name in the FROM clause
sDrillMdx = sDrillMdx & " FROM [" & oPT.PivotCache.CommandText & "]"

' Return the MDX statement
CreateDrillMdx = sDrillMdx
End Function



any help would be appreciated.
Thanks