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