10 декабря 2017 г.

Создание отчета в Pentaho Report Designer

Данный пост о создании печатных форм в Pentaho Report Designer – Open Source дизайнере отчетов. Для примера взята форма отчета «Отчет о кредитах, выданных нерезидентам (форма 17-ПБ)», который банки Казахстана сдают в Национальный банк, в рамках регуляторной отчетности.

В целом все очень похоже на любой другой конструктор отчетов.

Создаем новый отчет, в свойствах отчета указываем название:



Как и в других конструкторах, структура отчета состоит из полос (band): Заголовок Отчета, Заголовок Страницы, Детали, Подвал Страницы, Подвал Отчета. При необходимости можно добавить группировку строк по какому-то признаку, соответственно тогда возникнут полосы Заголовок Группы и Подвал Группы. Для отображения групповых полос необходимо будет снять галку «Скрыть элемент» с этой группы в панели Структуры документа.



Далее размещаем все компоненты (поля) отчета на соответствующих полосах. Для текстовых полей соответственно текстовые компоненты, для числовых – числовые, и так далее. Задаем границы элементов. Для того, чтобы одним махом изменять свойства нескольких элементов – в Структуре документа выделяем их всех через Shift. На картинке у всех выделенных элементов изменено свойство Левая граница, на «Одинарная». Для того, чтобы сбросить свойство, кликаем галку Унаследовано.



Так как в текстовых полях длина текста может быть различной, и текст может переходить на вторую или третью строку, то высоту всех элементов на полосе Детали задаем как 100% от допустимой. В этом случае тот элемент, в котором длинный текст увеличил высоту, растянет полосу по высоте, и все остальные элементы на полосе так же растянутся. Для указания высоты в 100% нужно просто вбить текст «100%», с единицами измерения разработчики не стали заморачиваться.



Далее, на вкладке Данные добавляем датасет, из которого выводятся данные в отчет.



С большинством распространенных баз данных система соединяется прямо «из коробки», но для некоторых баз данных нужно доложить коннектор в папку C:\...\report-designer\lib.

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



Если список полей из запроса не появился, то нужно проверить SQL запрос, указанный в датасете. Могут мешать комментарии в тексте запроса, до или после.

В JDBC коннекторе к MySQL, до недавних пор, наблюдался глюк, список полей под датасетом не появлялся, пока в тело SQL-запроса датасета не добавить команду LIMIT. Количество строк в LIMIT не имело значения, например, 1 000 000 000, но сама команда LIMIT должна была присутствовать. Данный баг исправили недавно, например, в текущем коннекторе mysql-connector-java-5.1.44-bin.jar такого бага уже нет.

Отчет строится за какой-то период, соответственно в отчет нужно передать Дата_От и Дата_По. Под датасетом есть раздел Параметры, добавляем параметр dl_from, и аналогично dl_to.



Параметры вставляем в текст SQL запроса в фигурных скобках:
SELECT … FROM … WHERE … = ${dt_from} … = ${dt_to}

При вызове отчета пользователь получит приглашение ввести даты.



Далее заполняем все поля с данными. Двойной клик на компоненте вызывает список с полями, выбираем нужное поле.



В свойствах компонента устанавливаем формат отображения значения поля, например «dd.MM.yyyy» для даты, или «#,##0» для чисел.

В первой колонке отчета номер строки. Поэтому это компонент Ярлык (Label), а не поле для данных. В атрибутах этого Ярлыка, в свойстве Значение, указываем формулу =( ROWCOUNT() + 1 )



Если формула введена правильно, то над формулой будет или рассчитанное значение, или сообщение «Недостаточно данных для расчета». Если в формуле ошибка, то появится сообщение об ошибке. Даже пустая строка то же считается ошибкой.



В отчете есть поле с рассчитываемым значением. Под датасетом, в разделе Функции, добавляем функцию типа Open Formula.



В свойствах добавленной формулы, переименовываем ее имя в удобоваримое RowCount_..., и в атрибуте формула добавляем нужные расчеты.



В подвале отчета необходимо вывести сумму итого под некоторыми колонками. Это то же рассчитываемое поле, только с типом Сумма (Sum).



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



Для лучшей читаемости отчета, нужно чередовать фоновый цвет строк таблицы. Для этого в свойствах полосы, в атрибуте Фоновый цвет (bg-color) добавляем формулу: =IF( ISODD( ROWCOUNT() ); "#FFFFFF"; "#DDDDDD" ). Действие формулы понятно без комментария.



Последний штрих – в Заголовке отчета, в соответствующем Ярлыке добавляем формулу: = "за период с " & MESSAGE("{0,date,dd.MM.yyyy}"; [dt_from]) & " г. по " & MESSAGE("{0,date,dd.MM.yyyy}"; [dt_to]) & " г.". Действие формулы понятно без комментария.

Отчет готов.



Пример регуляторного отчета 17-ПБ «Отчет о кредитах, выданных нерезидентам» в PDF.

