Confluence — агрегация данных таблицы

Привет, Хабр! Меня зовут Белков Евгений, я руководитель направления разработки Siebel CRM в Московском кредитном банке. Сегодня я хочу поделиться своим опытом агрегации данных таблиц в «реальные цифры».

Все мы, работая с таблицами в Confluence, хотя бы раз упирались в ограничения функционала «из коробки», особенно, когда нам нужно посчитать сумму, или реализовать какую-то простую формулу. Конечно, все можно считать в Excel, но когда у тебя есть Confluence, пользоваться Excel — не серьезно. Да, сразу отмечу, что можно:

1. Загнать Excel в Confluence. Выглядит чудовищно некрасиво, но перед выбором красиво или функционально — каждому свое!

2. Использовать Макрос Table Spreadsheet. Это какая-то своя коробочная интерпретация Excel в Confluence, она более функциональна, чем стандартные таблицы, но все так же некрасива и трудна к восприятию. На ней мы сегодня не остановимся 

3. Есть другие варианты, которые подходят под конкретные задачи. Давайте их рассмотрим!

Как сделать сумму по столбцу

Начнем с самого простого и наиболее популярного.

Задача: вывести суммирующую строку по столбцу.

Сразу переходим к частному, так как частное всегда проще к восприятию. У нас задача отобразить по командам, сколько у нас сотрудников разного профиля. Создаем такую таблицу:

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

Можно быстро перейти с помощью горячих клавиш (Ctrl+Shift+A),  выбираем там Фильтр таблиц:

В созданный фильтр переносим нашу таблицу:

Далее в настройках макроса выбираем Calculations — Подсчет итогов столбцов — и для каждого столбца выбираем операцию, которая нам нужна, для примера я использую несколько разных вариантов, например так:

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

Какие операции доступны для итогов по столбцу:

1. Count — Количество строк

2. Count Not Empty — Количество непустых строк

3. Sum — Сумма значений по всем строкам

4. Average — Среднее значение 

5. Min — Минимальное значение

6. Max — Максимальное значение

7. Multiplication — Умножение всех значений

Если оставить для какого-либо столбца пустым поле Подсчет итогов столбцов, то и итог будет пустым, операция по умолчанию не предусмотрена. То же самое можно сделать и со строками, если нужно, но тут уже выбирается одна операция, которая применится ко всем строкам.

Сводная таблица

Задача: необходимо отобразить количество сотрудников по всем командам в разрезе профиля. Создаем Макрос Сводная таблица, помещаем в него новую табличку. В нашем случае это должно выглядеть так:

Настраиваем, поля по которым будет происходить фильтрация, расчет, и какую операцию проводим с результатами. В результате видим такую табличку:

Доступные операции:

1. Count — Количество строк

2. Sum — Сумма значений по всем строкам()

3. Average — Среднее значение 

4. Min — Минимальное значение

5. Max — Максимальное значение

6. Multiplication — Умножение всех значений

7. Literal — отображает значение полей, (я могу лишь предложить ее использование когда вам нужно сделать турнирную сетку игр.)

Более подробно я разбирал тему сводных таблиц тут.

Объединение таблиц 

Из коробки Confluence в макросе Table Transformer есть три варианта объединения: Join, Union и Custom.

Предлагаю сначала перейти к примерам задач.

Join 

Задача: Есть 2 таблицы нужно их сложить так, чтобы получить в одной строке колонки из обеих таблиц. Вы спросите зачем так делать, если можно сразу сделать таблицу с необходимыми тебе колонками и будет счастье! 

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

Создаем новый макрос Table Transformer и переносим в тело макроса созданные таблицы (не более 2-х таблиц). При редактировании макроса выбираем LookUp Tables и ниже в поле Match Records by выбираем колонку, по которой произойдет соединение типа Join (сюда попадают только те поля колонки, которые присутствуют в обеих таблицах),  и сохраняем.

В результате получаем такую картину:

Union

Задача: у вас есть несколько таблиц с одинаковой структурой. На выходе вы получите одну.

Если в макросе Table Transformer вы выберете Merge Tables — то будет операция Union, то есть одна таблица добавит записи к другой, при этом состав колонок будет использовать метод Merge.

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

Custom

Задача №1: Нам необходимо видоизменить содержание таблиц, значения, состав полей или объединить более 2-х таблиц.

