Как в СКД сделать необязательный отбор по периоду, чтобы при отключенном параметре выводились все данные?

← На главную

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

Если прописать жесткое условие в секции ГДЕ текста запроса, например ГДЕ Дата МЕЖДУ &НачалоПериода И &КонецПериода, то при отключении флага параметра в пользовательских настройках система либо выдаст ошибку о незаполненном параметре, либо вернет пустой результат. Разберем, почему так происходит и какими способами можно правильно настроить необязательный период.

Способ 1. Использование фигурных скобок СКД (расширение языка запросов)

Основной и наиболее лаконичный инструмент в компоновке данных — это язык расширения СКД. Любое условие, помещенное в фигурные скобки {ГДЕ ...}, считается опциональным. Если пользователь включает параметр или отбор — условие автоматически подставляется в результирующий SQL-запрос, а если флажок снят — условие полностью вырезается из итогового запроса к базе данных.

Рассмотрим базовый вариант оформления, предложенный в обсуждении:


ВЫБРАТЬ
    СчетФактураПрисоединенныеФайлы.ВладелецФайла.Представление КАК ВладелецФайлаПредставление,
    СчетФактураПрисоединенныеФайлы.Наименование КАК Наименование,
    СчетФактураПрисоединенныеФайлы.ВладелецФайла.Ссылка КАК ВладелецФайлаСсылка,
    СчетФактураПрисоединенныеФайлы.ВладелецФайла.Дата КАК ВладелецФайлаДата
ИЗ
    Справочник.СчетФактураПрисоединенныеФайлы КАК СчетФактураПрисоединенныеФайлы
{ГДЕ
    СчетФактураПрисоединенныеФайлы.ВладелецФайла.Дата МЕЖДУ &НачалоПериода И &КонецПериода}
СГРУППИРОВАТЬ ПО
    СчетФактураПрисоединенныеФайлы.Наименование,
    СчетФактураПрисоединенныеФайлы.ВладелецФайла.Ссылка,
    СчетФактураПрисоединенныеФайлы.ВладелецФайла.Дата,
    СчетФактураПрисоединенныеФайлы.ВладелецФайла.Представление
УПОРЯДОЧИТЬ ПО
    ВладелецФайлаСсылка

Однако при использовании конструкции МЕЖДУ внутри фигурных скобок есть нюанс: если пользователь снимет флаг только с одного параметра (например, укажет только начало периода), запрос может завершиться ошибкой или сработать некорректно. Чтобы обеспечить независимое управление границами периода, условия внутри фигурных скобок рекомендуется разделять через запятую:


{ГДЕ
    СчетФактураПрисоединенныеФайлы.ВладелецФайла.Дата >= &НачалоПериода,
    СчетФактураПрисоединенныеФайлы.ВладелецФайла.Дата <= &КонецПериода}

В этом случае компоновщик сам объединит активные условия оператором И, а неактивный параметр просто не будет включен в текст SQL-запроса.

Способ 2. Использование типа «СтандартныйПериод» на вкладке «Параметры»

Если мы хотим предоставить пользователю удобный выбор периода (например, «Этот месяц», «Прошлый квартал» или произвольный интервал), лучше объединить границы в один параметр:

На вкладке Параметры создаем параметр ПериодОтчета с типом СтандартныйПериод.
Для параметров запроса &НачалоПериода и &КонецПериода устанавливаем признак Ограничение доступности (чтобы они не отображались пользователю).
В поле «Выражение» для &НачалоПериода прописываем: &ПериодОтчета.ДатаНачала.
В поле «Выражение» для &КонецПериода задаем выражение с включением конца дня: КонецПериода(&ПериодОтчета.ДатаОкончания, "ДЕНЬ").

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

Способ 3. Универсальное условие в обычном запросе без фигурных скобок

Посмотрим на ситуацию, когда отчет формируется без функционала расширения СКД или в обычном запросе через встроенный язык. Попытка использовать конструкцию с ВЫБОР непосредственно в секции ГДЕ приведет к ошибке «Операция не разрешена в предложении "ГДЕ"», поскольку оператор ВЫБОР возвращает значение, а не логический предикат.

Правильный способ обойти это ограничение в стандартном SQL-запросе — использовать проверку на пустую дату ДАТАВРЕМЯ(1, 1, 1) через логическое ИЛИ:


ГДЕ
    (&НачалоПериода = ДАТАВРЕМЯ(1, 1, 1) ИЛИ СчетФактураПрисоединенныеФайлы.ВладелецФайла.Дата >= &НачалоПериода)
    И (&КонецПериода = ДАТАВРЕМЯ(1, 1, 1) ИЛИ СчетФактураПрисоединенныеФайлы.ВладелецФайла.Дата <= &КонецПериода)

Если параметр не задан (передана пустая дата Дата(1, 1, 1)), условие &НачалоПериода = ДАТАВРЕМЯ(1, 1, 1) становится истинным, и фильтрация по дате игнорируется.

Способ 4. Использование стандартных отборов компоновки данных

В схеме компоновки данных можно полностью отказаться от жестких условий в секции ГДЕ. Для этого поле даты просто выбирается в запросе, а фильтрация настраивается на вкладке Настройки в панели «Отбор»:

Удаляем секцию ГДЕ из запроса.
Переходим на вкладку Настройки → Отбор.
Добавляем отбор по полю ВладелецФайла.Дата с видом сравнения В интервале (для дат).
В свойствах элемента отбора включаем его в состав Пользовательских настроек.

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

Оптимизация: разыменование составного типа

Обратите внимание на поле ВладелецФайла.Дата. В типовых конфигурациях у справочников присоединенных файлов реквизит ВладелецФайла имеет составной тип. Обращение через точку к реквизиту составного типа на уровне СУБД приводит к неявному левому соединению со всеми возможными таблицами владельцев.

Для ускорения работы запроса рекомендуется явно приводить тип через функцию ВЫРАЗИТЬ:


ВЫБРАТЬ
    ВЫРАЗИТЬ(СчетФактураПрисоединенныеФайлы.ВладелецФайла КАК Документ.СчетФактураВыданный).Дата КАК ДатаВладельца
ИЗ
    Справочник.СчетФактураПрисоединенныеФайлы КАК СчетФактураПрисоединенныеФайлы
{ГДЕ
    (ВЫРАЗИТЬ(СчетФактураПрисоединенныеФайлы.ВладелецФайла КАК Документ.СчетФактураВыданный)).Дата >= &НачалоПериода,
    (ВЫРАЗИТЬ(СчетФактураПрисоединенныеФайлы.ВладелецФайла КАК Документ.СчетФактураВыданный)).Дата <= &КонецПериода}

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