Как правильно рассчитать разность полей из разных наборов данных в группировках СКД

При создании отчетов в системе компоновки данных (СКД), объединяющих несколько источников (например, регистры заказов покупателей и заказов поставщикам), часто возникает задача подсчета разности или других арифметических операций между показателями на уровне группировок. Разберем, почему стандартные формулы вида Сумма(Поле1 - Поле2) или вызовы функции ВычислитьВыражение возвращают пустые значения, и как правильно настроить формулы ресурсов в СКД.

Проанализируем типовую ситуацию: в отчете используется набор данных «Объединение», состоящий из двух запросов к остаточно-оборотным регистрам. Из первого набора выбираются данные по заказам покупателей, из второго — по заказам поставщикам. В результате объединения строки группируются по общему полю (например, Номенклатура), но попытка вычесть один приход из другого в строке группировки не дает ожидаемого результата.

Причина возникновения проблемы: влияние значений NULL

Выясним причину некорректного расчета. При объединении наборов данных строки из разных источников попадают в результирующую выборку как отдельные записи. В строках первого источника поля второго источника отсутствуют и равны NULL, а в строках второго источника значение NULL принимают поля первого источника.

Рассмотрим, что происходит при вычислении выражения Сумма(ЗаказыПокупателейКоличествоПриход - ЗаказыПоставщикамКоличествоПриход):

1. Система пытается вычислить выражение внутри агрегатной функции построчно для каждой детальной записи до выполнения суммирования.
2. Для строк первого набора данных операция выглядит как Число - NULL, результатом которой по правилам реляционной алгебры и встроенного языка является NULL.
3. Для строк второго набора данных выполняется NULL - Число, что также дает NULL.
4. Агрегатная функция Сумма() суммирует полученные значения NULL, и в итоге группировка отображает пустое значение или ноль.

Аналогично попытки использовать функцию ВычислитьВыражение("ЗаказыПокупателейКоличествоПриход - ЗаказыПоставщикамКоличествоПриход", "Номенклатура") в вычисляемых полях не приносят результата, так как функция не заменяет агрегацию на уровне группировки и сталкивается с тем же значением NULL в неагрегированных строках.

Решение: расчет разности агрегированных сумм на вкладке «Ресурсы»

Чтобы получить корректный результат в группировке, необходимо сначала выполнить агрегацию каждого поля по отдельности (свернув итоги по всем строкам группировки), а затем вычислить их разность. На вкладке Ресурсы СКД настраиваем расчет поля следующим образом:

Сумма(ЕстьNULL(ЗаказыПокупателейКоличествоПриход, 0)) - Сумма(ЕстьNULL(ЗаказыПоставщикамКоличествоПриход, 0))

Разберем по шагам, как работает данная формула:

1. Функция ЕстьNULL(Поле, 0) заменяет неопределенные значения в строках другого набора на числовой ноль.
2. Функция Сумма() по первому слагаемому рассчитывает общий приход по покупателям в рамках текущей группировки.
3. Функция Сумма() по второму слагаемому рассчитывает общий приход по поставщикам.
4. СКД производит вычитание одного свернутого итога из другого, получая верную дельту на уровне группировки (номенклатуры, склада или общих итогов отчета).

Оптимизация на уровне запроса

Посмотрим на альтернативный и более производительный вариант реализации. Вместо использования набора данных типа «Объединение» можно объединить запросы непосредственно в тексте одного набора данных «Запрос» с помощью конструкции ОБЪЕДИНИТЬ ВСЕ, явно подставляя числовой ноль в недостающие поля:

ВЫБРАТЬ
    ЗаказыПокупателей.Номенклатура КАК Номенклатура,
    ЗаказыПокупателей.Заказ КАК Заказ,
    ЗаказыПокупателей.КоличествоПриход КАК ЗаказыПокупателейКоличествоПриход,
    0 КАК ЗаказыПоставщикамКоличествоПриход
ИЗ
    РегистрНакопления.ЗаказыПокупателей.Обороты(&НачалоПериода, &КонецПериода, , ) КАК ЗаказыПокупателей

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
    ЗаказыПоставщикам.Номенклатура,
    ЗаказыПоставщикам.Заказ,
    0,
    ЗаказыПоставщикам.КоличествоПриход
ИЗ
    РегистрНакопления.ЗаказыПоставщикам.Обороты(&НачалоПериода, &КонецПериода, , ) КАК ЗаказыПоставщикам

В этом случае в выборке не будет значений NULL, а формулу на вкладке «Ресурсы» можно упростить до:

Сумма(ЗаказыПокупателейКоличествоПриход) - Сумма(ЗаказыПоставщикамКоличествоПриход)

Понятие детальных записей и особенности расчета ресурсов

Рассмотрим важную особенность функционирования СКД: различие между группировками и детальными записями.

1. Детальные записи — это элемент структуры отчета, у которого в списке группировочных полей не выбрано ни одно поле (в структуре компоновки отображается как пустая строка или элемент <Детальные записи>). В этой ветке выводятся строки напрямую из набора данных без применения группировочных агрегатов.
2. Группировка по полю — даже если группировка является самым нижним уровнем иерархии (например, группировка по документу или номенклатуре), она остается группировкой. Значения всех ресурсных полей в ней рассчитываются строго по агрегатным формулам, заданным на вкладке Ресурсы.

Если в схеме компоновки необходимо разделять логику расчета для строк выборки и итоговых строк группировок, на вкладке Ресурсы можно добавить поле несколько раз, указав в колонке Рассчитывать по... соответствующий уровень (отдельно для детальных записей, конкретных группировок и общего итога).