Связи и объединение наборов данных в СКД 1С без задвоения сумм

Как устроена закладка «Связи наборов данных»

Когда данные отчёта поступают из нескольких запросов или внешних таблиц значений, их соединение описывается на закладке «Связи наборов данных» конструктора схемы компоновки данных. Каждая строка этой таблицы представляет объект СвязьНаборовДанныхСхемыКомпоновкиДанных и связывает два набора по принципу ЛЕВОЕ СОЕДИНЕНИЕ (Left Outer Join): в результат попадают все записи из набора-источника и подходящие по ключу записи из набора-приёмника.

В отличие от соединения таблиц внутри запроса, которое выполняется на стороне СУБД, связь наборов данных в СКД 1С 8.3 обрабатывает сам ПроцессорКомпоновкиДанных в оперативной памяти сервера «1С:Предприятие». Если между двумя несвязанными наборами вывести поля в одну группировку и не задать связь на закладке «Связи наборов данных», СКД построит декартово произведение (перекрёстное соединение) всех строк.

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

// Программное добавление связи по двум полям (Контрагент И Договор)
СвязьКонтрагент = Схема.СвязиНаборовДанных.Добавить();
СвязьКонтрагент.НаборДанныхИсточник = "НаборПродажи";
СвязьКонтрагент.НаборДанныхПриемник = "НаборОплаты";
СвязьКонтрагент.ВыражениеИсточник   = "Контрагент";
СвязьКонтрагент.ВыражениеПриемник   = "Контрагент";
СвязьКонтрагент.Обязательная        = Истина;

СвязьДоговор = Схема.СвязиНаборовДанных.Добавить();
СвязьДоговор.НаборДанныхИсточник = "НаборПродажи";
СвязьДоговор.НаборДанныхПриемник = "НаборОплаты";
СвязьДоговор.ВыражениеИсточник   = "Договор";
СвязьДоговор.ВыражениеПриемник   = "Договор";
СвязьДоговор.Обязательная        = Истина;

Обратите внимание на флаг «Обязательная» (свойство Обязательная). По умолчанию он установлен в Истина: связь добавляется в макет компоновки всегда, когда в настройках отчёта задействовано хотя бы одно поле из набора-приёмника. Если снять этот флаг (Ложь), связь сработает только тогда, когда в отчёте явно используются поля из ВыражениеИсточник.

Параметры связи, условие связи и иерархия

Помимо базовых полей соединения, у связи наборов данных есть дополнительные настройки, которые управляют фильтрацией запроса приёмника, условным присоединением строк и обходом иерархии:

• Параметр связи (в конструкторе — «Параметр связи», в объектной модели — свойство Параметр) и флаг «Список параметров» (в конструкторе — «Параметры связи», в коде — РазрешенСписокПараметров). Без параметра связи СКД сначала выполняет запрос второго набора целиком по всей базе и лишь затем отсекает лишние строки в памяти. Если указать имя параметра запроса приёмника в свойстве Параметр, процессор компоновки соберёт значения ВыражениеИсточник из первого набора и передаст их в запрос второго набора.
• При снятом флаге РазрешенСписокПараметров запрос приёмника выполняется в цикле отдельно для каждой строки источника (это необходимо, например, когда в параметр виртуальной таблицы СрезПоследних(&ПериодСреза, ...) нужно передавать одиночную дату из каждой строки левого набора). Если же в запросе приёмника используется условие ГДЕ Номенклатура В (&СписокНоменклатуры) и включён флаг РазрешенСписокПараметров = Истина, СКД передаёт массив ключей порциями и выполняет запрос приёмника пакетно, ускоряя выборку в десятки раз.
• Условие связи (свойство УсловиеСвязи) — выражение на языке выражений СКД, которое проверяется перед соединением строк. В отличие от выражений источника и приёмника, которые всегда сравниваются на равенство (=), в УсловиеСвязи можно задать неравенство или интервал (например, ДатаОплаты >= ДатаОтгрузки) либо проверить параметр отчёта. Если условие связи пустое, связь выполняется безусловно.
• Начальное выражение источника (в конструкторе — «Начальное выражение источника», в коде — НачальноеВыражение) — используется, когда набор данных связывается сам с собой для построения иерархии (например, связь поля Ссылка с полем Родитель). В этом свойстве указывают условие отбора корневых записей верхнего уровня, например Родитель = ЗНАЧЕНИЕ(Справочник.Номенклатура.ПустаяСсылка).

// Текст запроса НаборДанныхПриемник (НаборЦены):
// ВЫБРАТЬ Номенклатура, Цена ИЗ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&Период, Номенклатура В (&СписокНоменклатуры))

Связь = Схема.СвязиНаборовДанных.Добавить();
Связь.НаборДанныхИсточник      = "НаборТовары";
Связь.НаборДанныхПриемник      = "НаборЦены";
Связь.ВыражениеИсточник        = "Номенклатура";
Связь.ВыражениеПриемник        = "Номенклатура";
Связь.Параметр                 = "СписокНоменклатуры";
Связь.РазрешенСписокПараметров = Истина;
Связь.УсловиеСвязи             = "Активен = Истина";

