Как задать условие на период виртуальной таблицы в построителе отчета при полном соединении?

При разработке отчетов с использованием объекта ПостроительОтчета (или ПостроительЗапроса) нередко возникает ситуация, когда в одном запросе соединяются несколько таблиц, требующих независимых условий на период. Если попытаться описать фильтр по дате во внешней секции построителя {ГДЕ ...}, можно столкнуться с неожиданным поведением запроса.

Разберем подробнее, почему возникает эта проблема, и проанализируем работающие способы ее решения.

В чем заключается проблема отсечения данных

Рассмотрим типовой сценарий: выполняется ПОЛНОЕ СОЕДИНЕНИЕ (или ЛЕВОЕ СОЕДИНЕНИЕ) основной таблицы с виртуальной таблицей оборотов, а отбор по дате задан в секции расширения языка запросов:

ВЫБРАТЬ
    РасчетыПоАукционам.Аукцион.Контрагент КАК Контрагент,
    РасчетыПоАукционам.Аукцион.Ссылка КАК Аукцион,
    ПродажиОбороты.ДокументПродажи КАК ДокументПродажи,
    РасчетыПоАукционам.СуммаВзаиморасчетовПриход КАК СуммаОплаты
ИЗ
    РасчетыПоАукционам КАК РасчетыПоАукционам
    ПОЛНОЕ СОЕДИНЕНИЕ РегистрНакопления.ПродажиЗаявки.Обороты(, , Регистратор, ЗаказЗаявка <> НЕОПРЕДЕЛЕНО) КАК ПродажиОбороты
    ПО РасчетыПоАукционам.Сделка = ПродажиОбороты.ЗаказЗаявка
{ГДЕ
    ПродажиОбороты.Период КАК ДатаРеализации}

Проанализируем, как сработает такой запрос. Секция {ГДЕ} построителя отчета транслируется в общее предложение ГДЕ итогового SQL-запроса. Это условие накладывается уже после выполнения соединения таблиц. Если по сделке не было продаж в выбранном интервале либо для строки первой таблицы поле ПродажиОбороты.Период содержит значение NULL, условие ГДЕ исключает всю результирующую строку. В итоге ПОЛНОЕ СОЕДИНЕНИЕ фактически превращается во внутреннее, а данные из левой таблицы теряются.

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

Способ 1. Использование вложенного запроса с локальной секцией отбора

Наиболее простое и универсальное решение — изолировать виртуальную таблицу во вложенный запрос и объявить секцию {ГДЕ} непосредственно внутри него. В таком случае построитель отчета встроит пользовательский отбор до выполнения соединения.

Посмотрим на пример структуры запроса:

ВЫБРАТЬ
    РасчетыПоАукционам.Аукцион.Контрагент КАК Контрагент,
    РасчетыПоАукционам.Аукцион.Ссылка КАК Аукцион,
    ВложенныеПродажи.ДокументПродажи КАК ДокументПродажи,
    РасчетыПоАукционам.СуммаВзаиморасчетовПриход КАК СуммаОплаты
ИЗ
    РасчетыПоАукционам КАК РасчетыПоАукционам
    ПОЛНОЕ СОЕДИНЕНИЕ (
        ВЫБРАТЬ
            ПродажиОбороты.ЗаказЗаявка КАК ЗаказЗаявка,
            ПродажиОбороты.ДокументПродажи КАК ДокументПродажи,
            ПродажиОбороты.Период КАК Период
        ИЗ
            РегистрНакопления.ПродажиЗаявки.Обороты(, , Регистратор, ЗаказЗаявка <> НЕОПРЕДЕЛЕНО) КАК ПродажиОбороты
        {ГДЕ
            ПродажиОбороты.Период КАК ДатаРеализации}
    ) КАК ВложенныеПродажи
    ПО РасчетыПоАукционам.Сделка = ВложенныеПродажи.ЗаказЗаявка
{ГДЕ
    РасчетыПоАукционам.Аукцион.ГарантийныйБанк.* КАК ГарантийныйБанк}

Выясним преимущества такого подхода:

1. Сохранение целостности соединения: фильтрация данных таблицы продаж происходит на этапе формирования подзапроса. Строки из таблицы РасчетыПоАукционам останутся в выборке независимо от наличия движений по продажам.
2. Гибкость видов сравнения: пользователь в пользовательском интерфейсе отбора может выбирать любые стандартные операторы: «Интервал», «Больше», «Меньше или равно» и т. д. Построитель автоматически подставит их во вложенное условие ГДЕ.

Способ 2. Необязательные параметры виртуальной таблицы

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

Разберем по шагам, как это реализовать:

1. В тексте запроса первые два параметра виртуальной таблицы оборотов помещаются в фигурные скобки:

   Скопировать код

   
   РегистрНакопления.ПродажиЗаявки.Обороты({(&ДатаРеализацииНач)}, {(&ДатаРеализацииКон)}, Регистратор, ЗаказЗаявка <> НЕОПРЕДЕЛЕНО) КАК ПродажиОбороты
   

   Конструкция {(&ИмяПараметра)} указывает построителю, что если данный параметр не был инициализирован в коллекции Построитель.Параметры, то он будет просто пропущен при формировании итогового запроса к СУБД.

2. Установка значений параметров выполняется программно перед вызовом метода Выполнить():

   Скопировать код

   
   Построитель.Параметры.Вставить("ДатаРеализацииНач", ДатаНачала);
   Построитель.Параметры.Вставить("ДатаРеализацииКон", Новый Граница(КонецДня(ДатаОкончания), ВидГраницы.Включая));
   

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

ЭлементОтбора = Построитель.Отбор.Найти("ДатаРеализации");
Если ЭлементОтбора <> Неопределено И ЭлементОтбора.Использование Тогда
    Если ЭлементОтбора.ВидСравнения = ВидСравнения.Интервал 
       Или ЭлементОтбора.ВидСравнения = ВидСравнения.ИнтервалВключаяГраницы Тогда
        Построитель.Параметры.Вставить("ДатаРеализацииНач", ЭлементОтбора.ЗначениеС);
        Построитель.Параметры.Вставить("ДатаРеализацииКон", Новый Граница(КонецДня(ЭлементОтбора.ЗначениеПо), ВидГраницы.Включая));
    ИначеЕсли ЭлементОтбора.ВидСравнения = ВидСравнения.БольшеИлиРавно Тогда
        Построитель.Параметры.Вставить("ДатаРеализацииНач", ЭлементОтбора.Значение);
    КонецЕсли;
КонецЕсли;

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