logo

20 июн. 2015 г.

BI Publisher 11g: Excel шаблон + native pivot table

Маленький рассказ о том, как реализовать средствами BI Publisher и его Excel-шаблонов разметки
вывод данных в виде "родной" кросс-таблицы.




Все достаточно просто и очевидно.
Сложность лишь в одном моменте - определении диапазона с данными, на базе которого строится кросс-таблица Excel. Постоянно забываю эту формулу, поэтому можно считать эту статью моим "узелком на память".

Идея в следующем:

1. Исходные данные, полученные например SQL-запросом, выводятся в виде плоской таблицы на скрытый лист Excel-шаблона.


2. На доступном пользователям листе Excel-шаблона определяем новую таблицу среза, базирующуюся на диапазоне данных скрытого листа.


3. Очевидно, что диапазон данных может меняться от запуска к запуску отчета. При различных параметрах отчета результаты в виде плоской таблицы на скрытом листе будут меняться. А значит нам нужно использовать не абсолютную ссылку на диапазон ячеек с данными, а относительную. Относительно именованного диапазона группы, в моем случае - XDO_GROUP_?ROW?.
Но если мы зададим источник данных для кросс-таблицы равным именованному диапазону XDO_GROUP_?ROW? - ничего хорошего не выйдет.



4. Проблема в том, что отсутствует строка заголовков. Без нее получается полная ерунда!
И решение в том, чтобы использовать следующую формулу определения итогового диапазона данных для кросс-таблицы, включающего в себя строку заголовков:
=СМЕЩ(XDO_GROUP_?ROW?,-1,0,СЧЁТЗ(XDO_?Year?)+1)

Т.е. мы задаем диапазон с помощью формул Excel.
У меня используется русифицированная версия, для английской - используйте =OFFSET(XDO_GROUP_?ROW?,-1,0,COUNTA(XDO_?Year?)+1)

Смысл в том, что мы смещаемся на одну строку вверх относительно исходного именованного диапазона с группировкой данных из XML. Плюс задаем кол-во строк нового диапазона как кол-во строк в одном из столбцов плюс единица.
Важный момент - подсчет количества строк в исходном диапазоне осуществляется с помощью функции СЧЕТЗ, которая подсчитывает непустые значения. Поэтому выбирайте для подсчета тот столбец (XDO_?...?-маппинг), значения которого не могут быть пустыми.

5. Не забудьте указать в свойствах кросс-таблицы пункт "Обновить при открытии файла", чтобы таблица динамически обновлялась при каждом запуске отчета и открытии его результатов.



P.S. Выкладываю пример Excel-шаблона и XML-файл с данными.

6 комментариев:

  1. Серегей, доброго дня!
    Идея нравится, особенно бизнесу - видеть отчёт сразу в Excel с его возможностями.
    Подскажите какова производительность метода?
    У нас детальных данных более 100тыс записей. И уже на 10тыс BIP думает 3-5 мин, на 100 тыс в районе получаса. При том, что если выбрать вывод в csv, то отрабатывает за 5с (селект специально для тестирования простой). Где "узкое" место? на этапе формирования xml, или же уже при вставке результатов xml на лист rtf или Excel?

    ОтветитьУдалить
  2. Здравствуйте, Илья!
    Я бы выделил основные этапы процесса получения отчета пользователем:
    1) генерация данных (обычно это выполнение SQL-запроса) в виде XML;
    2) трансформация XML файла с данными в нужный формат (это обычно XSL-трансформация);
    3) в случае, описанном выше, отрисовка результатов средствами Excel

    Если при выводе данных в CSV-формат все отрабатывает моментально, то первый этап у вас отрабатывает эффективно.
    Третий этап к вашему случаю не относится (к тому же отобразить таблицу среза на 100 тысячах - для Экселя на современных десктопах не проблема).
    Очевидно, что затык происходит на втором этапе. Чтобы дать конкретную рекомендацию - советую выслать мне пример вашего шаблона (RTF/Excel) и пример данных в XML формате.

    ОтветитьУдалить
  3. Андрей Малюков16 декабря 2015 г., 6:30

    Сергей, доброго времени суток!
    Нижайшая просьба - подскажите, возможно ли неким хитрым способом решить такую проблему:
    Есть критерий ТипПродаж(Интернет\Прочие) и две меры - СуммаФакт и СуммаПлан.
    СуммаПлан при этом не устанавливается отдельно по Интернет\Прочие, только общий план продаж.
    Если строить анализ (СуммаФакт, СуммаПлан) по критерию ТипПродаж, то в колонках Интернет и Прочие СуммаПлан будет равна нулю, реальная сумма будет только в итогах критерия. Внешний вид анализа получается некрасивый. Возможно ли как-то убрать колонки Интернет и Прочие для меры СуммаПлан, оставив для нее только итог?
    С картинками вопрос можно посмотреть здесь:
    https://community.oracle.com/thread/3877188
    Был бы очень Вам признателен...

    ОтветитьУдалить
    Ответы
    1. Андрей, добрый день!
      Попробуйте переместить столбец "СуммаПлан" из блока показателей таблицы среза в блок столбцов. Это лучшее. что можно сделать в вашем случае...

      Удалить
    2. Андрей Малюков16 декабря 2015 г., 15:05

      Сергей, еще раз благодарю за помощь!

      Удалить