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

← На главную

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

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

В чем причина дублирования данных в СКД?

Давайте подробно разберем, почему стандартные средства суммирования дают неверный результат. Представим ситуацию: у нас есть документ «Заказ», в котором по спецификации зафиксировано плановое количество номенклатуры — 100 единиц. К этому заказу привязано три документа «Поступление товаров и услуг» (на 30, 40 и 30 единиц соответственно).

Когда мы соединяем эти данные в рамках одного запроса, СУБД возвращает плоскую таблицу, содержащую три строки:

Заказ №1 | Номенклатура | План: 100 | Поступление №1 | Факт: 30
Заказ №1 | Номенклатура | План: 100 | Поступление №2 | Факт: 40
Заказ №1 | Номенклатура | План: 100 | Поступление №3 | Факт: 30

Для СКД эта плоская таблица является единым набором данных. Если мы определим ресурс КоличествоЕдиницПлан как Сумма(КоличествоЕдиницПлан), система сложит значения по всем строкам и вернет итоговое плановое количество равным 300 единиц вместо реальных 100. Это критическая ошибка, искажающая аналитику управленческого учета.

Метод 1. Наведение порядка в архитектуре: Связи наборов данных (Лучшая практика)

Наиболее элегантным, производительным и правильным с точки зрения архитектуры решением является разделение исходного запроса на два независимых набора данных с их последующим связыванием непосредственно на уровне СКД. Рассмотрим этот алгоритм по шагам.

Когда мы разделяем информацию, СКД работает с сущностями разной детальности изолированно и не плодит декартово произведение строк на уровне СУБД. Это избавляет нас от необходимости писать тяжелые и трудноподдерживаемые формулы в ресурсах.

Шаг 1. Создание первого набора данных (Данные заказов)

Добавим в схему компоновки первый набор данных — запрос с именем НаборДанныхЗаказы. В этом запросе мы будем выбирать только те поля, которые относятся к заказам и спецификациям, без какой-либо детализации по поступлениям:


ВЫБРАТЬ
    ЗаказПокупателя.Ссылка КАК Заказ,
    ЗаказПокупателя.Номенклатура КАК Номенклатура,
    ЗаказПокупателя.Количество КАК КоличествоЕдиницПлан
ИЗ
    Документ.ЗаказПокупателя КАК ЗаказПокупателя

Шаг 2. Создание второго набора данных (Данные поступлений)

Добавим второй набор данных-запрос с именем НаборДанныхПоступления. Здесь мы выберем данные по фактическому исполнению заказов:


ВЫБРАТЬ
    ПоступлениеТоваровУслуг.ЗаказПокупателя КАК Заказ,
    ПоступлениеТоваровУслуг.Номенклатура КАК Номенклатура,
    ПоступлениеТоваровУслуг.Ссылка КАК Поступление,
    ПоступлениеТоваровУслуг.Количество КАК КоличествоПоступило,
    ПоступлениеТоваровУслуг.ВалютаДокумента КАК Валюта
ИЗ
    Документ.ПоступлениеТоваровУслуг КАК ПоступлениеТоваровУслуг

Шаг 3. Настройка связей наборов данных

Перейдем на вкладку «Связи наборов данных» в конструкторе схемы компоновки. Нам необходимо связать эти два набора по ключевым полям аналитики. Заполним параметры связи следующим образом:

Источник связи: НаборДанныхЗаказы
Приемник связи: НаборДанныхПоступления
Условие связи: Заказ = Заказ И Номенклатура = Номенклатура

Шаг 4. Настройка ресурсов

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

Для поля КоличествоЕдиницПлан пишем выражение: Сумма(КоличествоЕдиницПлан)
Для поля КоличествоПоступило пишем выражение: Сумма(КоличествоПоступило)

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

Метод 2. Использование сложных вложенных выражений ресурсов в СКД

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

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

Проанализируем синтаксис этой функции:

ВычислитьВыражениеСГруппировкойМассив("Выражение", "Группировки")

В качестве первого параметра передается строковое выражение агрегатной функции, которая рассчитывается на уровне указанной во втором параметре группировки.

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

Шаг 1. Определение ресурса для группировки по Поступлениям

На уровне самых детальных записей (строк поступлений) нам нужно вывести плановое количество. Поскольку оно одинаково для всех поступлений по одной позиции заказа, мы берем максимум или среднее значение:

Выражение: Максимум(КоличествоЕдиницПлан)
Рассчитывать по: Поступление

Шаг 2. Определение ресурса для группировки по Заказам

На уровне группировки «Заказ» нам необходимо сложить уникальные значения планового количества. Если внутри заказа есть несколько строк номенклатуры, мы должны просуммировать их максимумы. Пишем следующее вложенное выражение:

Выражение: Сумма(ВычислитьВыражениеСГруппировкойМассив("Максимум(КоличествоЕдиницПлан)", "Заказ, Номенклатура"))
Рассчитывать по: Заказ

Обратите внимание на использование двойных кавычек внутри строки выражения — это обязательное требование синтаксиса СКД при вложении функций.

Шаг 3. Настройка Общих итогов

Одна из самых частых ошибок разработчиков — отсутствие настройки ресурса для строки «Общий итог». Если мы оставим колонку «Рассчитывать по» пустой для стандартного суммирования, общий итог снова посчитается неверно, собрав все дубли из плоской таблицы.

Чтобы общий итог рассчитывался корректно, мы добавляем еще одну строку в таблицу ресурсов для поля КоличествоЕдиницПлан:

Выражение: Сумма(ВычислитьВыражениеСГруппировкойМассив("Максимум(КоличествоЕдиницПлан)", "Заказ, Номенклатура"))
Рассчитывать по: ОбщийИтог (или оставить колонку "Рассчитывать по..." пустой, чтобы выражение применилось ко всем вышестоящим группировкам).

Сложные конструкции со множественной вложенностью

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


Сумма(ВычислитьВыражениеСГруппировкойМассив("Среднее(ВычислитьВыражениеСГруппировкойМассив(""Сумма(КоличествоЕдиницПлан)"",""Поступление, Валюта""))","Заказ"))

Давайте разберем, как работает эта формула изнутри наружу:

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

Почему вложенные агрегаты — это компромисс?

Несмотря на то, что использование сложных формул ресурсов позволяет решить задачу без изменения структуры запросов, этот подход имеет ряд существенных недостатков, о которых важно помнить при проектировании отчетов:

Низкая производительность: Все расчеты по свертке массивов СКД выполняет программно в оперативной памяти сервера приложений. На больших объемах данных (десятки тысяч строк) это гарантированно приведет к сильным задержкам при формировании отчета или даже к падению рабочего процесса по нехватке памяти.
Сложность отладки и поддержки: Разобраться в трехэтажной формуле с обилием кавычек через полгода будет крайне сложно даже самому автору кода. Любая опечатка в названии группировки приведет к ошибке выполнения отчета.
Зависимость от структуры группировок: Вложенные формулы жестко завязываются на состав полей группировок, указанных в строковом параметре функции. Если пользователь в режиме предприятия изменит структуру отчета (например, удалит группировку или добавит новую промежуточную группировку), расчет ресурсов мгновенно сломается или начнет выдавать неверные данные.

Таким образом, всегда отдавайте приоритет Методу №1 (разделение наборов данных). Прибегайте к сложным выражениям ресурсов только в крайних случаях, когда иные пути оптимизации структуры данных полностью закрыты.