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

← На главную

При разработке аналитических отчетов в схеме компоновки данных (СКД) часто возникает задача сравнения двух показателей строки — например, цены из документа реализации и плановой цены из прайс-листа на дату операции. Если цена не установлена или совпадает с ценой документа, такие строки требуется исключить из результата. Разберем по шагам, как настроить сравнение двух полей в отборе СКД, корректно обработать пустые значения и скрыть служебные условия от конечного пользователя.

Постановка задачи и анализ структуры данных

Рассмотрим типовую ситуацию. В схеме компоновки данных используются два набора данных:

Запрос 1 (Документы): выбирает строки табличной части документа РеализацияТоваровУслуг.Товары с полями Номенклатура, Цена (псевдоним ЦенаДОК), ДатаДок, ТипЦенДок.
Запрос 2 (Прайс-лист): обращается к виртуальной таблице РегистрСведений.ЦеныНоменклатуры.СрезПоследних с параметрами &Дата, &Номенклатура и &ТипЦен.

Посмотрим на текст исходных запросов:


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

Второй запрос выбирает срез цен по параметрам, передаваемым через вкладку «Связи наборов данных»:


ВЫБРАТЬ
    &Дата КАК Дата,
    ЦеныНоменклатурыСрезПоследних.Номенклатура КАК Номенклатура,
    ЦеныНоменклатурыСрезПоследних.Цена КАК ЦенаПрайса,
    ЦеныНоменклатурыСрезПоследних.ТипЦен КАК ТипЦен
ИЗ
    РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&Дата, Номенклатура = &Номенклатура И ТипЦен = &ТипЦен) КАК ЦеныНоменклатурыСрезПоследних

Если для позиции номенклатуры цена в регистре никогда не устанавливалась, поле ЦенаПрайса принимает значение NULL. Наша цель — оставить в отчете только те строки, где ЦенаДОК <> ЦенаПрайса, отсекая пустые значения и скрывая эти системные ограничения из пользовательского интерфейса.

Сравнение двух полей в отборе СКД

По умолчанию в колонке «Правое значение» на вкладке «Отбор» платформа ожидает ввод константного значения (числа, строки, даты или ссылки). Чтобы сравнить левое поле с другим полем схемы компоновки, выполним следующие действия:

Перейдем на вкладку «Настройки» в раздел «Отбор».
Добавим новый элемент отбора. В колонке «Левое значение» выберем поле ЦенаДОК.
В колонке «Вид сравнения» установим значение Не равно.
В колонке «Правое значение» нажмем кнопку очистки (крестик). После того как значение очистится, в поле появится кнопка выбора типа значения (с буквой «Т» или многоточием).
Выберем тип Поле компоновки данных и укажем в качестве правого значения поле ЦенаПрайса.

Чтобы гарантированно исключить строки, для которых прайс-лист не заполнен (значение NULL), добавим второе условие в отбор:

Левое значение: ЦенаПрайса.
Вид сравнения: Заполнено.

Скрытие технических отборов от пользователя

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

В строке созданного отбора найдем колонку «Режим отображения» (расположена правее колонки «Правое значение»).
Установим для режима отображения значение Недоступный.
Если требуется вовсе исключить элемент из панели пользовательских настроек, снимем признак включения в пользовательские настройки в нижней части окна настроек.

В результате отбор продолжит жестко фильтровать данные при формировании отчета, но не будет отображаться в шапке или в окне пользовательских настроек формы отчета.

Альтернативный вариант: использование вычисляемых полей

Проанализируем еще один надежный способ фильтрации, который позволяет избавиться от тонкостей трехзначной логики SQL при работе со значениями NULL. Перейдем на вкладку «Вычисляемые поля» и добавим служебное поле:

Путь к данным: РасхождениеЦены.
Выражение: ЕстьNull(ЦенаПрайса, 0) - ЦенаДОК.
Заголовок: Расхождение цены.

Если требуется строго учитывать наличие установленной цены, выражение можно расширить с помощью условной конструкции:


