При разработке отчетов на базе Системы Компоновки Данных (СКД) разработчики часто сталкиваются с задачей, когда для разных уровней иерархии отчета необходимо применять разные формулы расчета итогов. Разберем типичную ситуацию: имеется многоуровневая группировка вида Общий итог — Организация — Клиент — Заказ клиента — Детальные записи. Задача состоит в том, чтобы для группировки Заказ клиента вычислить среднее значение по детальным записям, а для всех вышестоящих группировок и общего итога — просто просуммировать полученные средние значения по заказам.
Рассмотрим подробно, как правильно решить эту задачу, какие штатные механизмы СКД для этого предназначены и какие существуют альтернативные подходы.
Самый правильный и рекомендуемый подход — использовать возможности колонки «Рассчитывать по…» на вкладке «Ресурсы» в настройках СКД. Одной из частых ошибок является попытка написать единую сложную формулу сразу для всех уровней. Вместо этого мы разобьем логику на несколько строк для одного и того же ресурса.
Выполним настройку по шагам:
Заказ клиента) добавим первую строку для нашего поля (например, СуммаОплаты). В колонке «Выражение» пропишем базовую функцию: Среднее(СуммаОплаты). В колонке «Рассчитывать по…» жестко укажем нашу группировку: ЗаказКлиента.
Сумма(ВычислитьВыражениеСГруппировкойМассив("Среднее(СуммаОплаты)", "ЗаказКлиента"))
Благодаря такому разделению, ядро СКД четко понимает контекст: на уровне заказа оно считает обычное среднее, а на уровнях выше — берет массив средних значений подчиненных заказов и складывает их.
При использовании функции ВычислитьВыражениеСГруппировкойМассив необходимо учитывать несколько важных ограничений и особенностей платформы:
ВычислитьВыражениеСГруппировкойМассив друг в друга часто возникает платформенная ошибка «Использование вложенных агрегатных функций не допускается». Платформа плохо справляется с глубоким синтаксическим разбором внутри строковых параметров, особенно там, где требуется экранирование кавычек (двойные "" или четверные """").Среднее() включает в расчетную базу нулевые значения. Если в детальных записях встречаются строки с нулевой суммой оплаты, они увеличат знаменатель и существенно занизят итоговое среднее значение по заказу.Если логика расчета становится слишком сложной для вкладки «Ресурсы» или возникают проблемы с производительностью, мы можем перенести часть вычислений на другие этапы компоновки данных. Проанализируем эти варианты.
Чтобы не перегружать СКД сложными вычислениями ресурсов, можно перенести математику непосредственно в текст запроса. Мы создаем временную таблицу, в которой группируем данные по заказам и сразу вычисляем среднюю сумму. Затем эту временную таблицу мы соединяем с основным набором данных. В результате на вкладку «Ресурсы» в СКД попадает уже готовое среднее значение по заказу, и для всех верхних уровней нам остается применить простейшую функцию Сумма() без дополнительных ухищрений.
Как мы выяснили ранее, нули могут исказить расчет среднего. Если бизнес-логика требует исключить пустые или нулевые оплаты из расчета среднего значения, мы можем обработать их прямо в запросе набора данных. Математика СКД устроена так, что значения NULL полностью игнорируются при агрегации (они не попадают ни в числитель суммы, ни в знаменатель количества). Используем конструкцию ВЫБОР:
ВЫБОР
КОГДА СуммаОплаты = 0 ТОГДА NULL
ИНАЧЕ СуммаОплаты
КОНЕЦ
Заменив нули на NULL, мы обеспечим корректный расчет функции Среднее().
Еще один архитектурный подход — разделить потоки данных. Мы создаем два отдельных набора данных. Первый набор выбирает сырые детальные записи. Второй набор с помощью языка запросов агрегирует данные до уровня заказов, рассчитывая средние значения.
Затем мы объединяем эти два набора на вкладке «Связи наборов данных». Этот метод несколько усложняет структуру схемы компоновки, но делает расчет ресурсов максимально простым и прозрачным: для иерархии применяется обычное суммирование полей, пришедших из второго (уже сгруппированного) набора данных.