logo

14 июн. 2010 г.

BIP: Excel-шаблоны разметки

Нам нужен Excel!!!
Я постоянно слышу это от пользователей Oracle BI.
Уверен, вы - тоже.



И нам приходилось использовать существующие решения в BI Publisher для предоставления нашим пользователям Excel-отчетов:
Чаще всего использование RTF-шаблонов с выводом в Эксель. Со всеми сопутствующими багами. Ведь такой метод использует обходной маневр – способность Экселя открывать mht-файлы.




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

Частично выручал метод создания отчетов с помощью XSL-шаблонов разметки, но и там большинство "нельзя" оставалось, плюс мы должны были регламентами использования ограничивать браузер (только IE), версию MS Office (2003 и выше), чтобы полученный отчет прозрачно для пользователя открывался в Экселе.

Теперь об этом можно забыть (шучу!).
Уже длительное время команда Oracle, занимающаяся ядром BIPublisher, готовит новый тип шаблонов разметки – XLS.

Прочитать об этом можно на блоге Тима Декстера

В чем-то данная статься является вольным переводом сообщения Тима, в чем-то – дополнением.

Итак, для использования XLS-шаблонов вам в первую очередь потребуется обновить BIPublisher. Для этого скачайте с металинка патч 9546699

Возьмите отформатированный в Экселе шаблон вашего отчета.
Я покажу пример на основе отчета "Balance Letter" из стандартной поставки BIPublisher.

Создайте в нем новый лист с именем XDO_METADATA, на котором поместите общую информацию о шаблоне.
(Не забудьте скрыть затем этот лист.)


На главном листе шаблона выделите область, содержащую повторяющиеся данные по группе G_CURRENCY. В поле именованных диапазонов задайте имя области – XDO_GROUP_?G_CURRENCY?.
G_CURRENCY – это элемент XML-файла, представляющий основную группировку выводимых данных.


Выделите группу низшего уровня. Аналогично, задайте ей имя – XDO_GROUP_?G_INVOICES?



Теперь для каждой ячейки низшей группы задайте имя, соответствующее элементу XML-файла данных.




Если вы где-то ошиблись в имени, либо хотите увидеть все ваши mapping’и:





На данном этапе будем считать наш шаблон разметки завершенным.
Выделенные области пока оставим без изменения.



Загрузим созданный шаблон разметки через интерфейс BIPublisher (к сожалению, пока нет возможности проверять шаблоны локально, как это сделано для RTF-шаблонов – через Desktop Template Builder).



Запустим отчет на выполнение. Полученный файл является «чистым» Эксель-файлом со всеми вытекающими последствиями.



Теперь попробуем понять - как это все работает.
Откроем системную временную папку – у меня это "C:\Windows\TEMP".
После запуска отчета с Эксель-шаблоном появятся 2 новых файла:



Первый файл – это XSL, полученный из подготовленного Excel-файла. Именно в нем содержатся все определенные нами группировки.



Второй файл – XML с данными, полученный XSLT-трансформацией XML-файла данных и XSL-файла с предыдущей картинки.


Видим, что остались видны все имена именованных диапазонов исходного шаблона разметки. Именно по ним в итоге полученный XML-файл преобразуется в Excel.

Итак, раз ничего сверхъестественного в данном механизме нет – можно попробовать использовать привычные XSL-конструкции.
Попробуем реализовать вывод информации, которая получается конкатенацией статического текста и динамических данных.
(Если делать это через механизм mapping’а, то придется мудрить с разбиением/схлапыванием ячеек).

На скрытом листе XDO_METADATA определим новую переменную, значение которой получается конкатенацией статического текста и динамического значения кода валюты и суммы по ней.


Присвоим первой ячейке в строке имя, равное определенной нами переменной.





P.S. Официальной документации по данной технологии еще нет. Но уже сейчас понятно, что возможности по созданию Excel-отчетов – замечательные.
Раз есть возможность использования макросов, то можно создавать графики.
Есть возможность динамического разбиения данных по листам Эксель-файла (используя bursting).
Есть возможность вывода данных в таблицах среза.
В следующих статьях я постараюсь раскрыть эти возможности.

P.P.S. Ссылка на архив с примером.


