Как вывести всю номенклатуру в отчете СКД, даже если по ней нет остатков и движений?

← На главную

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

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

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

Рассмотрим ситуацию, с которой столкнулся автор темы. Изначально его запрос выбирал данные из виртуальной таблицы остатков и присоединял к ней документы цен и отчетов кассовой смены:


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

Этот запрос выводил только те позиции, которые физически есть на остатке. Пытаясь исправить это, разработчик сделал базовой таблицей справочник Справочник.Номенклатура, присоединив к нему остальные таблицы левым соединением. Но номенклатура без остатков все равно не появилась в результате! Давайте разберем, почему так происходит.

Обратите внимание на секцию ГДЕ в запросе автора:


ГДЕ
    ОтчетКассовойСменыТовары.Ссылка.Проведен = ИСТИНА
    И УстановкаЦенНоменклатурыПоставщикаТовары.Ссылка.Проведен = ИСТИНА

Это главная ошибка! Использование полей присоединенных таблиц в секции ГДЕ неявно превращает ваше ЛЕВОЕ СОЕДИНЕНИЕ во ВНУТРЕННЕЕ СОЕДИНЕНИЕ.

Когда СУБД обрабатывает левое соединение для товара, у которого нет остатков или по которому не было документов, в полях документов возвращается значение NULL. Затем СУБД переходит к условию ГДЕ NULL.Проведен = ИСТИНА. Поскольку результат этого сравнения не равен ИСТИНЕ, СУБД просто отбрасывает всю строку целиком. В итоге позиции без документов и остатков исчезают из отчета.

Шаг 1. Исправляем логику соединений в запросе

Чтобы левое соединение работало корректно, все условия фильтрации полей из правых (присоединяемых) таблиц должны быть перенесены непосредственно в условия связи ПО. Давайте проанализируем, как должен выглядеть запрос после исправления этой ошибки:


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

В этом варианте мы избавились от секции ГДЕ, ограничивающей присоединенные таблицы, и перенесли проверку проведенности документов в условия связи ПО. Также мы использовали функцию ЕСТЬNULL, чтобы вместо пустых значений на экран выводились аккуратные нули.

Шаг 2. Решаем проблему производительности (Запрос зависает на 10+ минут)

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

Дело в том, что прямое соединение справочника Номенклатура (в котором могут быть десятки тысяч элементов) с табличными частями документов (Документ.ОтчетКассовойСмены.Товары) — это тяжелейшая операция для СУБД. Табличные части документов в базах данных SQL не имеют индексов по полю Номенклатура. СУБД вынуждена выполнять полное сканирование таблиц (Full Table Scan) для каждой строки номенклатуры. Ситуация усугубляется, если документы не ограничены по периоду.

Рассмотрим два профессиональных способа решения этой проблемы.

Вариант А. Оптимизация через Временные Таблицы (ВТ)

Правильным подходом при работе с запросами является предварительная подготовка данных во временных таблицах с их последующим индексированием. Давайте разберем этот процесс по шагам.

Выбираем только ту номенклатуру, которая нам действительно нужна (исключаем группы, помеченные на удаление, при необходимости накладываем фильтр по папкам).
Выбираем актуальные цены из документов за нужный период во временную таблицу и индексируем её по полю Номенклатура. Еще лучше — использовать для получения цен соответствующие регистры сведений (например, ЦеныНоменклатуры.СрезПоследних), так как они спроектированы для быстрой отдачи данных.
Соединяем подготовленные временные таблицы в финальном пакете.

Посмотрим на пример реализации такого оптимизированного запроса:


// Шаг 1: Получаем список активной номенклатуры
ВЫБРАТЬ
    Номенклатура.Ссылка КАК Товар
ПОМЕСТИТЬ ВТ_Номенклатура
ИЗ
    Справочник.Номенклатура КАК Номенклатура
ГДЕ
    НЕ Номенклатура.ЭтоГруппа
    И НЕ Номенклатура.ПометкаУдаления
ИНДЕКСИРОВАТЬ ПО
    Товар
;

// Шаг 2: Получаем остатки
ВЫБРАТЬ
    Остатки.Номенклатура КАК Товар,
    Остатки.Склад КАК Склад,
    Остатки.КоличествоОстаток КАК Остаток
ПОМЕСТИТЬ ВТ_Остатки
ИЗ
    РегистрНакопления.ОстаткиНоменклатуры.Остатки(&Период, ) КАК Остатки
ИНДЕКСИРОВАТЬ ПО
    Товар
;

// Шаг 3: Соединяем подготовленные данные
ВЫБРАТЬ
    ВТ_Номенклатура.Товар КАК Товар,
    ВТ_Остатки.Склад КАК Склад,
    ЕСТЬNULL(ВТ_Остатки.Остаток, 0) КАК Остаток
ИЗ
    ВТ_Номенклатура КАК ВТ_Номенклатура
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Остатки КАК ВТ_Остатки
        ПО ВТ_Номенклатура.Товар = ВТ_Остатки.Товар

Благодаря индексации временных таблиц по полю связи, СУБД выполнит этот запрос за доли секунды.

Вариант Б. Связь наборов данных в СКД (Лучшая практика)

Платформа 1С предоставляет великолепный инструмент для решения подобных задач на уровне самой СКД без написания сложных и тяжелых запросов. Это решение через связи наборов данных.

Вместо того чтобы пытаться объединить все данные в одном гигантском запросе, мы создадим несколько простых наборов данных (запросов) и свяжем их средствами компоновщика.

Выполним следующие шаги:

Создаем НаборДанных1 (Источник): Это будет простой запрос, который выбирает только список всей доступной номенклатуры.


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

Создаем НаборДанных2 (Получатель остатков): Запрос, который выбирает остатки.
```
ВЫБРАТЬ
    Остатки.Номенклатура КАК Товар,
    Остатки.Склад КАК Склад,
    Остатки.КоличествоОстаток КАК Остаток
ИЗ
    РегистрНакопления.ОстаткиНоменклатуры.Остатки(&Период, Номенклатура В (&СписокТоваров)) КАК Остатки
```
Обратите внимание: в параметрах виртуальной таблицы мы указали условие Номенклатура В (&СписокТоваров). Это позволит СКД передавать в запрос по остаткам только те товары, которые реально выводятся на текущую страницу отчета.
Настраиваем связи наборов данных:
- Переходим на вкладку «Связи наборов данных».
- Добавляем новую строку. Источником связи указываем НаборДанных1, приемником — НаборДанных2.
- Выражение источника: Товар. Выражение приемника: Товар.
- Параметр связи: указываем наш созданный параметр СписокТоваров.

В чем преимущество такого подхода? СКД работает чрезвычайно умно. Если пользователь наложит отбор (например, захочет посмотреть только определенную группу товаров), СКД сначала получит этот список из НабораДанных1, а затем передаст его в качестве фильтра в НаборДанных2. СУБД не придется обрабатывать остатки и документы по всей базе данных — расчет произойдет моментально только по нужным позициям!

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