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

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

Постановка задачи и анализ проблемы

Представим структуру отчета со следующими вложенными группировками: Поле5 -> Поле4 -> Поле3 -> Поле2 -> Поле1. На уровнях Поле1 и Поле2 значение ресурса КоличествоОстаток одинаково для всех записей внутри родительской группы. Если мы просто укажем СУММА(КоличествоОстаток), то получим завышенные данные, так как система сложит все повторяющиеся значения.

Нам необходимо добиться следующего поведения:

1. На уровнях Поле1 и Поле2 значение должно выводиться без суммирования (или быть равным 0, если это требуется по условиям задачи).
2. На уровнях Поле3, Поле4 и Поле5 система должна суммировать уникальные значения, относящиеся к этим группировкам.
3. В Общем итоге должна получиться корректная сумма всех уникальных показателей.

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

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

Рассмотрим пошагово настройку ресурса на вкладке «Ресурсы» в схеме компоновки данных. Для того чтобы исключить суммирование на нижних уровнях, мы можем явно прописать выражение для каждого уровня группировки.

Для уровней Поле1 и Поле2 мы устанавливаем выражение, равное 0 или просто выбираем само поле без агрегации, если суммирование там запрещено. Основная логика начинается с уровня Поле3.

Для группировок Поле3, Поле4 и Поле5 используем следующее выражение:

СУММА(ВычислитьВыражениеСГруппировкойМассив("Максимум(КоличествоОстаток)", "Поле3"))

Разберем, что происходит в этом выражении:

• "Максимум(КоличествоОстаток)" — это первый параметр функции. Мы указываем СКД, как получить значение для каждой группы Поле3. Использование Максимум (или Минимум, или Среднее) критически важно. Если в детальных записях группы значения одинаковы, любая из этих функций вернет нужное число. Если написать просто "КоличествоОстаток", система может повести себя непредсказуемо, взяв случайное значение из детальных записей.
• "Поле3" — второй параметр. Он говорит системе: «Сгруппируй данные по Поле3, вычисли для каждой группы Максимум и сложи эти результаты в массив».
• СУММА(...) — внешняя агрегатная функция, которая складывает все элементы полученного массива.

Такой подход отлично работает для иерархии внутри отчета, но часто «ломается» на этапе расчета общего итога. Выясним, почему это происходит.

Особенности расчета Общего итога

Частая ошибка программистов заключается в использовании того же самого выражения для расчета общих итогов. В контексте общего итога СКД пытается вычислить массив по Поле3 для всей выборки. Однако, если одно и то же значение Поле3 встречается в разных ветках дерева (например, под разными Поле4), простая группировка по одному полю может привести к потере данных или их некорректному объединению.

Чтобы общий итог считался верно, необходимо расширить контекст группировки во втором параметре функции. Рассмотрим финальное решение, к которому приходят опытные разработчики:

Для поля Общий итог в выражении ресурса следует указать:

СУММА(ВычислитьВыражениеСГруппировкойМассив("Максимум(КоличествоОстаток)", "Поле3, Поле4, Поле5"))

Важный нюанс: указание нескольких полей через запятую ("Поле3, Поле4, Поле5") заставляет СКД строить массив на основе уникальных комбинаций всех этих полей. Это гарантирует, что система «пробежится» по всем веткам отчета и не пропустит ни одного значения, которое должно участвовать в итоговой сумме. Это восстанавливает полный контекст данных, который теряется при указании только одного поля группировки.

Итоговая схема настройки ресурсов

Проанализируем итоговую таблицу настроек на вкладке «Ресурсы»:

1. Поле: КоличествоОстаток
   Выражение: 0
   Рассчитывать по: Поле1, Поле2
2. Поле: КоличествоОстаток
   Выражение: СУММА(ВычислитьВыражениеСГруппировкойМассив("Максимум(КоличествоОстаток)", "Поле3"))
   Рассчитывать по: Поле3, Поле4, Поле5
3. Поле: КоличествоОстаток
   Выражение: СУММА(ВычислитьВыражениеСГруппировкойМассив("Максимум(КоличествоОстаток)", "Поле3, Поле4, Поле5"))
   Рассчитывать по: Общий итог

Рекомендации по производительности и альтернативы

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

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

• Использование ролей полей: Если КоличествоОстаток является классическим остатком, попробуйте настроить роль поля «Остатки» в наборе данных. Укажите тип остатка (Начальный/Конечный) и поле периода. В этом случае СКД применит встроенные механизмы расчета итогов.
• Подготовка данных в запросе: Самый производительный способ — передать в СКД уже «чистые» данные. Используйте вложенные запросы или временные таблицы, чтобы свернуть избыточные записи до уровня Поле3 еще на этапе выполнения запроса к базе данных.
• Связи наборов данных: Иногда задачу можно решить, разбив запрос на два набора данных и связав их в СКД. Первый набор содержит уникальные показатели уровня Поле3, второй — детальные записи. Суммирование ресурса из первого набора будет происходить корректно «из коробки».

Тем не менее, если структура данных сложная, а отчет должен быть гибким, использование ВычислитьВыражениеСГруппировкойМассив с явным указанием списка полей группировки для общего итога является наиболее универсальным и надежным решением «в лоб».