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

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

Введение

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

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

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

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

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

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

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

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

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

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

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

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

Для платформы 1С код реализован в виде набора 30-40 функций, которые можно подключить в глобальный модуль или отчет. Производительность достаточно высока, т.к. не используется времяемких функций поиска и перебора.

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

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

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

Поле отчета – это клеточное прямоугольное поле размером (в клеточках) 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») - родитель УзГ

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

Цикл для каждой записи по продажам (Клиент,  Товар, Продано, Склад,  ПоЦене):
                По Складу в узле УзГород находим/создаем узел УзСклад;

По Клиенту находим/создаем узел УзКлиент;

                По Товару в узле УзКлиент находим/создаем узел УзТовар;

                По Складу определяем Город продажи;
                По Городу находим/создаем узел УзГород;

                В узле УзСклад находим/создаем узел УзЦена;

                В узле УзСклад находим/создаем узел УзКоличество;

                В значения ячеек добавляем (УзТовар; УзЦена; ПоЦене); 

                В значения ячеек добавляем (УзТовар; УзКоличество; Продано); 

                В значения ячеек добавляем (УзТовар; УзЦена; ПоЦене); 

Цикл по каждому узлу УзКлиент из узлов, подчиненных узлу 1:

Из справочника клиентов берем Телефон клиента;

В значения ячеек добавляем (УзКлиент; УзИнфо; Телефон); 

Цикл по каждому товару УзТовар из узлов, подчиненных узлу УзКлиент:

                Из справочника товаров берем оценку популярности Оценка;

В значения ячеек добавляем (УзТовар; УзИнфо; Оценка); 

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

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

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

  • Область итогов (АрКлиенты, АрСклады), область суммирования (АрТовар, АрКоличество)
  • Область итогов (АрКлиенты, УзИтого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


Начать дискуссию

Как сделать возврат или зачет суммы излишне уплаченного налога в 2024: новая форма заявления 

Суммы налогов, которые излишне уплачены в составе ЕНП, по сути переплата в бюджет, они образуют на ЕНС положительного сальдо. Такую переплату можно вернуть на расчетный счет или зачесть в счет будущей задолженности, которая будет сформирована. 

Как сделать возврат или зачет суммы излишне уплаченного налога в 2024: новая форма заявления 
26

Курсы повышения
квалификации

18
Официальное удостоверение с занесением в госреестр Рособрнадзора

Директора подразделения «Росатома» арестовали за взятку

Топ-менеджера структурного подразделения ГК «Росатом» заключили под стражу до 26 мая 2024 года. Его подозревают в получении взятки в особо крупном размере.

32

Фиктивная сотрудница, ничего не делая, за год «заработала» более 500 000 рублей

В Сочи направлено в суд уголовное дело о мошенничестве при трудоустройстве.

81
Лучшие спикеры, новый каждый день

Какие выплаты положены работнику при увольнении переводом

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

35

😠 Если ИП на УСН «доходы» закроется в январе 2025 года, его вычет на взносы 1% просто сгорит. Бухгалтеры возмущены

Взносы ИП 1% за 2024 год налог по УСН за 2024 год не уменьшают. Они пойдут на уменьшение налога только за 2025 год. Если конечно там будет, что уменьшать.

29

Центробанк снова продлил ограничения на перевод средств за рубеж

Ограничения на денежные переводы за границу будут действовать еще 6 месяцев — до 30 сентября 2024 года.

36
Опытом делятся эксперты-практики, без воды

Как бизнесу снизить налоговую нагрузку: 5 законных способов

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

Как бизнесу снизить налоговую нагрузку: 5 законных способов
30

ФАС требует от Ozon объяснений по введению сбора в ПВЗ

Представители рынка электронной торговли направили в ФАС жалобу на Ozon, который установил сервисные сборы для владельцев пунктов выдачи заказов.

53

Тонкое искусство управления дедлайнами

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

Тонкое искусство управления дедлайнами
42

Предприниматели оформили рекордное число кредитов с плавающей ставкой

Число кредитов, процентная ставка которых привязана к ключевой, уже выросла почти до 50%. Бизнес уверен, что ЦБ снизит ключевую ставку, а вместе с ней сократятся проценты по займам.

48
УСН

ИП на УСН «доходы» не нужно заявление на зачет взносов 1%

Срок уплаты взносов ИП 1% с дохода свыше 300 тысяч рублей за 2023 год – 01.07.2024. Но можно не тянуть до последнего, а оплатить уже сейчас.

88

Chanel завершает свой бизнес в России

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

149
Миникурсы, текстовые и видеоинструкции для бухгалтеров

Коротко, для чего нужен финансовый план? Как его создать

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

Коротко, для чего нужен финансовый план? Как его создать
26
НДФЛ

Оплата вынужденного прогула облагается НДФЛ

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

45

❗ Компания может попасть на штраф, если при переходе на ЕНС переплату по одному налогу ФНС зачла в счет долга по другому

За неумышленную неуплату налога из-за занижения базы или иного неправильного исчисления предусмотрен штраф 20% от суммы налога (по п. 1 ст. 122 НК).

144

Сколько длится дело по субсидиарке? Что приводит к затягиванию суда

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

28

Объявления об онлайн-продаже табака могут заблокировать без решения суда

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

35

Почему не существует шутки, которая нравится всем? Проведем эксперимент

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

Почему не существует шутки, которая нравится всем? Проведем эксперимент
339

Как обезопасить массовые выплаты внештатникам

Компании, которые сотрудничают с большим числом внештатников, проводят массовые выплаты со своего счета. Такие платежи могут нести риски с точки зрения ИФНС и банков. Рассказываем, в чем причины и как обезопасить переводы.

Как обезопасить массовые выплаты внештатникам
162

Интересные материалы

НДС

Уточнят нормы НК об НДС 0% при экспорте

Торгово-промышленная палата России (ТПП) предложила исключить двойное толкование норм о международной перевозке с точки зрения налогового законодательства.

195