Пример регуляторного отчета 17-ПБ «Отчет о кредитах, выданных нерезидентам» в Excel.

В отчете в Excel есть скрытые столбцы, то есть некоторые поля в отчете не очень плотно подогнаны друг к другу. Можно подогнать, а можно поменять раскладку полосы с Холста на Строку. В раскладке Холст вы сами размещаете компоненты на полосе. В раскладке Строка полоса сама будет выстраивать все компоненты друг за другом, примерно как float:left в свойствах div в HTML. Вам останется только изменять ширину компонентов, и менять их порядок в строке (перемещать влево – вправо по полосе) с помощью кнопок под ярлычком Структура документа.





PS: Осталось только добавить, что для написания поста, по техническим причинам, пришлось набросать тестовый SQL-запрос для наполнения отчета случайными данными.

SELECT
client_name AS NAMECLIENT,
tax_bin AS IIN,
CAST( ( FLOOR( RAND() * 400 ) + 100 ) AS CHAR ) AS NUM_DOG,
CAST( ( FLOOR( RAND() * 100000 ) + 100 ) AS SIGNED ) AS SUM_DOG,

CAST( ( FLOOR( RAND() * 100000 ) + 100 ) AS SIGNED ) AS SALDO_BEG_OD,
CAST( ( FLOOR( RAND() * 10000 ) + 100 ) AS SIGNED ) AS OBOROT_KT_OD,
CAST( -( FLOOR( RAND() * 50000 ) + 100 ) AS SIGNED ) AS OBOROT_DT_OD,
CAST( ( FLOOR( RAND() * 10000 ) + 100 ) AS SIGNED ) AS SALDO_END_OD,

CAST( ( FLOOR( RAND() * 100000 ) + 100 ) AS SIGNED ) AS SALDO_BEG_PRC,
CAST( ( FLOOR( RAND() * 10000 ) + 100 ) AS SIGNED ) AS OBOROT_KT_PRC,
CAST( -( FLOOR( RAND() * 50000 ) + 100 ) AS SIGNED ) AS OBOROT_DT_PRC,
CAST( ( FLOOR( RAND() * 10000 ) + 100 ) AS SIGNED ) AS SALDO_END_PRC,

CAST( ( FLOOR( RAND() * 900000000 ) + 10000000 ) AS CHAR ) AS BALANS_NUM_OD,
CAST( ( FLOOR( RAND() * 900000000 ) + 10000000 ) AS CHAR ) AS BALANS_NUM_PRC,

CASE ( FLOOR( RAND() * 2 ) + 1 )
WHEN 1 THEN 'USD'
WHEN 2 THEN 'EURO'
END AS CURRENCY,

CASE ( FLOOR( RAND() * 3 ) + 1 )
WHEN 1 THEN '16'
WHEN 2 THEN '17'
WHEN 3 THEN '18'
END AS INTEREST,

CASE ( FLOOR( RAND() * 6 ) + 1 )
WHEN 1 THEN CAST('2015-01-01' AS DATE)
WHEN 2 THEN CAST('2015-02-02' AS DATE)
WHEN 3 THEN CAST('2015-03-03' AS DATE)
WHEN 4 THEN CAST('2015-04-04' AS DATE)
WHEN 5 THEN CAST('2015-05-05' AS DATE)
WHEN 6 THEN CAST('2015-06-06' AS DATE)
END AS DATE_BEGIN,

CASE ( FLOOR( RAND() * 6 ) + 1 )
WHEN 1 THEN CAST('2015-07-01' AS DATE)
WHEN 2 THEN CAST('2015-08-02' AS DATE)
WHEN 3 THEN CAST('2015-09-03' AS DATE)
WHEN 4 THEN CAST('2015-10-04' AS DATE)
WHEN 5 THEN CAST('2015-11-05' AS DATE)
WHEN 6 THEN CAST('2015-12-06' AS DATE)
END AS DATE_GIVE,

CASE ( FLOOR( RAND() * 6 ) + 1 )
WHEN 1 THEN CAST('2016-07-01' AS DATE)
WHEN 2 THEN CAST('2016-08-02' AS DATE)
WHEN 3 THEN CAST('2016-09-03' AS DATE)
WHEN 4 THEN CAST('2016-10-04' AS DATE)
WHEN 5 THEN CAST('2016-11-05' AS DATE)
WHEN 6 THEN CAST('2016-12-06' AS DATE)
END AS DATE_END,

CASE ( FLOOR( RAND() * 5 ) + 1 )
WHEN 1 THEN 'Германия'
WHEN 2 THEN 'Объединенные Арабские Эмираты'
WHEN 3 THEN 'Великобритания'
WHEN 4 THEN 'Испания'
WHEN 5 THEN 'Франция'
END AS COUNTRY

FROM
tbl_client

ORDER BY RAND()

LIMIT 10

Комментариев нет:

Отправить комментарий