Для выполнения сложных динамических операций форматирования рабочей книги, построенной на базе BEx-запросов, применяют макросы, написанные на языке VBA. Макросы хранятся в рабочей книге.

«Точкой входа» для обработки выходных результатов запросов, обычно выступает макрос «CallBack», имя которого прописывается в BEx-свойствах рабочей книги на вкладке «Exits» (Рис.1)

Рис. 1 Вкладка «Exits» рабочей книги

Вызов макроса «CallBack» происходит только в случае, когда на вкладке «Общее» свойств рабочей книги установлен индикатор «Обновлять рабочую книгу при открытии». Вызов будет происходить столько раз, сколько элементов «Grid» находится в рабочей книге. При очередном вызове в макрос передается массив параметров «ParamArray» состоящий из трех элементов (перечислены ниже по их индексу):

0.     – имя датапровайдера

1.     – Range объект Excel ссылающийся на область вывода результатов запроса

2.     – имя элемента Grid

В версии SAP BW 7.x невозможно задать порядок обновления запросов в рабочей книге, но часто необходимо объединить результаты вывода в определенном порядке. Поэтому при обновлении очередного Grid мы будем считывать только его обновленные параметры и лишь при обновлении последнего Grid мы приступим к формированию и форматированию отчета, имея для этого все необходимые данные.

Для хранения параметров (техническое имя датапровайдера, адрес области данных, адрес боковика и т.д.) мы будем использовать коллекции на основе собственного типа, определенного как класс.

Коллекции VBA - это упорядоченные наборы элементов, представляющие собой единое целое. Любая коллекция VBA - экземпляр класса Collection. Все элементы коллекции (члены) представлены ссылками на фактические объекты. Это позволяет объединять в коллекцию данные различных типов. Коллекция - это списочная структура, поддерживающая операции создания коллекции как таковой, добавления и удаления элементов, получения элемента по его индексу.

В коллекцию можно добавлять элементы методом Add, удалять ранее добавленные элементы методом Remove и обращаться к элементам методом Item, в т.ч. и итеративно, например, с использованием инструкции For Each...Next. Все коллекции имеют свойства Count и Item, а также метод Add. Свойство Count возвращает количество элементов. Свойство Item возвращает определенный компонент коллекции.

Чтобы считать или вычислить некоторые из необходимых параметров придется обратиться к свойствам и методам объекта BExApplication. Для этого необходимо подключить API библиотеку и «прописать» функцию, получающую ссылку на актуальный объект BExApplication. Сделать это можно как вручную, так и автоматически с помощью нажатия кнопки «Добавить макросы» в свойствах рабочей книги (Рис.2).

Рис. 2 Настройки рабочей книги

Начнем написание нашего макроса «CallBack», вспомогательных процедур и функций:

1.     Переходим в редактор VBA (Alt+F11) и добавляем модуль. Называем  его, например, «Main» (Рис.3)

Рис. 3 Добавление модуля Main

2.     Добавляем ссылку на библиотеку BEx Analyzer API.

3.     Добавляем класс, в котором будут описаны свойства элементов коллекции, назовем его DPItem.

4.     Описываем свойства элементов коллекции

Public DPName        As String – Техническое имя датапровайдера

Public Query         As String – Техническое имя запроса

Public dataSheetName As String – Имя листа с данными

Public sideHeaderAddress  As String – адрес области с "боковиком", "урезанный" до одного столбца

Public sideHeaderAddressFull   As String – адрес области с "боковиком", "полный"

Public dataAddress   As String – адрес области с данными

Public localHeaderAddress As String – адрес заголовка столбцов. включая строку с единицами измерения

Public isEmpty       As Boolean – Есть данные в провайдере данных

Public isEmptyData   As Boolean – Есть данные на показателях в провайдере данных

Public isUpdated     As Boolean – Метаданные провайдера обновлены

Public DPOffsetX     As Integer – X - смещение первой ячейки датапровайдера относительно начала координат листа

Public DPOffsetY     As Integer – Y - смещение первой ячейки датапровайдера относительно начала координат листа

Public dataOffsetX   As Integer – Х - смещение первой ячейки с данными относительно начала датапровайдера

Public dataOffsetY   As Integer – Y - смещение первой ячейки с данными относительно начала датапровайдера

Public dataColumnsCount   As Integer – Количество столбцов с данными в датапровайдере

