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

Разработка управленческих отчетов в системе 1С с использованием Системы компоновки данных (СКД) — стандартная задача для программиста. Однако при попытке соединить в одном отчете текущие остатки товаров на складах и показатели оборотов (например, заказы клиентов или продажи за период) разработчики часто сталкиваются с неприятными проблемами. Данные либо начинают бесследно исчезать, либо выводятся некорректно, существенно расходясь с показателями типовых отчетов.

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

Анализ исходного запроса: логическая ловушка INNER JOIN

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

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

В этой конструкции кроется классическая логическая ловушка, часто встречающаяся у начинающих разработчиков. Обратите внимание на последовательность соединений. Мы делаем ЛЕВОЕ СОЕДИНЕНИЕ от таблицы справочника к таблице остатков, но внутри этого соединения прописано ВНУТРЕННЕЕ СОЕДИНЕНИЕ с таблицей ЗаказыКлиентов.

Для СУБД такое описание означает следующее: сначала необходимо выполнить жесткое внутреннее сопоставление (INNER JOIN) между остатками и заказами клиентов. В результате этого шага останутся только те строки, по которым одновременно есть и физический остаток на складе, и заказы за выбранный период времени. Если по какому-то товару остаток есть, но за период &ДатаНач - &ДатаКон не было ни одного заказа, эта строка будет полностью исключена из промежуточного набора данных.

И только после этого СУБД выполнит левое соединение справочника номенклатуры с полученным урезанным набором. В итоге все товары, по которым не было движений по заказам в указанном периоде, получат пустые значения (NULL) в поле остатка. Именно поэтому у автора пропало около 15 тысяч позиций номенклатуры.

Разница между регистрами: физический остаток против свободного

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

1. Регистр «Товары на складах» — отражает исключительно физическое наличие товаров в ячейках или на складах. Этот регистр не знает, зарезервирован ли товар под конкретного клиента, планируется ли его отгрузка или он полностью свободен для продажи.
2. Регистр «Свободные остатки» (или вычисляемые данные в типовых схемах) — хранит информацию о доступном лимите. Свободный остаток рассчитывается по формуле: Доступно = В наличии на складе - Зарезервировано.

Типовой отчет «Остатки и доступность» собирает сложный пакет данных, в котором анализируются не только физический складской остаток, но и регистры ТоварыКОтгрузке, ГрафикПоступленияТоваров, а также планируемые резервы. Если товар зарезервирован под заказ, его свободный остаток для продажи будет равен нулю, хотя физически на складе (в регистре ТоварыНаСкладах) может лежать большое количество единиц. При сверке отчетов всегда важно понимать, какой именно тип остатка вы выводите.

Ловушка простого левого соединения: дублирование (задвоение) остатков

На форумах часто предлагают простое решение: заменить внутреннее соединение на левое (LEFT JOIN). Давайте разберем по шагам, к какой критической ошибке в расчетах это приведет.

Представим ситуацию: на складе лежит товар «Смартфон» в количестве 10 штук. За выбранный период клиенты оформили на него три разных заказа: на 2 штуки, на 1 штуку и на 5 штук. Если мы свяжем таблицу остатков напрямую с таблицей заказов через ЛЕВОЕ СОЕДИНЕНИЕ, СУБД сформирует декартово произведение строк для совпавших ключей. На выходе мы получим три строки в результирующей таблице:

1. Смартфон | Остаток на складе: 10 | Заказано: 2 | Номер заказа: №1
2. Смартфон | Остаток на складе: 10 | Заказано: 1 | Номер заказа: №2
3. Смартфон | Остаток на складе: 10 | Заказано: 5 | Номер заказа: №3

Когда этот запрос будет передан в СКД, и система начнет рассчитывать ресурсы по группировкам (например, выводить общий итог по группе номенклатуры или по складу в целом), она применит агрегатную функцию СУММА(ВНаличииОстаток). СКД сложит значения во всех строках: 10 + 10 + 10 = 30. Отчет покажет, что у вас на складе лежит 30 смартфонов вместо реальных 10! Это опаснейшая ошибка, которая может привести к дезинформации отдела продаж и закупщиков.

Запомните правило: никогда не соединяйте таблицы остатков с таблицами оборотов (документов, движений) напрямую через соединения JOIN, если у них разная степень детализации данных.

Для правильного решения этой задачи в платформе 1С предусмотрено два архитектурных подхода.

Архитектурно верное решение №1: Связь наборов данных в СКД (Рекомендуемое)

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

Проанализируем этот алгоритм подробно. Перейдем на вкладку «Наборы данных» в конструкторе схемы СКД.

