При разработке отчетов в системе компоновки данных (СКД) разработчики часто сталкиваются с ситуацией, когда необходимо вывести в одном отчете шапку документа и данные сразу из нескольких его табличных частей — например, Товары и Оплаты. Если попытаться получить эти данные одним простым запросом, строки отчета начинают двоиться и множиться. Выясним причину такого поведения платформы и подробно разберем правильные способы решения этой задачи.
Проанализируем механику работы СУБД. Когда в одном запросе мы обращаемся к двум несвязанным между собой табличным частям одного документа через вложенные таблицы или через левое соединение без уникального ключа строки, происходит декартово произведение строк. Например, если в табличной части Товары содержится 3 строки, а в табличной части Оплаты — 2 строки, результирующая плоская таблица запроса будет содержать 3 * 2 = 6 строк. Каждая строка первой табличной части повторится для каждой строки второй табличной части.
В консоли запросов при получении результата с вложенными таблицами типа ВыборкаИзРезультатаЗапроса визуально может казаться, что строки не дублируются, так как консоль обрабатывает вложенные таблицы как отдельные подчиненные выборки. Однако СКД по умолчанию разворачивает выборку в плоский набор данных, что приводит к визуальному дублированию информации.
Кроме того, конструкция вида Документ.ТабличнаяЧасть.(Поле1, Поле2) в запросах 1С является устаревшим механизмом и не рекомендуется к использованию в схемах компоновки данных. Рассмотрим корректные варианты настройки СКД для устранения дублей.
Наиболее чистый и архитектурно правильный способ в СКД — разделить выборку шапки и табличных частей на отдельные наборы данных-запросы с последующей связью по ссылке на документ.
Разберем по шагам, как настроить такую схему:
ВЫБРАТЬ
Тест.Ссылка КАК Документ,
Тест.Имя КАК Имя,
Тест.Фамилия КАК Фамилия
ИЗ
Документ.Тест КАК Тест
ВЫБРАТЬ
Товары.Ссылка КАК Документ,
Товары.НомерСтроки КАК НомерСтроки,
Товары.Номенклатура КАК Номенклатура,
Товары.Стоимость КАК Стоимость
ИЗ
Документ.Тест.Товары КАК Товары
ВЫБРАТЬ
Оплаты.Ссылка КАК Документ,
Оплаты.НомерСтроки КАК НомерСтроки,
Оплаты.Дата КАК Дата,
Оплаты.Цена КАК Цена
ИЗ
Документ.Тест.Оплаты КАК Оплаты
Переходим на вкладку «Связи наборов данных» и добавляем две строки:
Источник: НаборДанных1, Приемник: НаборДанных2, Условие связи: Документ = Документ.
Источник: НаборДанных1, Приемник: НаборДанных3, Условие связи: Документ = Документ.
На вкладке «Настройки» добавляем группировку по полю Документ (или Имя, Фамилия). Внутрь нее добавляем две параллельные подчиненные группировки (таблицы или списки детальных записей) — одну для полей набора Товары, другую для полей набора Оплаты. При таком подходе строки выводятся раздельно и декартово перемножение полностью исключено.
Если требуется вывести данные табличных частей компактно в строках документа без создания сложной структуры группировок, можно воспользоваться встроенными функциями агрегации СКД: СоединитьСтроки, ТаблицаЗначений или ВычислитьВыражениеСГруппировкойТаблицаЗначений.
Посмотрим на пример настройки ресурсов:
СоединитьСтроки(ТаблицаЗначений(Товары.Номенклатура, ", Стоимость - ", Товары.Стоимость), Символы.ПС)
СоединитьСтроки(ТаблицаЗначений(Оплаты.Дата, ", Оплачено - ", Оплаты.Цена), Символы.ПС)
Если стоит задача вывести строки товаров и оплат параллельно «строка в строку» (строка №1 товаров напротив строки №1 оплат), используем один запрос с ПОЛНЫМ СОЕДИНЕНИЕМ по ссылке и номеру строки.
Рассмотрим текст запроса для такого случая:
ВЫБРАТЬ
ЕСТЬNULL(Товары.Ссылка, Оплаты.Ссылка) КАК Документ,
ЕСТЬNULL(Товары.Ссылка.Имя, Оплаты.Ссылка.Имя) КАК Имя,
ЕСТЬNULL(Товары.Ссылка.Фамилия, Оплаты.Ссылка.Фамилия) КАК Фамилия,
Товары.Номенклатура КАК Номенклатура,
Товары.Стоимость КАК Стоимость,
Оплаты.Дата КАК ДатаОплаты,
Оплаты.Цена КАК ЦенаОплаты
ИЗ
Документ.Тест.Товары КАК Товары
ПОЛНОЕ СОЕДИНЕНИЕ Документ.Тест.Оплаты КАК Оплаты
ПО Товары.Ссылка = Оплаты.Ссылка
И Товары.НомерСтроки = Оплаты.НомерСтроки
При таком подходе итоговое количество строк по документу будет равно не произведению, а максимуму из количества строк в двух табличных частях. Если строк оплат меньше, соответствующие колонки оплат просто останутся незаполненными (со значением NULL), не дублируя данные товаров.
Еще один надежный способ избежать дублирования при одном наборе данных — использовать конструкцию ОБЪЕДИНИТЬ ВСЕ с добавлением поля-признака типа строки.
Разберем пример запроса:
ВЫБРАТЬ
Товары.Ссылка КАК Документ,
Товары.Ссылка.Имя КАК Имя,
Товары.Ссылка.Фамилия КАК Фамилия,
"Товары" КАК ТипСтроки,
Товары.НомерСтроки КАК НомерСтроки,
Товары.Номенклатура КАК Номенклатура,
Товары.Стоимость КАК Стоимость,
ДАТАВРЕМЯ(1, 1, 1) КАК ДатаОплаты,
0 КАК ЦенаОплаты
ИЗ
Документ.Тест.Товары КАК Товары
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
Оплаты.Ссылка,
Оплаты.Ссылка.Имя,
Оплаты.Ссылка.Фамилия,
"Оплаты",
Оплаты.НомерСтроки,
ЗНАЧЕНИЕ(Справочник.Номенклатура.ПустаяСсылка),
0,
Оплаты.Дата,
Оплаты.Цена
ИЗ
Документ.Тест.Оплаты КАК Оплаты
В настройках структуры отчета СКД достаточно сделать группировку верхнего уровня по полю Документ, а внутри добавить подчиненную группировку по полю ТипСтроки с детальными записями. В итоге отчет будет выводить сначала список всех товаров документа, а затем список всех оплат без взаимного наложения и дублирования.