Public dataRowsCount As Long – Количество строк с данными в датапровайдере

5.     Далее работаем в модуле Main. Вставляем оператор

Option Explicit – требует явного объявления всех переменных в файле.

6.     Описываем глобальные для модуля константы и переменные

Private Const DebugModeOn = True – false отключает возможность взаимодействовать с книгой во время работы макроса

Public GridCounter As Integer – счетчик обновленных Grid-ов

Private dpCollection As New Collection – коллекция с параметрами датапровайдеров

7.     Описываем процедуру CallBack

Sub BExOnRefresh(ParamArray varname())

End Sub

8.     В теле процедуры вставляем следующий код:

    Dim GC As Integer

    If Not DebugModeOn Then – Если режим отладки отключен, то для ускорения работы макроса в текущей инстанции приложения отключаются обновление экрана, интерактивность (ответные действия приложения на действия пользователя), обработчики событий и отображение предупреждений

        Application.ScreenUpdating = False

        Application.Interactive = False

        Application.EnableEvents = False

        Application.DisplayAlerts = False

    End If

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

    ' обновление первого провайдера данных

    If (GridCounter = 0) Then

        Set dpCollection = New Collection

        'подсчёт количества GRID в static функции

        GC = GridCount(True, BEx)

    End If

Элементы в коллекции должны иметь уникальное имя. В этом качестве мы будем использовать имя датапровайдера. Производим проверку на наличие элемента в коллекции с помощью функции ExistInCollection (т.к. в коллекции стандартно не предусмотрен метод Exist, то мы написали свою булевскую функцию проверки существования элемента в коллекции). Если элемента с проверяемым именем в коллекции нет, то создаем новый с типом ссылающимся на наш класс DPItem, таким образом элемент унаследует все свойства класса.

    If Not (ExistInCollection(dpCollection, varname(0))) Then

        dpCollection.Add New DPItem, varname(0)

Заполняем свойства текущего элемента значениями.

        dpCollection.Item(varname(0)).DPName = varname(0)

        dpCollection.Item(varname(0)).isEmpty = True

Заполнение остальных свойств мы вынесли в отдельную процедуру FillDPCollection.

        ' заполнение оставшихся атрибутов текущего провайдера данных в коллекции

        Call FillDPCollection(varname(0), varname(1), varname(2))

        dpCollection.Item(varname(0)).isUpdated = True

    End If

Увеличиваем счетчик обновленных Grid-ов

    GridCounter = GridCounter + 1

Если значение счетчика равно количеству Grid-ов в рабочей книге, то можно запускать процедуру формирования и форматирования отчета.

    ' обновление последнего провайдера данных

    If (GridCounter = GridCount) Then

'

Форматируем отчет. Задать порядок следования запросов в итоговом отчете, правила «сцепки» и форматирования можно на специально отведенном скрытом листе или листе-шаблоне.

'        Call Set_format

Подготовка данных к следующему обновлению рабочей книги

        GridCounter = 0

        Range("A1").Activate

        Application.CutCopyMode = False

    End If

Если режим отладки отключен, то включаем все ранее отключенные свойства, обеспечивающие взаимодействие приложения с пользователем.

    If Not DebugModeOn Then

        Application.ScreenUpdating = True

        Application.Interactive = True

        Application.EnableEvents = True

        Application.DisplayAlerts = True

    End If

9.   Создаем статическую функцию для подсчета количества Grid-ов. Для инициализации необходимо передать опциональные параметры toZero = True и myBEx = Bex (типа BExApplication).

Static Function GridCount(Optional toZero As Boolean, Optional ByRef myBEx As Variant) As Integer

    Dim locGridCount As Integer

    Dim myBExItem As Object

    If toZero Then

        locGridCount = 0

        For Each myBExItem In myBEx.Items

            If myBExItem.ToString Like "*BExItemGrid*" Then

                locGridCount = locGridCount + 1

            End If

        Next

    End If

    GridCount = locGridCount

    Set myBExItem = Nothing

End Function

10.  Создаем булевскую функцию ExistInCollection. Если элемент с именем itemName существует в коллекции, то функция возвращает значение «Истина».

Private Function ExistInCollection(fCollection As Collection, itemName As Variant) As Boolean

    On Error GoTo ErrHandler

    fCollection.Item (itemName)

    ExistInCollection = True

    Exit Function

