Автоматизация написания сложных отчетов (Версия 2)

Введение

Данная статья касается вопроса автоматизации написания сложных отчетов. Она снабжена примерами для 1С, но будет полезна для любой программной среды, где используются отчеты. На практике для 1С реализован модуль «Дерево Осипова», который можно применять для написания реальных отчетов.

По роду деятельности мне часто приходится писать различные отчеты. В написании отчетов есть две вещи, которые сильно раздражают меня:

1. Часто приходится писать одно и то же.
2. Заказчики все чаще хотят видеть сложные перекрестные отчеты, которые человеку кажутся простыми, но чтобы написать их , требуется большое время.

Поэтому меня интересовали различные способы автоматизации написания отчетов.

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

Далее я исследовал Crystal Reports. Однако он показался мне слишком сложным для простых задач построения отчетов. Все равно что из пушки по воробьям палить. К тому же, все основные средства построения отчетов были и в 1С, Crystal Reports не давал мне существенных способов автоматизации.

Поэтому я начал задумываться о построении собственной системы автоматизации вывода отчетов.

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

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

Ознакомьтесь с предлагаемым подходом и примените его в своей практике.

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

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

Основные термины

Расшифруем термины, которые будут встречаться в тексте.

Клетка – это прямоугольная область листа (экрана), содержащая некоторый текст, обладающая цветом фона, текста, рамками и прочими элементами оформления. Соседние по вертикали клетки имеют одинаковую ширину, по горизонтали – одинаковую высоту. Таким образом клетки образуют строки и колонки.

Поле отчета – это клеточное прямоугольное поле размером (в клеточках) M по вертикали и N по горизонтали, сверху и слева снабженное шапками.

Ячейка – это одна или несколько клеток, образующих прямоугольную область. Внутри ячейки содержится некоторое значение. Обычно клетки ячейки объединяются в одну. Отчет – это совокупность ячеек, расположенных на поле отчета. Часть ячеек, расположенных слева(сверху) образуют вертикальную(горизонтальную) шапку.

Вертикальная(горизонтальная) шапка – это совокупность ячеек, которыя расшифровывают содержание строк (колонок) таблицы.

Особенности модели

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

Шапка является деревом, состоящим из узлов.

Узел – ячейка шапки. Имеет подчиненные узлы.

Не все ветви дерева шапки имеют одинаковый уровень. Узлы последнего уровня (листы) могут находиться на разном расстоянии от корневых узлов. Количество листов по вертикали равно M, по горизонтали соответственно N. Каждый узел в дереве указывает на определенный участок таблицы - строку (столбец), высоту (ширину) в клетках которого назовем размером узла. Другой размер ячейки узла назовем контрразмером узла. Все узлы можно хранить в одном списке (таблице), тогда узлом с ID=1 (2) будет узел вертикальной (горизонтальной) шапки.

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

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

Пусть В1, В2, В3, …  – узлы вертикальной шапки, а Г1, Г2, Г3, … - вертикальной. Тогда пара координат (Вi, Гi) указывает на некоторую ячейку, размеры которой равны размерам и контрразмерам образующих ее узлов. Т.к. для каждого узла известно, к какой шапке он принадлежит, то пара координат может быть задана и как (Гi , Вi), т.е. главное, чтобы узлы относились к противоположным шапкам. Координата 0 указывает, что узел находится в шапке.

Ареал – это узел или список узлов, или координата 0.

Область таблицы – это совокупность ячеек, находящаяся на пересечении двух ареалов.

Для того, чтобы указать значения ячеек достаточно задать список триад – (Узел по горизонтали; Узел по вертикали; Значение).

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

На данном этапе моделирования для описания любой таблицы (без визуального форматирования – шрифт, узор и т.п.)  достаточно задать список узлов вертикальной и горизонтальной шапки, а также указать значения ячеек на пересечении узлов. Программа генерации отчета должна по этим данным уметь вывести таблицу с шапкой и ячейками.

Расширение модели

Однако для вывода полноценного отчета нужны дополнительные средства автоматизации. Рассмотрим их по порядку.

Визуальное представление ячеек

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

Можно использовать форматирование по образцу (см. далее).

Видимость

Некоторые узлы могут быть скрыты при выводе. Для этого каждый узел имеет атрибут «Видимость». Можно скрыть также и все подчиненные данному узлу.

Сортировка

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

В будущем предполагается усовершенствовать механизм сортировки, чтобы сортировка осуществлялась с учетом иерархии групп (1С).

Итоги

Крайне желателен подсчет итогов, т.к. итоги содержатся в большинстве таблиц.

1. Подставлять уже непосредственные значения итогов.
2. Суммировать значения в ячейках – т.е. взять значения в нужных ячейках таблицы, подитожить и подставить в ячейку итогов. Для этого удобно использовать перебор ячеек области таблицы.
3. Автоматизировать вычисление итогов – указать описание, по которому генератор таблиц должен посчитать итоги. Пока нет подходов.

Очевидно, что некоторые итоги считаются уже по итогам – это «итоги итогов». Поэтому при вычислении итогов нужно правильно задавать их порядок.

Топ-отборы

Топ-отборы – это выдача не всех, а только нескольких максимальных по какому-нибудь параметру значений в запросе.

