7.2. Пример перекрестного отчёта

Для создания перекрестного, или матричного, отчёта выберите ориентацию полосы Crosstab на вкладке Report structure редактора отчётов. При выборе этой ориентации к полосе автоматически добавляются три набора данных:

  1. <band_name>_dynamic_header – данные из этого набора заполняют отчёт значениями слева направо, то есть он ведет себя, как вертикальная полоса с заголовками столбцов матрицы.

  2. <band_name>_master_data – данные из этого набора заполняют отчёт значениями сверху внизу, то есть он ведет себя, как горизонтальная полоса с заголовками строк матрицы.

  3. <band_name> – набор данных, названный так же, как полоса, в которой он создан. Этот набор содержит данные для заполнения ячеек матрицы.

Для этих наборов данных вы можете выбрать любой из доступных типов: SQL, JPQL, Groovy, и т.д.

Для примера создадим матричный отчёт для сущности Order из демо-приложения Sales со следующей структурой:

crosstab structure

  1. Структура данных отчёта.

    Рассмотрим полосы отчёта.

    • набор данных orders_dynamic_header возвращает список названий месяцев:

      orders_dynamic_header dataset 
      import java.text.DateFormatSymbols

List result = new ArrayList()
DateFormatSymbols dateFormatSymbols = DateFormatSymbols.getInstance(Locale.ENGLISH)
for (i in 0..dateFormatSymbols.months.length - 1) {
    result.add(["header_id" : i + 1, "month_name" : dateFormatSymbols.months[i]])
}
return result

    • набор данных orders_master_data возвращает имена и идентификаторы покупателей, выбранных пользователем в качестве внешнего параметра отчёта:

      orders_master_data dataset 
      select name as name, id as customer_id
from SALES_CUSTOMER
where id in (${selected_customers})

    • набор данных orders генерирует данные для заполнения ячеек матрицы, то есть сумму всех заказов, сделанных конкретным покупателем в конкретном месяце. Он использует orders_master_data@customer_id (идентификатор покупателя) как вертикальную координату ячейки и orders_dynamic_header@header_id (название месяца) как горизонтальную координату, а затем заполняет ячейку суммой значений amount.

      В примере ниже мы использовали два дополнительных внешних параметра: start_date и end_date, которые определяют временной диапазон заказов. Мы рекомендуем использовать перекрестную валидацию значений введенных параметров, чтобы избежать ошибок, вызванных неправильным диапазоном дат.

      orders dataset 
      select
        o.customer_id as orders_master_data@customer_id,
        month(o.date_) as orders_dynamic_header@header_id,
        sum(o.amount) as "amount"
from sales_order o
where o.date_ >= ${start_date} and o.date_ <= ${end_date}
and o.customer_id in (${orders_master_data@customer_id})
and month(o.date_) in (${orders_dynamic_header@header_id})
group by o.customer_id, month(o.date_)
order by o.customer_id, month(o.date_)

  2. Параметры отчёта.

    На вкладке Parameters and Formats объявлены внешние параметры отчёта – selected_customers, start_date, end_date:

    crosstab external params
    Рисунок 62. Внешние параметры отчёта

    Эти параметры запрашиваются у пользователя при запуске отчёта. Выбор покупателей производится через экран sales_Customer.browse, имеющийся в приложении.

  3. Шаблон отчёта.

    Теперь создадим шаблон отчёта, используя Microsoft Office или LibreOffice.

    Создайте в шаблоне именованные регионы для всех трех наборов данных полосы orders, а также, дополнительно, регион для заголовка столбца: <band_name>_header. В нашем случае это orders_header.

    Вот так будет выглядеть шаблон отчёта, выводящего по вертикали список имен Customers и горизонтально сумму Orders, сгруппированных по месяцам:

    crosstab template 2
    crosstab names regions

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

crosstab result

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