Почему при связи «один ко многим» задваиваются ресурсы

Классическая ошибка аналитика и разработчика СКД возникает при попытке соединить два набора данных с разной детальностью или двумя независимыми табличными частями. Допустим, НаборЗаказы возвращает сумму заказа клиента (СуммаЗаказа = 100 000), а НаборОплаты возвращает три платёжных поручения по этому заказу на 30 000, 30 000 и 40 000. Когда мы связываем НаборЗаказы и НаборОплаты по полю Заказ, процессор компоновки формирует плоскую таблицу из трёх детальных строк:

• Строка 1: Заказ №1 | СуммаЗаказа = 100 000 | Оплата №1 | СуммаОплаты = 30 000
• Строка 2: Заказ №1 | СуммаЗаказа = 100 000 | Оплата №2 | СуммаОплаты = 30 000
• Строка 3: Заказ №1 | СуммаЗаказа = 100 000 | Оплата №3 | СуммаОплаты = 40 000

В результате ресурс Сумма(СуммаОплаты) посчитается верно (100 000), а ресурс родительского набора Сумма(СуммаЗаказа) сложится трижды и выдаст 300 000 вместо 100 000. Ещё хуже ситуация при связи «многие ко многим» (например, соединили «Реализации по клиенту» из 4 документов и «Оплаты по клиенту» из 3 документов только по полю Контрагент): в плоской выборке получится 4 × 3 = 12 строк, сумма реализаций утроится, а сумма оплат учетверится.

Три способа соединить данные без задвоения итогов

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

Разберём подробно настройку каждого из этих механизмов.

Способ 1. Набор данных — объединение вместо связи

Если в отчёте нужно сопоставить обороты или остатки из двух независимых источников (например, «План продаж» и «Факт продаж», либо «Отгрузки из запроса» и «Оплаты из внешней ТаблицыЗначений»), вместо создания двух наборов верхнего уровня и связи между ними создайте Набор данных — объединение (тип НаборДанныхОбъединениеСхемыКомпоновкиДанных).

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

В первом подчинённом наборе мы выбираем Контрагент, Договор и СуммаПродажи, а во втором — Контрагент, Договор и СуммаОплаты. Когда пользователь группирует отчёт по контрагенту и договору, СКД схлопывает строки обоих наборов в одну строку группировки и считает Сумма(СуммаПродажи) и Сумма(СуммаОплаты) независимо друг от друга без единого дубля.

// Создаём корневой набор данных — объединение
НаборОбъединение = Схема.НаборыДанных.Добавить(
    Тип("НаборДанныхОбъединениеСхемыКомпоновкиДанных")
);
НаборОбъединение.Имя = "ПродажиИОплаты";

// 1-й подчинённый набор внутри объединения (Продажи)
НаборПродажи = НаборОбъединение.Элементы.Добавить(
    Тип("НаборДанныхЗапросСхемыКомпоновкиДанных")
);
НаборПродажи.Имя = "Продажи";
НаборПродажи.ИсточникДанных = "ИсточникДанных1";
НаборПродажи.АвтоЗаполнениеДоступныхПолей = Истина;
НаборПродажи.Запрос =
    "ВЫБРАТЬ
    |   ПродажиОбороты.Контрагент КАК Контрагент,
    |   ПродажиОбороты.Договор КАК Договор,
    |   ПродажиОбороты.СуммаОборот КАК СуммаПродажи
    |ИЗ
    |   РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода, , ) КАК ПродажиОбороты";

// 2-й подчинённый набор внутри объединения (Оплаты)
НаборОплаты = НаборОбъединение.Элементы.Добавить(
    Тип("НаборДанныхЗапросСхемыКомпоновкиДанных")
);
НаборОплаты.Имя = "Оплаты";
НаборОплаты.ИсточникДанных = "ИсточникДанных1";
НаборОплаты.АвтоЗаполнениеДоступныхПолей = Истина;
НаборОплаты.Запрос =
    "ВЫБРАТЬ
    |   РасчетыОбороты.Контрагент КАК Контрагент,
    |   РасчетыОбороты.Договор КАК Договор,
    |   РасчетыОбороты.СуммаВзаиморасчетовРасход КАК СуммаОплаты
    |ИЗ
    |   РегистрНакопления.ВзаиморасчетыСКонтрагентами.Обороты(&НачалоПериода, &КонецПериода, , ) КАК РасчетыОбороты";

Способы 2 и 3. Настройка «Рассчитывать по» и пакетный запрос с ВТ

Что делать, если связь между наборами всё-таки нужна — например, в верхней группировке отчёта выводится ЗаказКлиента и его СуммаЗаказа из первого набора, а во вложенной группировке разворачиваются строки ДокументОплаты и СуммаОплаты из второго набора? В этой структуре показатель СуммаЗаказа относится только к уровню ЗаказКлиента и не имеет смысла на уровне отдельных платёжек.

