Как в отчете СКД вывести продажи с ценами закупки на дату документа

← На главную

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

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

Анализ проблемы: почему обычный Join не всегда работает

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

Проанализируем базовый запрос, который позволяет объединить товары из реализации и срез последних цен:


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

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

Решение №1: Использование двух наборов данных в СКД

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

Шаг 1. Создание НабораДанных1 (Продажи)

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


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

Шаг 2. Создание НабораДанных2 (Цены)

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


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

Шаг 3. Настройка связей наборов данных

Перейдем на закладку Связи наборов данных. Это ключевой этап. Нам нужно передать дату из первой таблицы в параметр &ПериодЦен второй таблицы для каждой строки.

Источник связи: НаборДанных1.
Приемник связи: НаборДанных2.
Условие связи: Номенклатура = НоменклатураДляЦен.
Параметры связи: В колонке «Параметр» указываем ПериодЦен, а в колонке «Выражение источника» указываем ДатаПродажи.

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

Решение №2: Использование временных таблиц в одном запросе

Если вы предпочитаете использовать один набор данных (например, для упрощения структуры отчета), рекомендуется использовать механизм временных таблиц. Это предотвратит «падение» системы по памяти, о котором упоминалось в сообщении №11 форума. Для этого можно изучить базовый пример фильтрации данных временными таблицами в запросе.

Выясним причину проблем с производительностью: когда мы делаем LEFT JOIN к физической таблице РегистрСведений.ЦеныНоменклатуры, SQL-сервер вынужден перебирать огромное количество строк. Чтобы оптимизировать это, сначала соберем все уникальные товары из наших документов.

Разберем по шагам структуру эффективного запроса:

Выбираем все товары из документов реализации за период во временную таблицу ВТ_Товары.
Индексируем поле Номенклатура во временной таблице.
Соединяем ВТ_Товары с виртуальной таблицей СрезПоследних.

Пример реализации в одном запросе (с учетом фиксированной даты среза):


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

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

Важные нюансы при работе с ценами

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

Вид цены: Всегда жестко ограничивайте выборку из регистра ЦеныНоменклатуры конкретным типом цены (например, «Закупочная»). Если этого не сделать, и для одного товара задано несколько типов цен (розничная, оптовая, закупочная), в отчете возникнет дублирование строк.
Характеристики: Если в вашей конфигурации используется учет по характеристикам, соединение обязательно должно идти по двум полям: Номенклатура и Характеристика. В противном случае цена может подтянуться некорректно.
Единицы измерения: Проверьте, в каких единицах хранится цена в регистре и в каких — в документе. Если в документе продажа идет в «коробках», а цена в регистре указана за «штуку», вам необходимо будет умножить цену на коэффициент пересчета из упаковки.
Себестоимость вместо цен: Посмотрите на ситуацию шире. Если ваша задача — посчитать прибыль, возможно, стоит брать не «цену закупки» из прайс-листа, а фактическую себестоимость из регистров накопления. В типовых конфигурациях (УТ 11, ERP) это регистр Выручка и себестоимость продаж. Там уже сопоставлена каждая продажа и её реальная стоимость закупки.

Заключение

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

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