Почему при отборе в СКД пропадают пустые колонки и как сохранить вывод всех элементов справочника?

← На главную

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

Разберем причину такого поведения платформы и подробно рассмотрим проверенные способы решения этой задачи.

Выясним причину: почему левое соединение превращается во внутреннее

Проанализируем, как компоновщик данных обрабатывает запросы и строит итоговую кросс-таблицу:

Формирование колонок группировки: Колонки таблицы в СКД строятся исключительно на основании тех значений измерений, которые физически присутствуют в результирующем наборе записей после выполнения запроса. Если запрос не возвращает ни одной строки с определенным товаром, компоновщик не сможет создать под него колонку.
Трансляция отбора в условие запроса: По умолчанию в наборе данных включен флаг «Автозаполнение». При установке пользователем отбора по полю правой таблицы (например, Покупатель) СКД автоматически помещает это условие в секцию ГДЕ итогового SQL-запроса.
Правило реляционной алгебры: Любое условие вида ГДЕ Таблица2.Поле = Значение отсекает строки, в которых правая таблица вернула NULL. В результате ЛЕВОЕ СОЕДИНЕНИЕ превращается во ВНУТРЕННЕЕ СОЕДИНЕНИЕ еще на уровне СУБД, и элементы справочника без движений полностью исключаются из выборки.

Способ 1. Перенос отбора в секцию соединения (ПО) через расширение языка СКД

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

Для реализации этого подхода выполним следующие шаги:

Откроем схему компоновки данных и перейдем в набор данных «Запрос».
На вкладке «Компоновка данных» в конструкторе запроса перейдем на страницу «Условия».
Укажем условие отбора для правой таблицы в фигурных скобках {} внутри секции соединения ПО.

Посмотрим на пример текста запроса с расширением СКД:


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

При таком подходе, если отбор по покупателю задан, условие применяется только к записям регистра сведений. Все товары из справочника останутся в выборке, а для несоответствующих записей в полях покупателя и даты вернется значение NULL.

Способ 2. Замена соединения на объединение таблиц (ОБЪЕДИНИТЬ ВСЕ)

Разберем альтернативный и часто более производительный вариант — использование конструкции ОБЪЕДИНИТЬ ВСЕ вместо соединения таблиц. Этот подход исключает тяжелые операции JOIN на уровне СУБД и гарантирует наличие полного перечня элементов.

Посмотрим на пример структуры запроса:


ВЫБРАТЬ
    Покупки.Товар КАК Товар,
    Покупки.Покупатель КАК Покупатель,
    Покупки.Период КАК ДатаПокупки
ИЗ
    РегистрСведений.Покупки КАК Покупки
{ГДЕ
    Покупки.Покупатель.*,
    Покупки.Товар.*}

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
    СправочникТовары.Ссылка,
    NULL,
    NULL
ИЗ
    Справочник.Номенклатура КАК СправочникТовары

Проанализируем работу этого запроса:

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

Способ 3. Использование нескольких наборов данных и связей СКД

Рассмотрим архитектурный способ разделения источников на уровне схемы компоновки данных с использованием механизма «Связи наборов данных».

Разберем по шагам настройку схемы:

Набор данных 1 («Товары»): Создадим запрос к справочнику номенклатуры, получающий полный список элементов:
```
ВЫБРАТЬ
    СправочникТовары.Ссылка КАК Товар
ИЗ
    Справочник.Номенклатура КАК СправочникТовары
```

Набор данных 2 («Продажи»): Создадим отдельный запрос к регистру сведений:


ВЫБРАТЬ
    Покупки.Товар КАК Товар,
    Покупки.Покупатель КАК Покупатель,
    Покупки.Период КАК ДатаПокупки
ИЗ
    РегистрСведений.Покупки КАК Покупки

Перейдем на вкладку «Связи наборов данных» и добавим связь:
1. Источник связи: Товары;
2. Приемник связи: Продажи;
3. Условие связи: Товар = Товар.

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

Особенности обработки NULL в ресурсах

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

ЕстьNULL(Максимум(ДатаПокупки), "")

Для числовых ресурсов выражение примет вид: ЕстьNULL(Сумма(Количество), 0).