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

← На главную

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

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

Выясним причину такого поведения и научимся его исправлять.

Причина проблемы: неправильный тип соединения таблиц

Давайте проанализируем первоначальный запрос, который приводил к потере данных:


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

В этом запросе таблицы Документ.СписаниеНедостачТоваров.Товары и вложенный запрос с ценами соединяются через условие в секции ГДЕ. Такое соединение (перечисление таблиц через запятую в секции ИЗ и связывание их в ГДЕ) является неявным аналогом внутреннего соединения (INNER JOIN).

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

Решение 1: Используем ЛЕВОЕ СОЕДИНЕНИЕ для сохранения всех данных

Чтобы гарантировать, что все строки из "главной" (левой) таблицы попадут в отчет, независимо от наличия для них цен, необходимо использовать ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ (LEFT OUTER JOIN или просто LEFT JOIN).

Это соединение работает так:

Берутся все строки из левой таблицы (в нашем случае это `Документ.СписаниеНедостачТоваров.Товары`).
Для каждой строки из левой таблицы ищется соответствующая строка в правой таблице (`РегистрСведений.ЦеныНоменклатуры.СрезПоследних`) по заданному условию соединения.
Если соответствие найдено, поля из правой таблицы заполняются значениями.
Если соответствие не найдено, поля из правой таблицы заполняются значением NULL.

Таким образом, ни одна строка из табличной части документа не будет потеряна. Давайте перепишем запрос правильно:


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

Обратите внимание, что условие на вид цены (ВидЦены = &ВидЦены) перенесено в параметры виртуальной таблицы СрезПоследних. Это важно! Если оставить такое условие в секции ГДЕ, то LEFT JOIN фактически превратится в INNER JOIN, так как строки с NULL в поле ВидЦены будут отфильтрованы, и мы вернемся к исходной проблеме.

Обработка NULL-значений

Теперь, когда у нас в отчете есть товары без цены, в поле Цена у них будет значение NULL. При попытке выполнить математические операции с NULL (например, умножить на количество) результат также будет NULL. Чтобы избежать этого, используем функцию ЕСТЬNULL(). Она принимает два параметра: первое — проверяемое значение, второе — значение, на которое нужно заменить NULL.

Модифицируем выборку полей для корректного расчета суммы:


ВЫБРАТЬ
    ...
    ЕСТЬNULL(ЦеныНоменклатурыСрезПоследних.Цена, 0) КАК Цена,
    СписаниеНедостачТоваровТовары.Количество * ЕСТЬNULL(ЦеныНоменклатурыСрезПоследних.Цена, 0) КАК Сумма
...

Теперь, если цена не найдена, она будет считаться равной нулю, и сумма корректно рассчитается как 0.

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

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

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

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

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

Для этого используем временные таблицы.

Шаг 1. Собираем уникальную номенклатуру из документов за период во временную таблицу.
Шаг 2. В основном запросе используем эту временную таблицу для фильтрации в параметрах виртуальной таблицы СрезПоследних.

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


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

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

Такой подход на порядок производительнее, так как срез цен будет формироваться не по всему регистру, а по маленькому, заранее подготовленному списку товаров.

Дополнительная задача: Получение цены на дату каждого документа

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

1. Использование двух наборов данных в СКД (рекомендуемый способ)

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

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

При такой настройке СКД сама для каждой строки документа выполнит "маленький" запрос к регистру цен, подставив нужную дату и номенклатуру, что является очень производительным решением.

2. Сложный запрос с вложенной конструкцией

Можно добиться того же результата и одним запросом, но он будет сложнее и потенциально медленнее на больших объемах.


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

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

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