ErrHandler:

    ExistInCollection = False

End Function

11. Создаем процедуру для заполнения свойств элемента коллекции FillDPCollection. В качестве входных параметров выступает массив параметров идентичный массиву CallBack процедуры.

Private Sub FillDPCollection(ParamArray varname())

    Dim dataRange        As Range

    Dim sideHeaderRange  As Range

    Dim localHeaderRange As Range

    Dim DimS             As Object

    Dim DItem            As BExDimension

    Dim ScF              As Integer

С помощью With...End With можно выполнить ряд выписок в указанном объекте несколько раз без указания имени объекта. Внутри блока выписки With можно указать член объекта, начиная с точки, если объект With выписки предшествовал его.

Начинаем обработку элемента коллекции.

        With dpCollection.Item(varname(0))

Получаем техническое имя запроса из свойств датапровайдера хранящихся в объекте BExApplication

            .Query = BEx.DataProviders(varname(0)).Query

Определение начальной строки и столбца ячейки с данными относительно начала области вывода датапровайдера.

            .dataOffsetX = BEx.DataProviders(varname(0)).Result.Grid.firstdatacell.X

            .dataOffsetY = BEx.DataProviders(varname(0)).Result.Grid.firstdatacell.Y

Определение начальной строки и столбца области вывода датапровайдера относительно начала координат листа

            .DPOffsetX = varname(1).Column

            .DPOffsetY = varname(1).Row

            .isEmpty = False

Если данные в датапровайдере есть, данные - определение количества строк и столбцов с данными в таблице с результатом. Если значения свойств dataOffsetY и dataOffsetX >0, то либо датапровайдер не содержит данных, либо запрос не подержит показателей, например, построен на признаке и выводит список основных данных. Последняя ситуация будет обработана ниже:

           If .dataOffsetY > 0 Then

                .dataColumnsCount = varname(1).Columns.Count - .dataOffsetX

                .dataRowsCount = varname(1).Rows.Count - .dataOffsetY

                .isEmptyData = False

            Else

Отдельная обработка для датапровайдеров не содержащих показатели:

                .dataColumnsCount = varname(1).Columns.Count

                .dataRowsCount = varname(1).Rows.Count - 1

                .isEmptyData = True

                On Error Resume Next

                Set dataRange = varname(1).Offset(1, 0).Resize(varname(1).Rows.Count - 1)

                .dataAddress = dataRange.Address

                If Err.Number <> 0 Then

                    .isEmpty = True

                    On Error GoTo 0

                End If

            End If

Определение имени листа с данными:

            .dataSheetName = varname(1).Worksheet.Name

            If Not .isEmptyData Then

Определение области с данными:

                Set dataRange = Range(Cells(.DPOffsetY + .dataOffsetY, .DPOffsetX + .dataOffsetX), _

                                        Cells(.DPOffsetY + .dataOffsetY + .dataRowsCount - 1, .DPOffsetX + .dataOffsetX + .dataColumnsCount - 1))

Определение области с "боковиком":

                Set sideHeaderRange = Range(Cells(.DPOffsetY + .dataOffsetY, .DPOffsetX), _

                                        Cells(.DPOffsetY + .dataOffsetY + .dataRowsCount - 1, .DPOffsetX + .dataOffsetX - 1))

Определение, есть ли вывод коэффициента масштабирования:

                If BEx.DataProviders(varname(0)).Request.Properties.DisplayScalingFactor Then

                    Set DimS = BEx.DataProviders(varname(0)).Request.Dimensions

                    For Each DItem In DimS

Проверяем содержит ли измерение показатели:

                        If DItem.ContainsKeyfigure And DItem.Axis = BExAxis_Columns Then

                            ScF = 1

                            Exit For

                        End If

                Next

                End If

Определение области заголовка столбцов, включая строку с единицами измерения:

                Set localHeaderRange = Range(Cells(.DPOffsetY, .DPOffsetX + .dataOffsetX), _

                                        Cells(.DPOffsetY + .dataOffsetY - 1 - ScF, .DPOffsetX + .dataOffsetX + .dataColumnsCount - 1))

Запись адресов найденных областей в свойства элемента коллекции:

                .dataAddress = dataRange.Address

                .sideHeaderAddressFull = sideHeaderRange.Address

                Set sideHeaderRange = sideHeaderRange.Resize(sideHeaderRange.Rows.Count, 1)