Как сделать параметры виртуальных таблиц в СКД необязательными и избежать ошибки «Не задано значение параметра»?

← На главную

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

Почему возникает ошибка и почему конструкция с ВЫБОР не работает?

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

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

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

Наиболее лаконичное и производительное решение — использование синтаксиса расширения языка запросов СКД с помощью фигурных скобок {}. В конструкторе запроса на вкладке «Компоновка данных» (или вручную в тексте запроса) параметры условий виртуальной таблицы помещаются в фигурные скобки через запятую.

Посмотрим на пример текста запроса для получения остатков по регистру:


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

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

Условия внутри фигурных скобок {Организация = &Организация, Проект = &Проект} перечисляются через запятую (что эквивалентно логическому оператору И).
Если пользователь на форме отчета заполнил только параметр Организация, платформа автоматически сформирует условие только по организации, а фильтр по проекту будет полностью исключен из итогового SQL-запроса.
Если оба параметра не заполнены, виртуальная таблица выберет данные по всем организациям и проектам без ошибок об отсутствии значений параметров.
Благодаря левому соединению в отчет попадут абсолютно все проекты из справочника, даже если по ним не было движений и остатков.

Способ 2. Условия в секции связи таблиц через расширение СКД

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

Рассмотрим пример реализации:


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

В этом случае, если значение &Организация не задано, блок {И (Остатки.Организация = &Организация)} удаляется из запроса, не нарушая работу левого соединения.

Способ 3. Обработка параметров на вкладке «Параметры» схемы СКД

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

В списке параметров найдите нужный параметр (например, Организация).
Снимите флаг «Запрещать незаполненные значения».

В колонке «Выражение» напишите проверку на случай, если значение не указано:


ВЫБОР
    КОГДА &Организация = НЕОПРЕДЕЛЕНО ИЛИ &Организация ЕСТЬ NULL
        ТОГДА ЗНАЧЕНИЕ(Справочник.Организации.ПустаяСсылка)
    ИНАЧЕ &Организация
КОНЕЦ

В самом запросе условие может выглядеть следующим образом:


ГДЕ
    (&Организация = ЗНАЧЕНИЕ(Справочник.Организации.ПустаяСсылка)
        ИЛИ Таблица.Организация = &Организация)

Важно: Помните, что третий способ менее предпочтителен для высоконагруженных отчетов, так как конструкция ИЛИ в секции ГДЕ или в параметрах виртуальной таблицы не позволяет СУБД полноценно использовать индексы таблиц и может приводить к замедлению формирования отчета.