Как в СКД рассчитать сумму средних значений по подчиненным группировкам?

← На главную

При разработке отчетов на базе системы компоновки данных (СКД) часто возникает задача нестандартного расчета итоговых показателей. Типовые агрегатные функции, такие как Сумма, Среднее или Количество, по умолчанию рассчитываются по всем детальным записям набора данных. Однако в аналитических отчетах нередко требуется двухуровневый расчет: например, для дочерней группировки рассчитать среднее значение, а для родительской — сложить полученные средние значения, а не суммировать исходные строки.

Рассмотрим структуру исходных данных и разберем практические способы настройки таких итогов в СКД.

Постановка задачи и анализ структуры данных

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

Пользователь 1 / Контрагент 1 / Номенклатура 1 / Сумма = 100
Пользователь 1 / Контрагент 1 / Номенклатура 2 / Сумма = 100
Пользователь 1 / Контрагент 2 / Номенклатура 1 / Сумма = 100

Нам необходимо вывести отчет с иерархией Пользователь → Контрагент → Номенклатура со следующими правилами расчета:

На уровне группировки Контрагент сумма рассчитывается как среднее значение по номенклатуре (для Контрагента 1 среднее от 100 и 100 равно 100; для Контрагента 2 среднее равно 100).
На уровне группировки Пользователь и в общем итоге должна выводиться сумма средних по контрагентам (100 + 100 = 200). Если бы мы использовали стандартную сумму по детальным записям, отчет показал бы 300, что не соответствует бизнес-логике.

Решение с помощью функции ВычислитьВыражениеСГруппировкойМассив

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

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

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

Правила указания контекста и имен группировок

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

Имена полей вместо имен группировок: во втором параметре функции ВычислитьВыражениеСГруппировкойМассив указываются имена полей компоновки данных (например, "Контрагент"), а не пользовательские имена элементов дерева настроек, заданные через команду «Установить имя...».
Составной контекст: если группировка Контрагент подчинена группировке Пользователь, для исключения некорректного объединения одинаковых контрагентов у разных пользователей безопаснее указывать цепочку полей через запятую:
```
Сумма(ВычислитьВыражениеСГруппировкойМассив("Среднее(Сумма)", "Пользователь, Контрагент"))
```

Использование сложных и вложенных условий в ресурсах

Иногда требуется не просто просуммировать средние, а применить тарифную сетку или условия ВЫБОР КОГДА ... к промежуточным итогам. Если попытаться напрямую вложить одну агрегатную функцию в другую, СКД выдаст синтаксическую ошибку о недопустимости вложенных агрегатов.

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


Сумма(ВычислитьВыражениеСГруппировкойМассив(
    "ВЫБОР 
        КОГДА Сумма(ВычислитьВыражениеСГруппировкойМассив(""Сумма(ВаловаяПрибыль)"", ""Контрагент"")) >= &МенеджерМелкийСумма 
             И Сумма(ВычислитьВыражениеСГруппировкойМассив(""Сумма(ВаловаяПрибыль)"", ""Контрагент"")) < &МенеджерСреднийСумма 
            ТОГДА &ТарифМелкие
        КОГДА Сумма(ВычислитьВыражениеСГруппировкойМассив(""Сумма(ВаловаяПрибыль)"", ""Контрагент"")) >= &МенеджерСреднийСумма 
             И Сумма(ВычислитьВыражениеСГруппировкойМассив(""Сумма(ВаловаяПрибыль)"", ""Контрагент"")) < &МенеджерКрупныйСумма 
            ТОГДА &ТарифСредние
        КОГДА Сумма(ВычислитьВыражениеСГруппировкойМассив(""Сумма(ВаловаяПрибыль)"", ""Контрагент"")) >= &МенеджерКрупныйСумма 
            ТОГДА &ТарифКрупные 
        ИНАЧЕ 0 
    КОНЕЦ", 
    "Контрагент"
))

Альтернативные способы реализации

Если отчет формируется по большим массивам данных, контекстные вычисления в СКД могут вызывать повышенное потребление оперативной памяти. В таких случаях рассмотрим альтернативные архитектурные решения:

Предварительный расчет в запросе: перенести вычисление средних значений во временную таблицу на уровне запроса. В СКД передаются уже готовые агрегированные показатели, что существенно ускоряет построение отчета.
Использование нескольких наборов данных и связей:
Создадим два набора данных:
1. НаборДанных_Итоги: группирует данные по пользователям и контрагентам с расчетом СРЕДНЕЕ(Сумма) КАК СредняяСумма.
2. НаборДанных_Детали: содержит подробные строки по номенклатуре.
На вкладке Связи наборов данных свяжем их по полям Пользователь и Контрагент. Это позволит на верхних уровнях группировки рассчитывать обычную Сумма(СредняяСумма) без использования массивных контекстных функций.