При разработке отчетов в системе компоновки данных (СКД) часто возникает задача получить в одной таблице и остатки, и обороты товаров. Сложность появляется, когда учет этих данных ведется с разной степенью детализации. Классический пример — товары со сроками годности, где остатки могут храниться в общем по номенклатуре, а движения (приход/расход) — в разрезе конкретных серий (партий, сроков годности).
Рассмотрим типовую проблему: в отчете появляется проданная партия товара с нулевым остатком, но ей ошибочно присваивается количество из новой, только что поступившей партии. Это приводит к задвоению строк и неверным итоговым данным. Давайте разберемся, почему это происходит и как это исправить.
Корень зла кроется в логике SQL-запроса, который пытается соединить таблицы с разной аналитикой. Проанализируем исходный запрос, который приводил к ошибке. Главная его часть выглядит примерно так:
...
ИЗ
Справочник.Номенклатура КАК СправочникНоменклатура
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки КАК ТоварыНаСкладахОстатки
ПО (ТоварыНаСкладахОстатки.Номенклатура = СправочникНоменклатура.Ссылка)
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ДвиженияСерийТоваров.Обороты КАК ДвиженияСерийТоваровОбороты
ПО (ДвиженияСерийТоваровОбороты.Номенклатура = СправочникНоменклатура.Ссылка)
...
Здесь происходит следующее:
ТоварыНаСкладах.Остатки. Она содержит одну строку на каждую номенклатуру с ее общим остатком (например, "Товар А", остаток 10 шт). В этой таблице нет информации о сериях.ДвиженияСерийТоваров.Обороты. Она содержит несколько строк для той же номенклатуры, по одной на каждую серию (например, "Товар А, серия 1", "Товар А, серия 2").Когда СУБД выполняет соединение (JOIN) этих таблиц по полю Номенклатура, она для каждой строки из таблицы оборотов по сериям подставляет одну и ту же строку с общим остатком. Если у товара было две серии, то строка с общим остатком продублируется дважды. В результате отчет покажет неверный, завышенный остаток.
Попытки решить эту проблему "в лоб" с помощью ключевого слова РАЗЛИЧНЫЕ (DISTINCT) или громоздкой конструкции СГРУППИРОВАТЬ ПО по всем полям лишь маскируют проблему, но не исправляют логическую ошибку в расчетах итогов.
Самый логичный способ исправить ошибку — получать все данные в одинаковом разрезе. Если нам нужны обороты по сериям, значит, и остатки нужно получать по сериям. Для этого необходимо использовать регистр, который хранит остатки с нужной нам детализацией. В зависимости от конфигурации это может быть ТоварыПоСериям.Остатки или другой аналогичный регистр.
Важный момент: Необходимо заменить источник данных об остатках.
Вместо соединения с РегистрНакопления.ТоварыНаСкладах.Остатки, которое дает общие остатки, нужно соединяться с регистром, который хранит остатки в разрезе серий. Давайте предположим, что такой регистр называется ТоварыПоСериям.
Модифицированный запрос мог бы выглядеть так. Основная идея — получить данные из двух виртуальных таблиц с одинаковой аналитикой (Номенклатура, Характеристика, Серия) и затем соединить их.
ВЫБРАТЬ
ДанныеПоСериям.Номенклатура,
ДанныеПоСериям.Характеристика,
ДанныеПоСериям.Серия,
ДанныеПоСериям.Штрихкод,
ДанныеПоСериям.Цена,
ЕСТЬNULL(ОстаткиСерий.КоличествоОстаток, 0) КАК Остаток,
ЕСТЬNULL(ОборотыСерий.Приход, 0) КАК Приход,
ЕСТЬNULL(ОборотыСерий.Расход, 0) КАК Расход
ИЗ
// Вложенный запрос для получения всех уникальных серий и связанной информации
(ВЫБРАТЬ РАЗЛИЧНЫЕ
ДвиженияСерий.Номенклатура КАК Номенклатура,
ДвиженияСерий.Характеристика КАК Характеристика,
ДвиженияСерий.Серия КАК Серия,
Штрихкоды.Штрихкод КАК Штрихкод,
Цены.Цена КАК Цена
ИЗ
РегистрНакопления.ДвиженияСерийТоваров КАК ДвиженияСерий
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Штрихкоды КАК Штрихкоды
ПО ДвиженияСерий.Номенклатура = Штрихкоды.Владелец
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних КАК Цены
ПО ДвиженияСерий.Номенклатура = Цены.Номенклатура) КАК ДанныеПоСериям
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыПоСериям.Остатки(
&Период,
Номенклатура В (&СписокНоменклатуры)
) КАК ОстаткиСерий
ПО ДанныеПоСериям.Номенклатура = ОстаткиСерий.Номенклатура
И ДанныеПоСериям.Характеристика = ОстаткиСерий.Характеристика
И ДанныеПоСериям.Серия = ОстаткиСерий.Серия
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ДвиженияСерийТоваров.Обороты(
&НачалоПериода, &КонецПериода,
,
Номенклатура В (&СписокНоменклатуры)
) КАК ОборотыСерий
ПО ДанныеПоСериям.Номенклатура = ОборотыСерий.Номенклатура
И ДанныеПоСериям.Характеристика = ОборотыСерий.Характеристика
И ДанныеПоСериям.Серия = ОборотыСерий.Серия
ГДЕ
// Условие, чтобы показывать только серии с остатком или движениями
(ЕСТЬNULL(ОстаткиСерий.КоличествоОстаток, 0) > 0 ИЛИ ЕСТЬNULL(ОборотыСерий.КоличествоОборот, 0) <> 0)
Такой подход гарантирует, что на каждую уникальную связку "Номенклатура + Серия" придется не более одной строки с остатком и одной строки с оборотами, что исключает дублирование.
Система компоновки данных предоставляет более мощный и гибкий механизм для решения таких задач — связанные наборы данных. Этот подход является предпочтительным, так как он проще в настройке и часто производительнее.
Разберем по шагам, как это сделать:
Создаем первый набор данных "Остатки"
Создайте новый набор данных-запрос. В нем мы получим только общие остатки по номенклатуре. Запрос будет максимально простым:
ВЫБРАТЬ
ТоварыНаСкладахОстатки.Номенклатура,
ТоварыНаСкладахОстатки.Склад,
ТоварыНаСкладахОстатки.КоличествоОстаток КАК Остаток,
ТоварыНаСкладахОстатки.РезервОстаток КАК Резерв
ИЗ
РегистрНакопления.ТоварыНаСкладах.Остатки(&Период, ) КАК ТоварыНаСкладахОстатки
Создаем второй набор данных "ДвиженияПоСериям"
Добавьте еще один набор данных-запрос. В нем мы получим всю детализацию по сериям и другую связанную информацию (штрихкоды, цены).
ВЫБРАТЬ
ДвиженияСерийТоваровОбороты.Номенклатура,
ДвиженияСерийТоваровОбороты.Серия,
ДвиженияСерийТоваровОбороты.КоличествоПриход КАК Приход,
ДвиженияСерийТоваровОбороты.КоличествоРасход КАК Расход,
Штрихкоды.Штрихкод,
ЦеныНоменклатуры.Цена
ИЗ
РегистрНакопления.ДвиженияСерийТоваров.Обороты(&НачалоПериода, &КонецПериода, , ) КАК ДвиженияСерийТоваровОбороты
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Штрихкоды КАК Штрихкоды
ПО ДвиженияСерийТоваровОбороты.Номенклатура = Штрихкоды.Владелец
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&Период, ) КАК ЦеныНоменклатуры
ПО ДвиженияСерийТоваровОбороты.Номенклатура = ЦеныНоменклатуры.Номенклатура
Настраиваем связи между наборами данных
Перейдите на вкладку "Связи наборов данных" в конструкторе схемы компоновки. Создайте новую связь:
ДвиженияПоСериям (набор с детальными данными).Остатки (набор с общими данными).Номенклатура.Номенклатура.Теперь СКД знает, как связаны эти два набора. При построении отчета система сама выполнит "умное" соединение: она возьмет детальные данные из набора-источника и для каждой группировки по номенклатуре подтянет соответствующее итоговое значение из набора-приемника, не создавая лишних строк.
Настраиваем ресурсы и структуру отчета
На вкладке "Ресурсы" укажите, какие поля являются числовыми показателями (Остаток, Резерв, Приход, Расход) и как их нужно агрегировать (обычно Сумма).
На вкладке "Настройки" создайте структуру отчета, например, группировку по Номенклатуре, а внутри нее — детальные записи с полем Серия и остальными показателями. СКД корректно рассчитает итоги для каждой строки и группировки.
Главное преимущество этого подхода — разделение логики. Каждый запрос выполняет свою простую задачу, а сложную работу по объединению данных берет на себя механизм СКД. Это делает отчет более читаемым, легким в поддержке и часто более быстрым.