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