При разработке отчетов на Системе компоновки данных (СКД) программисты часто сталкиваются с классической проблемой: дублированием или «задвоением» числовых показателей на верхних уровнях группировок. Типичный пример — отчет с иерархией Контрагент > Договор > Документ движения, где в качестве ресурса выступает жестко заданная сумма договора. Если по договору проведено несколько документов движения, стандартный расчет ресурса через функцию Сумма() сложит сумму договора столько раз, сколько документов привязано к этому договору. В итоге мы получим критическую ошибку в отчете.
В этой статье мы подробно разберем, почему возникает эта проблема, и рассмотрим три надежных практических способа ее решения.
Когда мы пишем один запрос, который соединяет таблицу договоров и таблицу документов движений (или регистр накопления), СУБД возвращает плоскую таблицу. В каждой строке этой таблицы, соответствующей конкретному документу, будет дублироваться реквизит «Сумма договора». Если у нас есть договор на сумму 50 000 рублей, по которому проведено 5 документов оплаты, то в результирующей выборке строка договора повторится 5 раз. При простом суммировании СКД сложит эти значения и покажет итог в 250 000 рублей вместо положенных 50 000 рублей.
Для решения этой проблемы мы можем использовать возможности агрегатных функций СКД, архитектуру связей наборов данных или вычисляемые поля. Рассмотрим каждый из этих вариантов подробнее.
---
Это наиболее современный и изящный способ, который позволяет решить задачу в рамках одного набора данных (одного запроса) без изменения структуры выборки. Мы настроим расчет ресурса таким образом, чтобы СКД понимала контекст каждой группировки и рассчитывала сумму по уникальным ключам.
Для реализации этого метода перейдем на вкладку «Ресурсы» в схеме компоновки данных. Нам потребуется добавить наше поле СуммаДоговора трижды с разными настройками расчета для каждого уровня группировки:
Максимум(СуммаДоговора) (также можно использовать Среднее или Минимум). Поскольку в рамках одного договора сумма всегда константна, любая из этих функций вернет точное значение договора, проигнорировав количество строк с документами внутри него.Договор.Сумма(ВычислитьВыражениеСГруппировкойМассив("Максимум(СуммаДоговора)", "Договор")).Контрагент.ВычислитьВыражениеСГруппировкойМассив собирает массив из максимальных сумм по каждому уникальному договору в разрезе текущего контрагента. Затем внешняя функция Сумма складывает элементы этого массива. Если у контрагента 2 договора (на 10 000 и 20 000 рублей), система получит массив из двух элементов и корректно сложит их: 10 000 + 20 000 = 30 000 рублей, сколько бы документов внутри этих договоров ни находилось.Сумма(ВычислитьВыражениеСГруппировкойМассив("Максимум(СуммаДоговора)", "Договор")).Обратите внимание на группировку «Документ» (детальные записи). Чтобы на уровне документа сумма договора вообще не выводилась (так как она не имеет смысла для конкретного регистратора), мы просто не добавляем строку расчета ресурса для этой группировки. В настройках структуры отчета для группировки «Документ» также можно зайти во вкладку «Выбранные поля», снять флаг «Авто» и вручную перетащить только те поля, которые действительно должны отображаться на детальном уровне, исключив из них СуммаДоговора.
---
Если вам не хочется перегружать отчет сложными агрегатными формулами СКД или если отчет работает с огромным объемом данных (где расчеты массивов в памяти могут замедлить работу), оптимальным решением станет разделение запроса на два набора данных.
Давайте настроим эту схему шаг за шагом:
Шаг 1. Создаем первый набор данных (НаборДанных1 - Договоры)
Этот запрос будет возвращать только уникальный список контрагентов, договоров и сумм по ним. Здесь на каждую комбинацию контрагента и договора будет приходиться строго одна строка:
ВЫБРАТЬ
Договоры.Владелец КАК Контрагент,
Договоры.Ссылка КАК Договор,
Договоры.СуммаДоговора КАК СуммаДоговора
ИЗ
Справочник.ДоговорыКонтрагентов КАК Договоры
ГДЕ
НЕ Договоры.ПометкаУдаления
Шаг 2. Создаем второй набор данных (НаборДанных2 - Движения)
Этот запрос будет выбирать детальные записи по хозяйственным операциям и документам движения:
ВЫБРАТЬ
Взаиморасчеты.ДоговорКонтрагента КАК Договор,
Взаиморасчеты.Регистратор КАК ДокументДвижения,
Взаиморасчеты.СуммаОборот КАК СуммаОборота
ИЗ
РегистрНакопления.ВзаиморасчетыСКонтрагентами.Обороты КАК Взаиморасчеты
Шаг 3. Настраиваем связи наборов данных
Переходим на вкладку «Связи наборов данных» и добавляем новую строку:
НаборДанных1НаборДанных2Договор = ДоговорШаг 4. Настройка ресурсов
Поскольку поле СуммаДоговора физически присутствует только в первом наборе данных, где записи уникальны по договорам, СКД применит к нему стандартную группировку без дублирования. На вкладке ресурсов мы пишем обычную простую формулу:
СуммаДоговораСумма(СуммаДоговора)Благодаря архитектуре компоновки данных, СКД сначала получит уникальные данные из первого набора, посчитает по ним итоги, а затем присоединит подчиненные детальные записи из второго набора. В результате сумма по контрагенту и общий итог будут рассчитаны абсолютно верно, а на уровне документов поле останется пустым.
---
Этот способ является альтернативным вариантом реализации первого метода, но позволяет явно разделить исходные реквизиты объектов и рассчитываемые показатели отчета.
Выполним следующие действия:
СуммаДоговораВычисляемаяДоговор: выражение Максимум(СуммаДоговора).Контрагент: выражение Сумма(ВычислитьВыражениеСГруппировкойМассив("Максимум(СуммаДоговора)", "Договор")).Сумма(ВычислитьВыражениеСГруппировкойМассив("Максимум(СуммаДоговора)", "Договор")).Этот способ гарантирует, что на уровне детальных записей (документов движения) колонка «Сумма договора» будет абсолютно пустой, так как само вычисляемое поле не содержит базовой формулы расчета для детальных строк, а ресурс настроен только для вышестоящих группировок.
Мы рассмотрели три мощных инструмента для работы с итогами в СКД. Выбор конкретного метода зависит от архитектуры вашей базы данных и объема информации:
ВычислитьВыражениеСГруппировкойМассив.