Разберем распространенную и непростую задачу в системе компоновки данных (СКД): как правильно рассчитать итоговое значение в группировке, если исходные данные содержат дубли. Часто бывает, что из-за особенностей связей в запросе одни и те же записи (например, время выполнения одного задания) дублируются в детальных строках отчета. При попытке посчитать сумму стандартной функцией Сумма() результат получается завышенным.
Проблема усложняется, когда нам нужно:
Попытка решить эту задачу "в лоб" с помощью вложенных агрегатных функций, таких как Сумма(ВычислитьВыражение(...)), часто приводит к пустому результату в итоговой строке. Проанализируем, почему так происходит и как найти правильное решение, не прибегая к изменению отчета в конфигураторе.
Рассмотрим ситуацию из примера. Автор пытается посчитать общее время, затраченное на задания для каждого заказчика. Из-за специфики отчета каждая запись о времени выполнения задания дублируется несколько раз. Чтобы в детальных записях отображалось корректное время по одному заданию, используется выражение:
ВычислитьВыражение("Массив(Различные [Задание на работу.Время выполнения (min) (Задания на работу)])", "[Задание на работу], [Задание на работу.Дата], [Задание на работу.Дата завершения], [Задание на работу.Тема обращения (Задания на работу)]")
Это выражение для каждой уникальной комбинации полей, определяющих задание, собирает массив из различных значений времени и, так как оно одно, выводит его. Это работает для детальных записей.
Но когда мы пытаемся обернуть эту конструкцию в функцию Сумма() для итоговых записей, СКД не может обработать такой запрос. Причины могут быть следующими:
ВычислитьВыражение с агрегатной функцией Массив() внутри может возвращать не число или массив чисел, а объект специального типа или его строковое представление (например, "10; 25; 50"). Внешняя функция Сумма() ожидает на входе числовые значения, а не строку или сложный объект, и в итоге возвращает пустой результат.Сумма(ВычислитьВыражение(...)), нарушает логику вычислений СКД при переходе от детальных записей к итоговым.Простое использование Сумма(Различные [Время выполнения]) также не подходит, так как это выражение будет применяться ко всем уровням группировок, а нам нужно, чтобы уникальность значений определялась на уровне конкретного задания, а суммирование происходило на уровне заказчика.
Для решения подобных задач, когда нужно сначала вычислить агрегат на одном уровне (уникальное время для каждого задания), а затем вычислить другой агрегат от полученных результатов на более высоком уровне (сумма времен по заказчику), в СКД существует специальная функция — ВычислитьВыражениеСГруппировкойМассив.
Эта функция идеально подходит для сценариев "двойного агрегирования". Она позволяет вычислить выражение в контексте определенной группировки и вернуть результат в виде массива значений, который затем можно передать внешней агрегатной функции, например, Сумма(). Для более подробного изучения работы со схоми механизмами можно использовать примеры применения функций СКД Массив и СоединитьСтроки.
Разберем по шагам, как настроить пользовательское поле.
ВычислитьВыражение("Массив(Различные [Задание на работу.Время выполнения (min) (Задания на работу)])", "Задание на работу")
Важно: для более точной работы контекста здесь достаточно указать только ключевую группировку "Задание на работу".
Сумма(ВычислитьВыражениеСГруппировкойМассив("Сумма(Различные [Задание на работу.Время выполнения (min) (Задания на работу)])", "Задание на работу"))
Давайте подробно проанализируем, как работает эта формула в итоговой строке по заказчику:
"Сумма(Различные [Задание на работу.Время выполнения (min) (Задания на работу)])" — это внутреннее выражение, которое будет вычисляться. Так как для одного задания время выполнения должно быть одним и тем же, эта часть по сути вернет нам уникальное значение времени для одного конкретного задания."Задание на работу" — это второй параметр, который указывает контекст. Он говорит системе: "выполни внутреннее выражение для каждой уникальной записи в группировке 'Задание на работу'".ВычислитьВыражениеСГруппировкойМассив(...) — основная функция. В рамках текущей группировки (например, "Заказчик") она "пробежится" по всем вложенным в нее заданиям на работу. Для каждого задания она вычислит внутреннее выражение (получит его уникальное время) и соберет все эти значения в один массив. Если у заказчика было три задания со временем 10, 25 и 50 минут, функция вернет массив чисел [10, 25, 50].Сумма(...) — внешняя агрегатная функция. Она получает на вход готовый массив чисел от ВычислитьВыражениеСГруппировкойМассив и корректно его суммирует. В нашем примере она посчитает 10 + 25 + 50 и выведет итоговый результат 85.Таким образом, мы сначала получаем уникальные значения на нижнем уровне, а затем агрегируем их на верхнем, решая проблему двойного агрегирования чисто средствами выражений СКД.
В ходе обсуждения на форуме был предложен вариант использования настройки "Рассчитывать по..." на вкладке "Ресурсы". Этот способ тоже может быть рабочим в некоторых случаях. Он позволяет жестко задать, для каких группировок должен рассчитываться тот или иной ресурс. Однако подход с использованием выражений в пользовательском поле является более гибким и мощным, так как позволяет описать сложную логику вычислений, не затрагивая основные настройки ресурсов отчета, и полностью соответствует задаче решить проблему на уровне пользовательских настроек. Сравнение этих подходов с практическими примерами приведено в публикации по настройке различных расчетов итогов в СКД.
В заключение: если вы столкнулись с необходимостью посчитать итог по уникальным значениям в отчете СКД, не спешите усложнять запрос. Используйте функцию ВычислитьВыражениеСГруппировкойМассив — это штатный и эффективный инструмент для решения задач двойного агрегирования прямо в настройках варианта отчета.