При разработке отчетов в системе компоновки данных (СКД) на платформе 1С перед разработчиком часто возникает задача расчета накопительных (нарастающих) итогов. Типичный пример — планирование запасов, анализ графиков платежей или ожидаемых поступлений товаров, когда необходимо видеть не просто обороты за конкретный месяц, а аккумулированную сумму нарастающим итогом с начала периода.
В этой статье мы подробно рассмотрим, почему стандартное суммирование ресурсов СКД не решает эту задачу, и разберем два основных метода реализации нарастающего итога: с помощью встроенных функций компоновщика данных и непосредственно на уровне SQL-запроса. Также мы проанализируем частые ошибки разработчиков и разберем лучшие практики оптимизации таких отчетов.
Рассмотрим классическую ситуацию. Из информационной базы запросом мы получаем временную таблицу со следующей структурой данных:
Исходные данные выглядят следующим образом:
Если мы выведем эти данные в кросс-таблицу СКД со стандартным ресурсом Сумма(ОжидаемыйПриход), то получим простые обороты за каждый месяц. Наша же цель — вывести в колонках периоды, а в ячейках получить накопленный итог, где значение каждого последующего периода включает в себя сумму предыдущих:
---
Это наиболее элегантный, современный и производительный способ решения задачи. Платформа 1С предоставляет мощный математический аппарат для расчета агрегатных функций на уровне компоновщика данных в памяти компьютера. Нам не нужно усложнять запрос к базе данных — всю работу по расчету накопительного итога возьмет на себя СКД.
Выясним, как правильно настроить этот расчет. Для этого мы воспользуемся функцией ВычислитьВыражение() на вкладке «Ресурсы» схемы компоновки данных.
ОжидаемыйПриход (или созданное вычисляемое поле).
ВычислитьВыражение("Сумма(ОжидаемыйПриход)", "Группировка", , "Первая", "Текущая")
Разберем подробнее параметры используемой функции ВычислитьВыражение:
"Сумма(ОжидаемыйПриход)") — это агрегатное выражение, которое рассчитывается СКД для каждой ячейки."Группировка") — это ключевое слово (системный параметр), указывающее СКД рассчитывать выражение в разрезе текущей группировки. В сообщениях форума часто встречается указание конкретного поля, например, "МесяцОжидаемогоПрихода". Однако использование универсального значения "Группировка" гораздо надежнее. Если пользователь решит перестроить отчет или изменить периодичность колонок (с месяцев на недели), универсальная формула продолжит работать без ошибок."Первая") — указывает на начало диапазона суммирования. В нашем случае расчет всегда начинается с самой первой колонки (первого периода) для каждого товара."Текущая") — указывает на конечную точку диапазона. Суммирование происходит по текущую колонку (текущий период) включительно.---
Если по какой-то причине вы не хотите использовать встроенные возможности СКД (например, отчет должен формироваться строго в рамках логики SQL-запроса для последующей выгрузки во внешнюю систему), расчет можно перенести на сторону СУБД.
Этот метод основан на левом соединении таблицы с самой собой по условию совпадения аналитики (номенклатуры) и неравенства дат (период левой таблицы должен быть больше или равен периоду правой таблицы).
Рассмотрим пример реализации такого запроса:
ВЫБРАТЬ
ИсходныеДанные.Номенклатура КАК Номенклатура,
ИсходныеДанные.МесяцОжидаемогоПрихода КАК МесяцОжидаемогоПрихода,
ИсходныеДанные.ОжидаемыйПриход КАК ОжидаемыйПриход
ПОМЕСТИТЬ ВТ_Данные
ИЗ
&ТаблицаИсточника КАК ИсходныеДанные
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Т1.Номенклатура КАК Номенклатура,
Т1.МесяцОжидаемогоПрихода КАК МесяцОжидаемогоПрихода,
СУММА(ЕСТЬNULL(Т2.ОжидаемыйПриход, 0)) КАК ОжидаемыйПриходНарастающим
ИЗ
ВТ_Данные КАК Т1
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Данные КАК Т2
ПО Т1.Номенклатура = Т2.Номенклатура
И Т1.МесяцОжидаемогоПрихода >= Т2.МесяцОжидаемогоПрихода
СГРУППИРОВАТЬ ПО
Т1.Номенклатура,
Т1.МесяцОжидаемогоПрихода
Проанализируем ситуацию с точки зрения быстродействия системы. Какой метод выбрать для боевой задачи?
>=) имеет квадратичную сложность $O(N^2)$. Если в базе данных накопилось много информации и количество уникальных периодов и товаров исчисляется десятками тысяч, такое левое соединение приведет к колоссальной нагрузке на СУБД. Время формирования отчета вырастет по экспоненте, что может привести к зависанию сервера.ВычислитьВыражение() происходит на уже сгруппированном и подготовленном результирующем наборе данных. Если в базе миллион строк, но в отчет выводится всего 100 строк по товарам и 12 колонок по месяцам, СКД будет рассчитывать формулу только для этих конечных ячеек в оперативной памяти. Это работает мгновенно и абсолютно безопасно для производительности сервера.Вердикт: Метод с использованием встроенных функций СКД предпочтителен в 95% случаев.
---
Реализация нарастающего итога часто сопровождается сопутствующими трудностями. Разберем основные проблемы, с которыми сталкиваются разработчики в реальных проектах, и способы их решения.
Если для определенного товара в конкретном месяце не было движений и ожидаемых приходов, СУБД по умолчанию вообще не вернет строку за этот период. В результате в кросс-таблице колонка этого месяца для данного товара останется пустой или исчезнет, а нарастающий итог может прерваться.
Чтобы решить эту проблему, выполните следующие действия:
NULL). Для этого оберните агрегатную функцию в конструкцию проверки на пустоту:
ВычислитьВыражение("ЕстьNull(Сумма(ОжидаемыйПриход), 0)", "Группировка", , "Первая", "Текущая")
Начинающие разработчики часто пытаются прописать формулу с функцией ВычислитьВыражение() непосредственно на вкладке «Вычисляемые поля». Это грубая методическая ошибка.
В вычисляемых полях выражение рассчитывается для каждой детальной строки исходного набора данных еще до группировки и агрегации. Это приведет к неверным расчетам или ошибкам компоновщика. Правильный подход — создавать вычисляемое поле, оставляя его выражение пустым, а саму формулу нарастающего итога прописывать только на вкладке «Ресурсы».
Если для таблицы требуются общие итоги (всего по строке), стандартное суммирование ресурса с нарастающим итогом выдаст неверное число, так как СКД просто сложит промежуточные накопленные суммы за каждый месяц. Нам же нужен корректный общий итог, который равен значению в последней колонке периода (конечное накопленное значение).
Для вывода правильного общего итога по строке в СКД используется более сложная системная конструкция с использованием массивов:
Сумма(ВычислитьВыражениеСГруппировкойМассив("ВычислитьВыражение(""Сумма(ОжидаемыйПриход)"", , , ""Первая"", ""Текущая"")"))
Эта формула заставляет СКД сначала рассчитать нарастающий итог по колонкам, собрать эти результаты в массив для каждой группировки и затем корректно получить финальное значение общего итога, не удваивая промежуточные данные. Для реализации похожей агрегации строк подойдёт способ сбора значений колонки из нескольких строк в СКД.
Применяя данные подходы в своей работе, вы сможете создавать быстрые, надежные и интерактивные отчеты с нарастающим итогом любой сложности. Для изучения смежных практик можно использовать примеры отчетов на СКД со сложными выражениями и программным формированием.