Как в СКД объединить цены и остатки в одном отчете, чтобы не терять данные при отборах?

← На главную

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

Анализ проблемы: почему стандартные соединения не работают

Проанализируем классическую ситуацию. Допустим, мы соединяем таблицу цен с таблицей остатков по номенклатуре:

Левое соединение к ценам: В отчет попадут только те товары, для которых установлена цена. Если товар есть на складе, но цена на него еще не назначена, он исчезнет из отчета.
Левое соединение к остаткам: Мы увидим только то, что есть в наличии. Номенклатура с установленными ценами, но нулевым остатком, не отобразится.
Полное соединение: Кажется идеальным вариантом, но при наложении отбора в СКД (например, Остаток > 0), строки, где остаток равен NULL (то есть товары только с ценами), будут отсечены условием ГДЕ.

Кроме того, возникает проблема «размножения» записей. Если у товара 3 вида цен и он лежит на 5 складах, при обычном соединении мы получим 15 строк для одной позиции номенклатуры, что приведет к некорректным итогам по ресурсам.

Решение через объединение (UNION ALL) и вложенные соединения

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

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


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

Что это дает: Когда пользователь ставит отбор СвободныйОстаток > 0, СКД проверяет поле в первой части объединения. Благодаря левому соединению со свернутыми остатками, у строки с ценой поле СвободныйОстаток будет заполнено (если товар в принципе есть хоть на каком-то складе). В итоге цена останется в отчете, а детализация по складам подтянется из второй части объединения.

Использование нескольких наборов данных в СКД

Выясним, существует ли более «нативный» способ для 1С 8.3. Рассмотрим механизм связей наборов данных в компоновке. Вместо того чтобы мучить один сложный запрос, мы можем создать два независимых Набора данных - запрос:

НаборЦены: выбирает Номенклатуру, Характеристику, ВидЦены и Цену.
НаборОстатки: выбирает Номенклатуру, Характеристику, Склад и Количество.

Перейдем на вкладку Связи наборов данных и настроим связь от НаборЦены к НаборОстатки (и наоборот, если нужно полное соединение) по полям Номенклатура и Характеристика. В настройках СКД укажем тип вывода «Таблица», где в строках будет Номенклатура, а в колонках — Склад и Вид цены.

Преимущества этого метода:

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

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

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

Чтобы отчет выглядел профессионально, настроим оформление полей. Если цена или остаток равны NULL, в СКД на закладке «Наборы данных» для соответствующих полей можно указать «Выражение представления» или задать формат ЧН=0 (чтобы вместо пустых ячеек выводились нули), либо оставить пустоту для наглядности.

Практические советы по оптимизации

Рассмотрим, как ускорить выполнение такого отчета на больших объемах данных:

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

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

← На главную