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