Создадим первый набор данных — НаборДанныхОстатки (тип «Запрос»):

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

Этот запрос получает чистый список номенклатуры и актуальные физические остатки на указанную дату. Здесь нет никаких заказов и дат документов.

Создадим второй набор данных — НаборДанныхЗаказы (тип «Запрос»):

ВЫБРАТЬ
    ЗаказыКлиентов.Номенклатура КАК Номенклатура,
    ЗаказыКлиентов.Характеристика КАК Характеристика,
    ЗаказыКлиентов.Период КАК ДатаЗаказа,
    ЗаказыКлиентов.Заказано КАК Заказано
ИЗ
    РегистрНакопления.ЗаказыКлиентов КАК ЗаказыКлиентов
ГДЕ
    ЗаказыКлиентов.Период МЕЖДУ &ДатаНач И &ДатаКон

Этот запрос выбирает исключительно обороты по заказам клиентов за заданный интервал времени.

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

• Источник связи: НаборДанныхОстатки
• Приемник связи: НаборДанныхЗаказы
• Условие связи: Номенклатура = Номенклатура
• Параметры: При необходимости можно передать параметры начала и окончания периода во второй набор данных.

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

Архитектурно верное решение №2: Использование пакета запросов с ОБЪЕДИНИТЬ ВСЕ

Если вам по какой-то причине необходимо получить все данные в рамках одного запроса (например, для выгрузки в таблицу значений в коде или для сложной постобработки), то вместо соединений (JOIN) следует использовать объединение таблиц (UNION ALL) с последующей группировкой.

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

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

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВТ_ОстаткиИОбороты.Номенклатура КАК Номенклатура,
    СУММА(ВТ_ОстаткиИОбороты.ВНаличииОстаток) КАК ВНаличииОстаток,
    СУММА(ВТ_ОстаткиИОбороты.Заказано) КАК Заказано
ПОМЕСТИТЬ ВТ_СвернутыеДанные
ИЗ
    (ВЫБРАТЬ
        ТоварыНаСкладахОстатки.Номенклатура КАК Номенклатура,
        ТоварыНаСкладахОстатки.ВНаличииОстаток КАК ВНаличииОстаток,
        0 КАК Заказано
    ИЗ
        РегистрНакопления.ТоварыНаСкладах.Остатки(&ПериодОстатков, ) КАК ТоварыНаСкладахОстатки
    
    ОБЪЕДИНИТЬ ВСЕ
    
    ВЫБРАТЬ
        ЗаказыКлиентов.Номенклатура,
        0,
        ЗаказыКлиентов.Заказано
    ИЗ
        РегистрНакопления.ЗаказыКлиентов КАК ЗаказыКлиентов
    ГДЕ
        ЗаказыКлиентов.Период МЕЖДУ &ДатаНач И &ДатаКон) КАК ВТ_ОстаткиИОбороты

СГРУППИРОВАТЬ ПО
    ВТ_ОстаткиИОбороты.Номенклатура
;

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

Разберем, как работает этот пакет:

1. В таблице ВТ_Номенклатура мы подготавливаем список товаров со всеми необходимыми реквизитами из справочника.
2. В секции ВТ_ОстаткиИОбороты мы используем конструкцию ОБЪЕДИНИТЬ ВСЕ. Мы выбираем остатки из регистра остатков, при этом в поле Заказано принудительно подставляем числовой ноль. Вторую часть объединения мы строим на основе оборотов регистра заказов, где в поле остатка также пишем ноль.
3. Во внешней виртуальной таблице ВТ_СвернутыеДанные мы выполняем группировку по полю Номенклатура и суммируем числовые показатели. Свертка полностью исключает дублирование строк и искажение итогов.
4. На финальном шаге мы соединяем исходный справочник номенклатуры с предварительно сгруппированными данными по остаткам и заказам. Этот подход работает чрезвычайно быстро и гарантирует абсолютную точность вычислений.

Правильная работа с параметрами дат виртуальных таблиц в СКД

Важно обратить внимание на то, как виртуальная таблица остатков работает с параметром даты. Если в конструкторе запроса написать РегистрНакопления.ТоварыНаСкладах.Остатки без передачи первого параметра периода, система будет возвращать остатки на актуальный текущий момент времени (на "сейчас").

Если пользователь формирует отчет за прошлый месяц, а параметры виртуальной таблицы остатков не настроены, отчет покажет остатки на сегодняшнюю секунду, сопоставив их с оборотами прошлых периодов. Это логическая ошибка.

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

КонецПериода(&ПериодРегистрации, "День")

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

Новый Граница(КонецПериода(&ДатаКон, ЧастьВремени.День), ВидГраницы.Включая)

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