Как вывести цены закупки, продажи и оплаты в одну строку в СКД без ошибок и зависаний

← На главную

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

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

Анализ ошибки №1: Почему возникает «Недостаточно памяти» при соединении таблиц

Рассмотрим фрагмент первого запроса, который привел к падению платформы 1С:


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

Проанализируем эту конструкцию. Автор запроса указал в секции ИЗ две таблицы через запятую: таблицу документов ПоступлениеНаРасчетныйСчет и вложенную таблицу ТаблицаПродажи, не указав при этом ключевое слово ПО с условием связи между ними. В реляционных базах данных и языке запросов 1С такое описание приводит к операции декартова произведения (Cross Join).

При декартовом произведении СУБД умножает каждую строку первой таблицы на каждую строку второй таблицы. Например, если в базе данных есть 10 000 документов реализации и 5 000 документов поступления на расчетный счет, в результате их «безусловного» соединения получится временная таблица объемом в 50 000 000 записей! Оперативная память сервера мгновенно переполняется, и платформа аварийно завершает работу.

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

Анализ ошибки №2: Почему пропадают данные оплаты (СуммаДокументаРС)

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


ЛЕВОЕ СОЕДИНЕНИЕ Документ.ПоступлениеНаРасчетныйСчет КАК ПоступлениеНаРасчетныйСчет 
ПО ТаблицаСебестоимость.Регистратор.Ссылка = ПоступлениеНаРасчетныйСчет.Ссылка

Выясним причину, почему при таком соединении поле СуммаДокументаРС возвращает пустое значение (NULL). Поле Регистратор в таблице себестоимости (которая выбирается из регистра бухгалтерии по проводкам реализации) имеет тип ДокументСсылка.РеализацияТоваровУслуг. Поле Ссылка таблицы ПоступлениеНаРасчетныйСчет имеет тип ДокументСсылка.ПоступлениеНаРасчетныйСчет.

Уникальный идентификатор (GUID) документа реализации никогда не совпадет с идентификатором документа оплаты — это абсолютно разные физические сущности в базе данных. Из-за этого условие связи ПО никогда не выполняется, левое соединение возвращает пустые строки, и сумма оплаты не выводится в отчет.

Связать эти документы напрямую по ссылке невозможно. Нам необходимо использовать другие механизмы сопоставления данных.

Решение: Как правильно связать Продажи и Оплаты в СКД

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

Рассмотрим два наиболее эффективных способа решения этой задачи средствами СКД.

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

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

Создадим НаборДанных1 (Запрос) для получения информации о продажах и себестоимости:


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

Создадим второй набор данных — НаборДанных2 (Запрос) для получения оплат:


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

Перейдем на вкладку «Связи наборов данных» в конструкторе СКД и добавим новую строку связи:
- Источник связи: НаборДанных1
- Приемник связи: НаборДанных2
- Условие связи: Организация = Организация И Контрагент = Контрагент

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

Способ 2. Получение актуальных цен на дату продажи

В исходном коде автора темы присутствует еще одна архитектурная ошибка — левое соединение с виртуальной таблицей СрезПоследних без указания параметров даты:


ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних КАК ЦеныНоменклатурыСрезПоследних 
    ПО ЦеныНоменклатурыСрезПоследних.Номенклатура.Код = ТаблицаПродажи.Номенклатура.Код

Такое соединение возвращает цены, актуальные на текущую дату работы с отчетом. Если продажа была совершена полгода назад по одной цене, а сейчас цена изменилась, в отчете выведется неверная (текущая) цена. Кроме того, соединение по коду (Номенклатура.Код) работает значительно медленнее, чем по уникальной ссылке (Номенклатура).

Проанализируем, как правильно получать срез последних цен на каждую дату продажи в СКД с помощью связи наборов данных:

Создаем первый набор данных с продажами, где обязательно выбираем поле Период (дата совершения продажи):


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

Создаем второй набор данных (запрос получения цен):


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

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

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

Способ 3. Как вывести цены закупки и продажи в одну строчку (в разные колонки)

Когда вы выводите несколько типов цен (например, «Закупочная» и «Розничная»), в обычном соединении строки начинают дублироваться по вертикали, так как для одного товара в регистре цен существуют две разные записи. Нам нужно "развернуть" их по горизонтали.

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


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

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

Динамический вывод типов цен с помощью структуры «Таблица» в СКД

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

В этом случае настроим структуру вывода отчета на вкладке «Настройки» в СКД:

Вместо обычной структуры «Список» добавим структуру «Таблица».
В Строки таблицы добавим группировку по полю Номенклатура.
В Колонки таблицы добавим группировку по полю ТипЦен.
В Выбранные поля (Ресурсы) добавим поле Цена с функцией расчета Максимум(Цена).

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