Выбор 
    Когда ЦенаПрайса Есть NULL Тогда Ложь 
    Когда ЦенаДОК <> ЦенаПрайса Тогда Истина 
    Иначе Ложь 
Конец

После создания вычисляемого поля на вкладке «Настройки» -> «Отбор» достаточно указать условие вида ЕстьРасхождениеРавно Истина и также перевести режим отображения в «Недоступный».

Оптимизация производительности: переход на единый пакетный запрос

Обратим внимание на архитектурную особенность связывания наборов данных в СКД. При передаче параметров из первого запроса во второй (срез цен на дату документа) СКД выполняет второй запрос циклически для каждой строки первого набора данных. На больших массивах документов это вызывает существенное замедление работы отчета.

Для максимального быстродействия переведем схему на один запрос с использованием пакетного механизма и временных таблиц:


ВЫБРАТЬ
    РеализацияТовары.Ссылка КАК Документ,
    РеализацияТовары.Номенклатура КАК Номенклатура,
    НАЧАЛОПЕРИОДА(РеализацияТовары.Ссылка.Дата, ДЕНЬ) КАК ДатаДок,
    РеализацияТовары.Ссылка.ДоговорКонтрагента.ТипЦен КАК ТипЦен,
    РеализацияТовары.Цена КАК ЦенаДОК
ПОМЕСТИТЬ ВТ_ТоварыДокументов
ИЗ
    Документ.РеализацияТоваровУслуг.Товары КАК РеализацияТовары
ГДЕ
    РеализацияТовары.Ссылка.Дата МЕЖДУ &Дата1 И &Дата2
    И РеализацияТовары.Ссылка.Проведен = ИСТИНА
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ РАЗЛИЧНЫЕ
    ВТ.Номенклатура КАК Номенклатура,
    ВТ.ТипЦен КАК ТипЦен,
    ВТ.ДатаДок КАК ДатаДок
ПОМЕСТИТЬ ВТ_КлючиЦен
ИЗ
    ВТ_ТоварыДокументов КАК ВТ
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    Ключи.Номенклатура КАК Номенклатура,
    Ключи.ТипЦен КАК ТипЦен,
    Ключи.ДатаДок КАК ДатаДок,
    МАКСИМУМ(Цены.Период) КАК ПериодЦены
ПОМЕСТИТЬ ВТ_СрезДат
ИЗ
    ВТ_КлючиЦен КАК Ключи
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК Цены
        ПО Ключи.Номенклатура = Цены.Номенклатура
        И Ключи.ТипЦен = Цены.ТипЦен
        И Цены.Период <= Ключи.ДатаДок
СГРУППИРОВАТЬ ПО
    Ключи.Номенклатура,
    Ключи.ТипЦен,
    Ключи.ДатаДок
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    Срез.Номенклатура КАК Номенклатура,
    Срез.ТипЦен КАК ТипЦен,
    Срез.ДатаДок КАК ДатаДок,
    РегЦены.Цена КАК ЦенаПрайса
ПОМЕСТИТЬ ВТ_ИтоговыеЦены
ИЗ
    ВТ_СрезДат КАК Срез
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК РегЦены
        ПО Срез.Номенклатура = РегЦены.Номенклатура
        И Срез.ТипЦен = РегЦены.ТипЦен
        И Срез.ПериодЦены = РегЦены.Период
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    Док.Документ КАК Документ,
    Док.Номенклатура КАК Номенклатура,
    Док.ЦенаДОК КАК ЦенаДОК,
    Цены.ЦенаПрайса КАК ЦенаПрайса
ИЗ
    ВТ_ТоварыДокументов КАК Док
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ИтоговыеЦены КАК Цены
        ПО Док.Номенклатура = Цены.Номенклатура
        И Док.ТипЦен = Цены.ТипЦен
        И Док.ДатаДок = Цены.ДатаДок
ГДЕ
    Док.ЦенаДОК <> Цены.ЦенаПрайса

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