Разработка отчетов в Системе компоновки данных (СКД) часто сталкивает нас со стандартными математическими агрегациями: просуммировать количество, найти максимум по дате или среднюю цену. Однако реальные бизнес-требования часто оказываются сложнее стандартных функций СУБД. Типичный пример — задача двойного (или разноуровневого) агрегирования данных. Нам требуется рассчитать среднее значение показателей на нижнем уровне группировки, но на верхнем уровне мы хотим видеть не общее среднее по всем документам, а сумму этих локальных средних значений.
В этой статье мы подробно разберем, почему возникает эта проблема, проанализируем математику расчета и пошагово настроим схему компоновки данных для получения абсолютно корректных результатов с помощью встроенного языка выражений СКД.
Рассмотрим практический пример. Предположим, у нас есть отчет по остаткам и поступлениям товаров на складах в разрезе номенклатуры и документов движения. Нам необходимо вывести показатели по полю Сумма следующим образом:
Проанализируем ситуацию на конкретных цифрах, чтобы понять, где именно СКД по умолчанию допускает логическую ошибку. Допустим, у нас на складе есть две позиции номенклатуры, которые приходили по разным накладным:
Номенклатура "Тракторы":
Номенклатура "Лопаты":
Если мы хотим узнать общую стоимость запасов на Складе на основе средних показателей номенклатуры, мы должны сложить эти средние значения:
Итого по Складу = 13 500 000 (Тракторы) + 500 (Лопаты) = 13 500 500 руб.
Однако если мы просто укажем для ресурса Сумма стандартное выражение Среднее(Сумма), то СКД на уровне склада выполнит простое усреднение всех четырех документов:
Стандартный расчет СКД = (13 000 000 + 14 000 000 + 300 + 700) / 4 = 6 750 250 руб.
Этот результат в корне неверен и полностью искажает управленческую отчетность. Наша цель — заставить систему выполнить сначала локальное усреднение внутри номенклатуры, а затем сложить полученные массивы данных на верхнем уровне группировки.
Для решения задач многоуровневого агрегирования в платформе 1С:Предприятие предусмотрена мощная функция ВычислитьВыражениеСГруппировкойМассив. Данная функция позволяет свернуть детальные записи по определенному разрезу внутри текущего контекста расчета, выполнить над ними первичное агрегирование и вернуть результат в виде массива значений, к которому затем применяется внешняя функция.
Давайте выясним причину, почему простая конструкция вида ВычислитьВыражение(Сумма(Сумма), "Номенклатура") не работает или приводит к исчезновению данных. Дело в том, что функции работы с выражениями компоновки требуют строгого соблюдения контекста выполнения и правильной настройки полей расчета на вкладке "Ресурсы".
Рассмотрим правильный алгоритм настройки ресурсов схемы компоновки данных шаг за шагом.
Чтобы не путать СКД одинаковыми именами физических полей и ресурсов, хорошей практикой является добавление вычисляемого поля на одноименной вкладке в конструкторе схемы компоновки данных.
СуммаДляСклада.Перейдем на вкладку "Ресурсы". Главный секрет кроется в том, что мы можем добавлять одно и то же поле в список ресурсов несколько раз, указывая для каждой строки свое выражение расчета и область применения (колонку "Рассчитывать по...").
Мы добавим наше поле СуммаДляСклада дважды со следующими настройками:
Строка 1 (для уровня Номенклатуры):
СуммаДляСкладаСреднее(Сумма)Номенклатура (и, при необходимости, для подчиненной группировки Документ).Строка 2 (для уровня Склада и Общих итогов):
СуммаДляСкладаСумма(ВычислитьВыражениеСГруппировкойМассив("Среднее(Сумма)", "Номенклатура"))Склад и ОбщиеИтоги.Проанализируем логику работы процессора компоновки данных при расчете итогов по Складу:
Склад.Сумма(ВычислитьВыражениеСГруппировкойМассив("Среднее(Сумма)", "Номенклатура")).Номенклатура.Среднее(Сумма).[13500000, 500].Сумма(...) производит сложение элементов полученного массива, возвращая итог: 13 500 500.Рассмотрим неочевидные тонкости, с которыми вы можете столкнуться при использовании функций семейства ВычислитьВыражение... в крупных корпоративных отчетах.
Если в вашей базе данных присутствуют документы с нулевой суммой (например, корректировочные накладные или бесплатные образцы), стандартная функция Среднее() учтет эти строки при делении, что приведет к занижению итогов по номенклатуре. Чтобы этого избежать, рекомендуется на уровне запроса заменять нулевые значения на NULL.
Посмотрим на пример выражения в запросе:
ВЫБОР
КОГДА ХозяйственнаяОперация.СуммаДокумента = 0
ТОГДА NULL
ИНАЧЕ ХозяйственнаяОперация.СуммаДокумента
КОНЕЦ КАК Сумма
Поскольку математические функции агрегации СКД (включая Среднее) полностью игнорируют значения NULL, расчет среднего значения по документам останется абсолютно корректным.
Если в вашем отчете используется группировка по иерархии номенклатуры (Номенклатура Иерархия), стандартное указание имени поля "Номенклатура" внутри функции ВычислитьВыражениеСГруппировкойМассив может не дать ожидаемого результата для родительских папок справочника.
В этом случае необходимо убедиться, что вы правильно указали имя иерархического поля группировки в параметрах функции, либо добавить третью строку в таблицу ресурсов специально для расчета иерархических уровней.
Необходимо понимать, что функции ВычислитьВыражениеСГруппировкойМассив и ВычислитьВыражениеСГруппировкойТаблица выполняются не на уровне СУБД MS SQL/PostgreSQL, а непосредственно в оперативной памяти сервера приложений 1С в процессе компоновки результата.
Если ваш отчет обрабатывает миллионы записей детальных документов, использование таких функций может повлечь за собой:
rphost.Альтернативный вариант: Если вы заметили проблемы с производительностью, лучшим решением будет перенос логики двойного агрегирования непосредственно в текст SQL-запроса схемы компоновки. Сделайте предварительное группирование и расчет средних во временных таблицах запроса, а в СКД выгружайте уже подготовленные агрегированные данные, которые останется лишь просто сложить стандартной функцией Сумма().