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

← На главную

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

Причины появления значений NULL при соединении

Проанализируем базовый запрос, в котором к виртуальной таблице ОстаткиИОбороты присоединяется табличная часть документа:


ВЫБРАТЬ
    ХозрасчетныйОстаткиИОбороты.Счет КАК Счет,
    ХозрасчетныйОстаткиИОбороты.Регистратор КАК Регистратор,
    ХозрасчетныйОстаткиИОбороты.СуммаОборотДт КАК СуммаОборотДт,
    АвансовыйОтчетТовары.Номенклатура КАК Номенклатура
ИЗ
    РегистрБухгалтерии.Хозрасчетный.ОстаткиИОбороты(, , Авто, , Счет В ИЕРАРХИИ(&Счет), , ) КАК ХозрасчетныйОстаткиИОбороты
        ЛЕВОЕ СОЕДИНЕНИЕ Документ.АвансовыйОтчет.Товары КАК АвансовыйОтчетТовары
        ПО ХозрасчетныйОстаткиИОбороты.Регистратор = АвансовыйОтчетТовары.Ссылка

Если в результате выполнения запроса поля регистра заполнены, а реквизит Номенклатура возвращает значение NULL, причина заключается в особенностях работы левого соединения (ЛЕВОЕ СОЕДИНЕНИЕ):

Регистратор другого типа. Движения по указанному счету сформированы не документом АвансовыйОтчет, а другими документами (например, ПоступлениеТоваровУслуг, ТребованиеНакладная или ОперацияБух). Левое соединение сохраняет строку регистра, но для несовпадающих типов подставляет NULL.
Пустая табличная часть. Документ-регистратор действительно является авансовым отчетом, но его табличная часть Товары не заполнена (например, расходы отражены на закладке Прочее или Оплата).
Фильтрация в секции ГДЕ вместо параметров виртуальной таблицы. Передача отбора по счету в секцию ГДЕ вместо параметров виртуальной таблицы ОстаткиИОбороты не влияет на NULL напрямую, но сильно замедляет расчет итогов СУБД. Отборы по счетам и субконто всегда следует передавать в параметры виртуальной таблицы.

Главная опасность: задвоение оборотов и остатков

Важно понимать критическое ограничение реляционной модели при связывании регистра с табличной частью: прямое соединение по ссылке дублирует числовые данные. Если в документе-регистраторе табличная часть содержит 10 строк, то при левом соединении одна запись регистра размножится на 10 строк. В результате начальные остатки, обороты и конечные остатки увеличатся ровно в 10 раз при подсчете итогов в отчете.

Способ 1. Получение номенклатуры из нескольких видов документов через конструкцию ВЫБОР

Если требуется получить номенклатуру по документам разных типов и при этом гарантируется, что в строках нет критического задвоения итогов (или отчет выводится в виде простого детального списка), свяжем регистр с табличными частями всех целевых документов через левые соединения и конструкцию ВЫБОР КОГДА ... ССЫЛКА:


ВЫБРАТЬ
    ХозрасчетныйОстаткиИОбороты.Счет КАК Счет,
    ХозрасчетныйОстаткиИОбороты.Подразделение КАК Подразделение,
    ХозрасчетныйОстаткиИОбороты.Субконто1 КАК Субконто1,
    ХозрасчетныйОстаткиИОбороты.Субконто2 КАК Субконто2,
    ХозрасчетныйОстаткиИОбороты.ПериодСекунда КАК ПериодСекунда,
    ХозрасчетныйОстаткиИОбороты.Регистратор КАК Регистратор,
    ХозрасчетныйОстаткиИОбороты.СуммаНачальныйОстатокДт КАК СуммаНачальныйОстатокДт,
    ХозрасчетныйОстаткиИОбороты.СуммаОборотДт КАК СуммаОборотДт,
    ХозрасчетныйОстаткиИОбороты.СуммаОборотКт КАК СуммаОборотКт,
    ХозрасчетныйОстаткиИОбороты.СуммаКонечныйОстатокДт КАК СуммаКонечныйОстатокДт,
    ВЫБОР
        КОГДА ХозрасчетныйОстаткиИОбороты.Регистратор ССЫЛКА Документ.АвансовыйОтчет
            ТОГДА АвансовыйОтчетТовары.Номенклатура
        КОГДА ХозрасчетныйОстаткиИОбороты.Регистратор ССЫЛКА Документ.ПоступлениеТоваровУслуг
            ТОГДА ЕСТЬNULL(ПоступлениеТовары.Номенклатура, ПоступлениеУслуги.Номенклатура)
        КОГДА ХозрасчетныйОстаткиИОбороты.Регистратор ССЫЛКА Документ.ТребованиеНакладная
            ТОГДА ТребованиеМатериалы.Номенклатура
    КОНЕЦ КАК Номенклатура