23 комментария:

  1. К сожалению, допустил глупую ошибку с "приаттаченным" Excel-шаблоном: на доп. листе метаданных есть ячейка "Last Modified Date:", которой соответствует функция "=СЕГОДНЯ()".
    Тем самым при каждом открытии отчета, сформированного шаблоном, он автоматически меняется, и вам каждый раз при его закрытии придется видеть окошко с вопросом "Сохранить изменения?". Неприятно, не правда ли.
    Для решения "проблемки" просто укажите конкретную дату последней правки, без всяких функций.

    ОтветитьУдалить
  2. Для возможности использования excel-шаблона нужен BIP 10.1.3.4.1 + Патч 9546699

    ОтветитьУдалить
  3. Здравствуйте, Jack Carver! Можно в такие шаблоны добавлять графики?

    ОтветитьУдалить
  4. Здравствуйте, в Excel-шаблонах существует возможность создания VBA-макросов, с помощью которых и можно рисовать графики.
    Пример - http://obi2ru.blogspot.ru/2012/11/bipublisher-vba-pivot-table.html

    ОтветитьУдалить
  5. Подскажите еще, пожалуйста, как можно добавить namespace в этот шаблон. Например, в rtf шаблоне достаточно добавить строчку "", здесь же так не получается...

    ОтветитьУдалить
    Ответы
    1. имелось в виду добавить строчку

      Удалить
    2. упс... не получается описание namespace сюда вставить....

      Удалить
    3. Добавлю, что использование XDO_EXT?n? не помогает. При добавлении поля типа select=".//x:row", появляется сообщение об ошибке "namespace x used but not declared"

      Удалить
  6. Этот комментарий был удален автором.

    ОтветитьУдалить
  7. Извините за долгое молчание! Проблема еще актуальна?

    ОтветитьУдалить
    Ответы
    1. Ничего страшного ). В общем, актуальна. В том конкретном случае добавили внешний обработчик, который убирает объявление namespace из xml с данными, перед тем, как Publisher генерирует из него отчет, в этом случае описывать его в шаблоне уже не нужно, но мне такое решение не нравится - 2 шага вместо одного.

      Удалить
  8. Попробуйте на листе XDO_METADATA добавить строчку в раздел Data Constraints
    в первой ячейке которой будет:
    XDO_NS_?

    а во второй:

    ОтветитьУдалить
    Ответы
    1. Не видно, что нужно ставить во вторую ячейку... В первую я поставил XDO_NS_1 (XDO_NS_? вызывает ошибку), во вторую - <_?namespace:xmlns:x="бла бла бла"?_>, но в этом случае также ошибка "Namespace prefix 'x' used but not declared"

      Удалить
  9. Извините, во второй ячейке должно быть:
    <?namespace:xmlns:x="http://www.oracle.com/x"?>

    Вы не могли бы прислать мне ваш шаблон? (и тестовые данные в виде XML)

    ОтветитьУдалить
    Ответы
    1. Спасибо за помощь, отправил данные на почту

      Удалить
  10. Ответил вам лично.
    На будущее коллегам: если у вас вариант, когда XML файл с данными содержит "пустое" пространство имен, то рекомендую применять подход, описанный у Т.Декстера - https://blogs.oracle.com/xmlpublisher/entry/empty_namespaces

    Т.е. определять в самом начале раздела DataConstraints листа XDO_METADATA
    фиктивный неймспейс
    XDO_NS_? <?namespace:xmlns:x="http://www.oracle.com/x"?>
    и затем все XSL-ссылки на группы/узлы оформлять через этот фиктивный неймспейс:
    <xsl:value-of select="x:my_field"/>

    ОтветитьУдалить
  11. Здравствуйте.

    А как можно реализовать группировку данных, в рамках одной ячейки, если использовать excel-шаблон, ну то есть запихнуть for-each в одну ячейку (например чтобы вывести имена всех кого-то там в одну ячейку)?

    ОтветитьУдалить
    Ответы
    1. Здравствуйте, конечно, можно!
      Для это на листе XDO_METADATA определите новую XDO-переменную. Например, XDO_?TEST?
      У нее задайте следующую XSL-"формулу":
      <xsl:for-each select="//G_DEPT"><xsl:value-of select="./DEPARTMENT_NAME"/><xsl:if test="not(position() = last())">, </xsl:if></xsl:for-each>

      Где G_DEPT - группа, а DEPARTMENT_NAME узел группы, который нужно конкатенировать.

      Удалить
    2. Сергей, спасибо большое за ответ!

      Я подозревал что необходимо будет использовать xsl, с котором я совершенно не знаком. А можете привести еще пример, как будет выглядеть двойная группировка, ну то есть вложенная одна в другую, и условия внутри группировок на поля из хмл. Если вы приведете ссылку, где можно почитать об использовании xsl для таких более менее дефолтных кейсов при изготовлении шаблонов, моей благодарности просто не будет предела:)

      Удалить
    3. Будет проще, если Вы со мной свяжетесь по почте - пришлите пример того, что хотите получить, а также что уже есть.

      Удалить
  12. Добрый день!
    Подскажите, а как корректно обработать такую ситуацию, когда необходимо вывести сумму значений по элементу, который не у всех узлов заполнен(возвращается пустое значение).
    Вывод результата осуществляется в ячейку с числовым типом.
    Хотелось бы решить именно при помощи xsl, но не очень понимаю с чем его есть.
    Если все получаемые значения слагаемых заполнены, то помогает , но если встречается пустое значение, то функция, естественно, валится.

    ОтветитьУдалить
  13. А как должно выглядеть объявление двух и более пространств имён?
    XDO_NS_? - это один. А второй как обозвать?

    ОтветитьУдалить