Легко делаются на основе сортировки. Т.е. указывается узел-родитель и сколько элементов должно выводиться в топе. Остальные дети родителя прячутся. Предполагается, что дети уже отсортированы в нужной последовательности.

Последовательность построения отчета

Большинство функций носят процедурный, а не декларативный характер (отсортировать, разукрасить, построить), поэтому важен порядок их применения.

1. Описание узлов, можно прятать узлы.
2. Занесение значений ячеек.
3. Расчет итогов в правильной последовательности.
4. Сортировка.
5. Топ-отборы.
6. Рассчет координат узлов.
7. Вывод дерева в таблицу.
8. Оформление областей таблицы шаблонами - дизайн.
9. Показ таблицы.

Дизайн отчета

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

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

Виды группировок

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

Комбинации определяются значениями двух параметров:

• «ДетиВместе» - определяет, что родители и дети находятся на одном уровне при выводе в таблицу
• «ДетиПоложение» - определяет один из трех способов расположения родителя относительно детей:
• –1 – родитель расположен раньше детей (относительно левого верхнего узла);
• 0 – родитель объединяет детей одной шапкой;
• 1 – родитель расположен позже детей.

Варианты размещения представлены графически:

Пример задания отчета

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

Описание отчета:  Сколько товара (черным цветом)  купили клиенты и по какой цене (синим цветом) с каждого склада в каждом городе. В поле инфо отображаются телефоны контрагентов и некая оценка популярности товара по пятибальной шкале.

Предусмотреть возможность (по выбору пользователя) сортировки и топ-отборов:

Склады сортировать в пределах города по убыванию кол-ва продаж.

Клиентов сортировать по убыванию кол-ва продаж.

Выводить только по 2 товара с максимальным количеством продаж.

Алгоритм построения отчета

Составляем список узлов вертикальной шапки:

• 1 -  корневой узел вертикальной шапки
• УзВ(«ВШапка») - родитель 1
• УзКлиенты(«Клиенты»), УзИтого1 («Итого1») - родитель УзВ

Составляем список узлов горизонтальной шапки:

• 2 -  корневой узел горизонтальной шапки
• УзГ(«ГШапка») - родитель 2
• УзИнфо(«Инфо/рейтинг»), УзМеста(«Места»),УзИтого2(«Итого2») - родитель УзГ

Составляем список значений ячеек программно:

Определяем ареалы

• АрКлиенты – список всех узлов с клиентами
• АрТовары – список всех узлов с товарами
• АрГорода – список всех узлов с городами
• АрСклады – список всех складов
• АрИзмерения – список всех узлов «Цена» и «Количество»
• АрКоличество – список всех узлов «Количество»
• АрЦены=– список всех узлов «Цена»

Вычисляем итоги

• Область итогов (АрКлиенты, АрСклады), область суммирования (АрТовар, АрКоличество)
• Область итогов (АрКлиенты, УзИтого2), область суммирования (АрКлиенты, АрСклады)
• Область итогов (УзКлиенты, АрСклады), область суммирования (УзИтого1, АрСклады)
• Область итогов (УзИтого1, УзИтого2), область суммирования (УзИтого1, АрСклады)

Выполняем сортировку

Склады сортировать в пределах города по убыванию кол-ва продаж – сортируем ареал АрСклады по узлу сортировки УзИтого1 в порядке убывания.

Клиентов сортировать по убыванию кол-ва продаж - сортируем ареал АрКлиенты по узлу сортировки УзИтого2 в порядке убывания.

Товары сортировать по убыванию кол-ва продаж - сортируем ареал АрТовары по узлу сортировки УзИтого2 в порядке убывания. (для последующего отбора)

Выполняем отбор:

Отбираем по 2 самых продаваемых товара – выполняем топ-отбор для детей узла  АрКлиенты.

Расчет узлов, рисование таблицы, окончательный дизайн:

Завершающий этап формирования отчета.

Формат по образцу

Для форматирования по образцу используется область-шаблон и набор форматирования, который нужно скопировать из этого шаблона.

Набор задается форматной строкой, содержащей теги со знаками включения «+» или выключения «-». По умолчанию “Все+Размеры-“

Некоторые теги иерархически подчинены другим.

Действуют такое правило применения - тег более низкого уровня имеет более высокий приоритет.

Например формат «Все-Текст+ЦветФона+» означает скопировать только настройки текста и цвет фона.           

Замечания

1. В оформлении таблицы некоторые шаги опущены для экономии времени.
2. Для уяснения деталей смотрите прилагаемый образец отчета для 1С.
3. Данный подход будет дорабатываться и усовершенстоваться.
4. Для добавления в вашу программу достаточно скопировать функции между баннерами «Дерево» в ваш глобальный модуль или ваш отчет.

Скачать: Обработка и статья в формате Word.zip

Лицензия

Данный модуль распространяется по лицензии freeware. Лицам, использующим модуль в своих программах, предлагается уплатить посильный взнос автору. Платежные реквизиты можно найти на сайте www.koenigsoft.boom.ru.

Осипов Сергей Александрович.
Апрель 2003.
Московская область. В ссылке
www.koenigsoft.boom.ru
fixin@mail.ru
ICQ: 73587347