ИЗ
    РегистрБухгалтерии.Хозрасчетный.ОстаткиИОбороты(, , Авто, , Счет В ИЕРАРХИИ(&Счет), , ) КАК ХозрасчетныйОстаткиИОбороты
        ЛЕВОЕ СОЕДИНЕНИЕ Документ.АвансовыйОтчет.Товары КАК АвансовыйОтчетТовары
        ПО ХозрасчетныйОстаткиИОбороты.Регистратор = АвансовыйОтчетТовары.Ссылка

        ЛЕВОЕ СОЕДИНЕНИЕ Документ.ПоступлениеТоваровУслуг.Товары КАК ПоступлениеТовары
        ПО ХозрасчетныйОстаткиИОбороты.Регистратор = ПоступлениеТовары.Ссылка

        ЛЕВОЕ СОЕДИНЕНИЕ Документ.ПоступлениеТоваровУслуг.Услуги КАК ПоступлениеУслуги
        ПО ХозрасчетныйОстаткиИОбороты.Регистратор = ПоступлениеУслуги.Ссылка

        ЛЕВОЕ СОЕДИНЕНИЕ Документ.ТребованиеНакладная.Материалы КАК ТребованиеМатериалы
        ПО ХозрасчетныйОстаткиИОбороты.Регистратор = ТребованиеМатериалы.Ссылка

Способ 2. Предварительное объединение и группировка во временной таблице

Чтобы избежать множественных соединений основной таблицы и сократить объем передаваемых данных, объединим табличные части документов в единую временную таблицу и свернем повторяющиеся позиции с помощью СГРУППИРОВАТЬ ПО:


ВЫБРАТЬ
    ТЧ.Ссылка КАК Ссылка,
    ТЧ.Номенклатура КАК Номенклатура
ПОМЕСТИТЬ ВТ_СтрокиДокументов
ИЗ
    (ВЫБРАТЬ
        АвансовыйОтчетТовары.Ссылка КАК Ссылка,
        АвансовыйОтчетТовары.Номенклатура КАК Номенклатура
    ИЗ
        Документ.АвансовыйОтчет.Товары КАК АвансовыйОтчетТовары
    
    ОБЪЕДИНИТЬ ВСЕ
    
    ВЫБРАТЬ
        ПоступлениеТоваровУслугТовары.Ссылка,
        ПоступлениеТоваровУслугТовары.Номенклатура
    ИЗ
        Документ.ПоступлениеТоваровУслуг.Товары КАК ПоступлениеТоваровУслугТовары
    
    ОБЪЕДИНИТЬ ВСЕ
    
    ВЫБРАТЬ
        ТребованиеНакладнаяМатериалы.Ссылка,
        ТребованиеНакладнаяМатериалы.Номенклатура
    ИЗ
        Документ.ТребованиеНакладная.Материалы КАК ТребованиеНакладнаяМатериалы) КАК ТЧ
СГРУППИРОВАТЬ ПО
    ТЧ.Ссылка,
    ТЧ.Номенклатура
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ХозрасчетныйОстаткиИОбороты.Счет КАК Счет,
    ХозрасчетныйОстаткиИОбороты.Регистратор КАК Регистратор,
    ХозрасчетныйОстаткиИОбороты.СуммаОборотДт КАК СуммаОборотДт,
    ХозрасчетныйОстаткиИОбороты.СуммаОборотКт КАК СуммаОборотКт,
    ВТ_СтрокиДокументов.Номенклатура КАК Номенклатура
ИЗ
    РегистрБухгалтерии.Хозрасчетный.ОстаткиИОбороты(, , Авто, , Счет В ИЕРАРХИИ(&Счет), , ) КАК ХозрасчетныйОстаткиИОбороты
        ЛЕВОЕ СОЕДИНЕНИЕ ВТ_СтрокиДокументов КАК ВТ_СтрокиДокументов
        ПО ХозрасчетныйОстаткиИОбороты.Регистратор = ВТ_СтрокиДокументов.Ссылка

Способ 3. Использование связей наборов данных в СКД (рекомендуемый)

Для построения отчетов в системе компоновки данных (СКД) наилучшим решением является разделение выборки на два независимых набора данных:

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

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

Способ 4. Анализ субконто и корреспонденций регистра

Разберем ситуацию, когда номенклатура уже присутствует в самом регистре. Если отчет строится по счету затрат (например, 20, 26 или 44), на котором нет субконто «Номенклатура», она может быть указана на корреспондирующем счете (счет 10 или 41). В этом случае обращаться к табличным частям документов не требуется — достаточно использовать виртуальную таблицу РегистрБухгалтерии.Хозрасчетный.ДвиженияССубконто и получить аналитику из полей КорСубконто1...КорСубконто3.