Динамические столбцы в интерактином отчете
10.03.2016

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

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

Вот пример из жизни. Московская областная федерация закаливания и зимнего плавания регулярно проводит соревнования по зимнему плаванию. Набор дистанций от соревнования к соревнованию немного изменяется, вводятся новые дистанции и технические элементы. Каждый участник может заявиться на несколько дистанций. Соответственно с точки зрения "нормальной" модели данных отношение между сущностью "Участник" и "Дистанция" будет "один ко многим". Нужен отчет по участникам соревнований в котором каждая дистанция, это отдельный столбец в таблице со значением "Да", если участник заявился на эту дистанцию, или пусто, если не заявился. Поскольку набор дистанций не фиксирован и их названия могут быть разными, мы не можем заранее создать отчет под все виды дистанций. Что делать?

1. Делаем предположение о максимальном возможном количестве дистанций. Допустим их будет не более 9. Дистанциям назначаем порядковый номер от 1 до 9.

2. Создаем SQL-запрос, где отношение "один ко многим" между сущностью "Участник" и "Дистанция" транспонируется в одну строку c 11-ю колонками: идентификатор участника, ФИО, и 9 дистанций. Это можно реализовать с помощью группировки, но есть способ получше, см. PIVOT Дистанция с номером 1 будет попадать в колонку "D1", с номером 2 в "D2" и т.д. SQL-запрос будет примерно таким:

SELECT * FROM (
SELECT m.id AS member_id,
       m.surname ||' '||m.name ||' '|| m.patronymic AS fio,
       p.sn AS n
  FROM member m
       JOIN member_event me ON me.member_id = m.id
       JOIN member_event_program mep ON mep.member_event_id = me.id
       JOIN program p ON p.id = mep.program_id
 WHERE me.event_id = 1)
PIVOT (COUNT(1) FOR n IN (1 d1, 2 d2, 3 d3, 4 d4, 5 d5, 6 d6, 7 d7, 8 d8, 9 d9)
)

3. Создаем страницу с интерактивным отчетом на основе полученного SQL-запроса:

Здесь требуются некоторые пояснения.

На странице есть два региона "Отчет" и "Дистанции". Один собственно интерактивный отчет. Второй является контейнером для Hidden полей содержащих заголовки колонок с D1 по D9. Динамичность заголовков реализуется с помощью "Placeholder variable". В атрибуте "Column Heading" колонки указывается не текстовый заголовок, а ссылка на поле. Например для колонки D1 указан placeholder &P20_D1.

Видимость столбцов регулируется условием Conditional Diasplay, задаваемым индивидуально для каждого столбца:

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

Для уменьшения накладных расходов на рендеринг страницы, региону "Дистанции" указывается условие Conditional Diasplay=None. Но это не обязательно.

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

4. Для региона "Отчет" на закладке "Report Attributes" включаем "Link Column" и задаем для него условие Condition Type вот такого вида:

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

Рассмотрим подробнее, что делает этот код:

BEGIN
  /* Инициализировать сессию, если отчет запущен по подписке */
  IF NOT APEX_CUSTOM_AUTH.SESSION_ID_EXISTS THEN
    apex_application.g_instance     := wwv_flow_custom_auth.get_next_session_id;
    apex_custom_auth.define_user_session(p_user => 'FZZP', p_session_id => apex_application.g_instance);
    wwv_flow_custom_auth.remember_deep_link (p_url => 'f?p=401');
  END IF;
     /* Получить заголовки для дистанций */
  FOR r IN (SELECT p.sn, d.short_name FROM program p JOIN distance d ON d.id = p.distance_id WHERE p.event_id = 1) LOOP
    APEX_UTIL.SET_SESSION_STATE('P20_D'||r.sn, r.short_name);
  END LOOP;
 
  RETURN TRUE;
END;  

Первый блок проверяет наличие открытой пользовательской сессии. Если ее нет, создает. Это нужно только в одном случае, когда вы настроите подписку. Дело в том, что когда отчет запускает по подписке, APEX не создает сессию, а лишь выполняет ваш SQL-запрос отчета и результаты выводит в HTML файл, прикрепляемый как вложение к письму. Ни какие сессионные переменные не инициализируются и сама сессия в таблице wwv_flow_sessions$ не описывается. А поскольку у нас часть столбцов зависит от сессионных переменных, необходимо создать сессию и инициализировать поля используемые в условиях Condition, и в качестве "Placeholder variable". Приведенный здесь пример создания сессии не является полноценным, но волне подходит для текущей задачи. Более полный способ инициализации сессии см. по ссылке

Второй блок получает перечень дистанций для мероприятия и сохраняет названия дистанций в соответствующих Hidden полях страницы. Идентификатор мероприятия задан фиксированный "1" для простоты кода, в реальности он должен либо, вычисляться, либо как то задаваться пользователем, например через фильтр.

У Вас может возникнуть вопрос, почему названия заголовков для дистанций формируются в таком странном месте - Condition для Link Column? Почему не в каком-нибудь процессе уровня страницы? Во-первых процесс уровня страницы не сработает при рассылке отчета по подписке, а вот Condition для Link Column отчета сработает и инициализирует все необходимые вам сессионные переменные. Во-вторых, в условиях ограниченного набора процессов запускаемых APEX-ом при рассылке отчетов по подписке, этот процесс (Condition для Link Column) срабатывает один из первых в цепочке процессов в которых можно написать свой PLSQL-код.

release 1.0