Тут сразу предупреждаю — Вам понадобится навык SQL-запросов.

Давайте создадим еще одну таблицу Должностей Руководителей и закинем все их в тело макроса Table Transformer. Обратите внимание, что всем таблицам внутри присваиваются алиасы T1….Tn, в зависимости от количества таблиц внутри тела макроса.

Редактируем макрос и в Custom Transformation напишем простые SELECT-объединения, сохраним и посмотрим, что у нас получится.

В результате видим такой результат:

Вроде неплохо, но не тот порядок, и хотелось бы оставить лишь руководителей и вывести колонку с суммой сотрудников. Попробуем?

Далее все изменения таблицы лишь ограничиваются вашими знаниями SQL.

Задача №2: Необходимо посчитать процент укомплектованности команды. Создаем таблицу с работающими сотрудниками и потребностями. Пишем select в макросе, в котором определяем новое поле «% Укомплектованности«, как формулу и сохраняем.

Сохраняем, и видим новое поле, которое добавилось к оригинальной таблице.

Но есть 1 нюанс, о котором  хочу вас сразу предупредить: 

При редактировании страницы вы будете видеть только оригинальную таблицу без новых полей, которые создаете в Sql-запросе. Новые колонки будут видны только при чтении сохраненной страницы. Это может вызывать некий когнитивный диссонанс у редактирующих страницу и таблицу. Обязательно помните об этом.

Примечание!

В тело макроса можно переносить не только готовые таблицы. Можно использовать, например:

1. Вставка выборки таблицы, которая ссылается на Выборку таблицы страницs Confluence. 

2. Включить страницу, желательно чтобы все работало на странице, не должно быть ничего другого, кроме таблицы. Таблица должна быть одна.

3. Сводная таблица — макрос который делает свод по другой таблице.

4. Фильтр/проблема Jira — Подготовленная выборка из Jira.

5. Загруженный файл Excel, но его нужно добавлять в тело макроса как макрос Office Excel.

   

6. Фильтр таблицы, Table Transformer, Table from CSV,  Table from Json, Table Spreadsheet — это подобие Excel.

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

Построение таблиц Confluence на данных Jira

Что теперь можно делать с полученными знаниями? Применять на реальной практике. Confluence очень хорошо работает с данными Jira, поэтому любой запрос вы можете преобразовать в таблицу, и дальше крутить, как вам будет удобно для визуализации.

Например, перед нами стоит задача посмотреть, сколько сотрудников в нашем подчинении находится без задач на текущий момент. Предвосхищая вопросы зачем все это, если есть Доска Kanban и дашборды в Jira — ответ простой — во-первых, это просто пример, во-вторых, там вы не сможете сматчить данные с разных проектов Jira в одной таблице или получить быстрые данные по заготовленной формуле.

Например:

1. Создаем макрос Table Transformer.

2. Помещаем внутрь него макрос запроса фильтра Jira — все сотрудники, у которых я являюсь руководителем JQuery( issuetype = Сотрудники AND status in (Работает, Увольняется, Выход) AND «Руководитель» = currentUser()).

3. Добавляем фильтр по всем задачам в статусе «В работе» JQuery(issuetype = Разработка  and status =»В работе»), мы выводим все задачи вообще всех сотрудников, ограничим дальше. Не забудьте в параметрах фильтра указать максимальное количество отображаемых записей, иначе получите не все. Так же если вам нужны конкретные поля Jira, их необходимо в настройках фильтра так же указать.

   

4. Также мне дополнительно придется разбить ФИО из одного поля на три и немного изменить формат на Имя О.

В результате видим, что у нас 3 сотрудника сидят без задач на текущий момент, а у одного — даже 8. 

Заключение

1. Таблицы в Confluence — очень гибкий инструмент при наличии макросов, которыми их можно модифицировать.

2. Суммы и своды доступны и просты в освоении.

3. Расчет собственных формул достаточно трудный, и либо требует знаний SQL, либо снижения презентабельности страницы.

4. В качестве таблиц можно использовать достаточно широкий спектр источников данных.

5. Возможно использование Макроса в Макросе в Макросе и т.д. Но главное не переусердствовать, иначе скажется на производительности, и загрузка страницы будет стремиться к ∞.

Спасибо за прочтение. И удачи вам в создании своих таблиц и расчетов.