В конструкторе СКД на закладке «Ресурсы» для поля СуммаЗаказа можно:

• Задать выражение Максимум(СуммаЗаказа) для группировки ЗаказКлиента (так как во всех размноженных строках одного заказа число одинаковое), а для вышестоящих группировок (например, Контрагент и ОбщийИтог) использовать конструкцию Сумма(ВычислитьВыражениеСГруппировкойМассив("Максимум(СуммаЗаказа)", "ЗаказКлиента")).
• Заполнить колонку «Рассчитывать по…» (в объектной модели — массив ПолеИтогаСхемыКомпоновкиДанных.Группировки), указав только те поля группировок, на уровне которых ресурс должен вычисляться (например, "ЗаказКлиента" и "ОбщийИтог"). Тогда в детальных строках оплат ресурс заказа выводиться не будет.

// Настройка ресурса на уровне группировки ЗаказКлиента и в ОбщемИтоге без задвоения
РесурсПоЗаказу = Схема.ПоляИтога.Добавить();
РесурсПоЗаказу.ПутьКДанным = "СуммаЗаказа";
РесурсПоЗаказу.Выражение   = "Максимум(СуммаЗаказа)";
РесурсПоЗаказу.Группировки.Добавить("ЗаказКлиента");

РесурсОбщий = Схема.ПоляИтога.Добавить();
РесурсОбщий.ПутьКДанным = "СуммаЗаказа";
РесурсОбщий.Выражение   = "Сумма(ВычислитьВыражениеСГруппировкойМассив(""Максимум(СуммаЗаказа)"", ""ЗаказКлиента""))";
РесурсОбщий.Группировки.Добавить("Контрагент");
РесурсОбщий.Группировки.Добавить("ОбщийИтог");

Если же оба набора данных выбираются из таблиц информационной базы и в отчёте не требуется передавать параметры связи в цикле, самый быстрый и надёжный вариант — заменить два набора данных одним НаборДанныхЗапросСхемыКомпоновкиДанных с пакетным запросом. Поместите выборку каждого регистра во временную таблицу (ПОМЕСТИТЬ ВТ_Продажи и ПОМЕСТИТЬ ВТ_Оплаты) и соедините их в итоговом пакете через ОБЪЕДИНИТЬ ВСЕ с полями-заглушками 0 КАК СуммаОплаты и 0 КАК СуммаПродажи. СУБД выполнит объединение за один проход без накладных расходов процессора компоновки.

Частые ошибки при связях наборов данных в СКД

• Связь двух регистров только по верхнему измерению («многие ко многим»). Если в первом наборе строки сгруппированы по Контрагент + ДокументРеализации, во втором — по Контрагент + ДокументОплаты, а связь задана только по полю Контрагент, записи перемножатся между собой. Для разнородных документов используйте НаборДанныхОбъединениеСхемыКомпоновкиДанных.
• Совпадающие имена числовых полей в разных наборах. Если и в источнике, и в приёмнике поле суммы названо одинаково (Сумма), СКД воспримет его как одно и то же поле схемы и перезапишет либо некорректно сложит значения. Всегда давайте числовым ресурсам разных наборов уникальные псевдонимы (СуммаЗаказа, СуммаОплаты), а общие имена оставляйте только для измерений в НаборДанныхОбъединение.
• Указан «Параметр связи», но забыт флаг «Параметры связи» (Список параметров) при операторе В (&Параметр). Если в запросе приёмника написано ГДЕ Ссылка В (&СписокСсылок), а свойство РазрешенСписокПараметров оставлено в Ложь, СКД будет выполнять запрос приёмника по одному элементу столько раз, сколько строк вернул источник.
• Отбор по полю приёмника превращает левое соединение во внутреннее. Когда пользователь накладывает в настройках отчёта обычный отбор на поле набора-приёмника (например, СуммаОплаты > 0), строки источника, у которых в приёмнике оказалось NULL, пропадают из отчёта. Если нужно фильтровать только присоединяемые строки, сохраняя все записи источника, используйте УсловиеСвязи или параметры запроса приёмника.
• Ошибки при программном обращении к свойствам связи. В Синтаксис-помощнике имена свойств объекта СвязьНаборовДанныхСхемыКомпоновкиДанных отличаются от подписей колонок конструктора: вместо «Источник связи» в коде пишется НаборДанныхИсточник, вместо «Параметр связи» — Параметр, а вместо «Параметры связи» — РазрешенСписокПараметров.
• Не снят флаг «Обязательная» у вспомогательного набора. При установленном флаге Обязательная = Истина запрос приёмника будет выполняться всегда, когда его поля присутствуют в макете, даже если ключевое поле источника не выведено в структуру текущего варианта отчёта.

Частые вопросы по связям и объединению наборов в СКД