Получение дочерних элементов в PivotTable

[motorway]

В том-то и дело, что если попробовать сделать вывод окна с arr(1), то оно не выходит. Массив - нормально, так подходит. Только он получается пустой

[KL (XL)]

Все разобрался - не совсем так понял задачу, да и пример отличается от картинки. Сейчас подумаю, как сделать.

[motorway]

Я написал ранее, как это в принципе можно сделать, но сейчас думаю, что в случае большого числа элементов может быть проблема знатная... Даже OLAP тормозит, когда достает такое число элементов

[KL (XL)]

Сообщение от motorway

В том-то и дело, что если попробовать сделать вывод окна с arr(1), то оно не выходит. Массив - нормально, так подходит. Только он получается пустой

Вот ваш файл-пример с кодом. Знаю, что не то, но массив не пустой.
В моем вариант выбирать надо ячейку в первом столбце, где лейблы-числа

[motorway]

Нет, что-то не получается... наверно, надо придумать какой-то вариант, в котором будет учитываться возможность большого кол-ва ячеек, и он будет нормально работать при этом.
А нет ли вообще способа программно получать эти коды из таблицы фактов, не помещая их в сводную таблицу? Если там >10000, они уже не умещаются.

[motorway]

Пока что сделал примерное решение, довольно корявое, но общий смысл понятен:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

u = ActiveCell.PivotCell.RowItems.Count
v = ActiveCell.Row
For i = v + 1 To 100
ur = Cells(i, ActiveCell.Column).PivotCell.RowItems.Count
If (ur = 3) Then ' тут проверка на последний уровень вложенности должна быть
Cells(i, 1).Value = Cells(i, ActiveCell.Column).PivotCell.RowItems.Item(3).Caption
Else
If (ur <= u) Then Exit For
End If

Next i

End Sub

Теперь надо понять, что делать, когда слагаемых много (>10000)

[KL (XL)]

Не так страшен черт... Как-то так. Не думаю, что будет очень медленно даже при 10000 строк.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim pt As PivotTable
    Dim r As Range, ra As Range, rc As Range
    Dim i As Long, k As Long
    Dim arr()
    Dim msg As String
    
    Set pt = ActiveSheet.PivotTables(1)
    
    If Intersect(ActiveCell, pt.DataBodyRange) Is Nothing Then Exit Sub
    
    ReDim arr(0)
    With pt.RowRange
        Set r = .Offset(1).Resize(.Count - 1)
    End With
    k = pt.RowFields.Count
    
    On Error GoTo errSkip1
    Set ra = ActiveCell
    For i = 1 To r.Count
        Set rc = ra.Offset(-i)
        With rc.PivotCell.RowItems
            If .Count = ra.PivotCell.RowItems.Count Then
                If .Count = k And rc <> "" Then
                    msg = msg & rc & Chr(13)
                    arr(UBound(arr)) = rc
                    ReDim Preserve arr(UBound(arr) + 1)
                End If
            Else
                Exit For
            End If
        End With
    Next i
errSkip1:
    On Error GoTo errSkip2
    For i = 0 To r.Count
        Set rc = ra.Offset(i)
        With rc.PivotCell.RowItems
            If .Count >= ra.PivotCell.RowItems.Count Then
                If .Count = k And rc <> "" Then
                    msg = msg & rc & Chr(13)
                    arr(UBound(arr)) = rc
                    ReDim Preserve arr(UBound(arr) + 1)
                End If
            Else
                Exit For
            End If
        End With
    Next i
errSkip2:
    If UBound(arr) > 0 Then
        ReDim Preserve arr(UBound(arr) - 1)
        MsgBox msg
    End If
End Sub

Очередность значений не выдерживается, если активная ячейка находится на самом нижнем иерархическом уровне и не на самом первом непустом значении

[motorway]

Спасибо, попробую. Но проблема в том, что сам Эксел не выводит в таблицу более 10000 значений из источника. Если же как-то получать значения без вывода в таблицу, обращаясь к какому-то свойству, например, или результатам запроса, то можно получить элементы и в случае 100000 значений.

[KL (XL)]

При наличии источника данных, ничто не мешает использовать ADO для подключения к данным напрямую и создания конкретных рекордсетов на основании динамически создаваемых запросов. Там конечно урезанный SQL, но есть, с чем поиграть :-) Насколько я понимаю, делать запросы к PivotCache - гиблое дело.

[motorway]

У меня данные в таблице берутся из PowerPivot, а в него попадают из БД. Можно, конечно, подключаться к самой БД, но вроде бы тогда теряется преимущество в скорости, которое рекламируется.
Например, полное заполнение таблицы из 644621 строки фактов в Экселе занимает около минуты. Сначала уровень вложенности - Код магазина, потом Код товара, а потом уже "листья" в виде ключа в таблице продаж. Достаточно долго. В результате вся таблица занимает 958457 строк. А в самом PowerPivot получение таблицы обычно быстрее происходит. Ну и фильтрация.