Как в СКД рассчитать нарастающий итог по колонкам кросс-таблицы?

При разработке аналитических отчетов в системе компоновки данных (СКД) 1С часто возникает задача расчета накопительного итога по горизонтали (в разрезе колонок кросс-таблицы, например, по месяцам или дням периода). Разберем, почему стандартные формулы могут работать некорректно, и рассмотрим проверенные способы реализации нарастающего итога средствами СКД и языка запросов.

Способ 1. Использование функции ВычислитьВыражение в ресурсах СКД

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

Разберем по шагам правильную настройку схемы компоновки данных:

1. Перейдем на вкладку Вычисляемые поля схемы компоновки данных. Добавим новое поле, например, с именем СуммаНарастающимИтогом. Поле выражения оставим пустым, задав только заголовок и путь к данным.
2. Перейдем на вкладку Ресурсы. Добавим наше поле СуммаНарастающимИтогом в список ресурсов.
3. В колонке Выражение укажем формулу расчета с явным указанием диапазона: ВычислитьВыражение("Сумма(СуммаОборот)", "Период", , "Первая", "Текущая")
4. В колонке Рассчитывать по... обязательно выберем имя группировки колонок (например, Период), чтобы расчет производился именно по горизонтали, не затрагивая итоги других уровней.

Проанализируем параметры функции ВычислитьВыражение подробнее:

1. Выражение — агрегатная функция над суммируемым полем, например "Сумма(СуммаОборот)".
2. Имя группировки — имя группировки, в пределах которой рассчитывается выражение. Если не указать группировку колонок, СКД по умолчанию выполнит расчет по строкам текущей группировки (вертикально).
3. Область вычисления — в базовом сценарии оставляется пустой.
4. Начальная позиция — указывается "Первая", чтобы брать данные от первой колонки группировки.
5. Конечная позиция — указывается "Текущая", чтобы суммировать значения включительно по текущую колонку.

Особенности расчета итогов и пустых значений в СКД

При использовании вычислений по колонкам важно учитывать поведение платформы в нестандартных ситуациях:

1. Пропуски периодов: если в выборке отсутствуют движения по какому-либо периоду, соответствующая колонка не сформируется или вернет значение NULL, что может прервать цепочку нарастающего итога. Для решения этой проблемы формируйте в запросе непрерывную шкалу дат либо используйте функцию ЕстьNULL.
2. Общие итоги кросс-таблицы: по умолчанию в общих итогах строки может отображаться значение последней колонки или сумма всех накопленных значений. Чтобы настроить корректный общий итог, на вкладке Ресурсы можно добавить вторую строку для этого же поля с пустой группировкой («Рассчитывать по...» = ОбщийИтог) и применить агрегацию массива через ВычислитьВыражениеСГруппировкойМассив.

Способ 2. Расчет накопительного итога в тексте запроса

Если по каким-либо причинам расчет на уровне выражений СКД не подходит, нарастающий итог можно рассчитать еще на этапе выполнения запроса к базе данных.

Посмотрим на пример классического левого соединения таблицы с самой собой по условию «меньше или равно»:

ВЫБРАТЬ
    Таблица.Период КАК Период,
    Таблица.Номенклатура КАК Номенклатура,
    СУММА(Таблица1.СуммаОборот) КАК СуммаНарастающимИтогом
ИЗ
    ВТ_Данные КАК Таблица
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Данные КАК Таблица1 
        ПО Таблица1.Период <= Таблица.Период
        И Таблица1.Номенклатура = Таблица.Номенклатура
СГРУППИРОВАТЬ ПО
    Таблица.Период,
    Таблица.Номенклатура

Проанализируем ограничения данного метода:

1. Производительность: нестрогое соединение Таблица1.Период <= Таблица.Период создает квадратичную сложность выборки, что приводит к замедлению отчета на больших объемах данных.
2. Пользовательские отборы: отборы, установленные пользователем в настройках СКД, накладываются на итоговый результат выборки и могут нарушить целостность накопленной суммы, если фильтрация не передается внутрь параметров виртуальных таблиц.

В современных версиях платформы 1С:Предприятие 8.3 при поддержке СУБД рекомендуется вместо нестрогого соединения использовать оконные функции в запросе, например: СУММА(Поле) НАД (УПОРЯДОЧИТЬ ПО Период), что обеспечивает значительно более высокое быстродействие.