Как вывести в отчете 1С на СКД только ту номенклатуру, у которой есть ненулевые остатки?

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

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

Определение проблемы и выбор метода фильтрации

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

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

1. Использование ВНУТРЕННЕГО СОЕДИНЕНИЯ с виртуальной таблицей остатков регистра накопления.
2. Применение подзапроса для предварительного отбора номенклатуры, имеющей остатки.

Давайте рассмотрим каждый из этих подходов подробнее, выясним их преимущества и особенности применения.

Метод 1: Использование ВНУТРЕННЕГО СОЕДИНЕНИЯ (INNER JOIN)

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

Шаги реализации в СКД:

1. Создание нового отчета: Откройте конфигуратор, создайте новый внешний отчет или откройте существующий (в качестве основы можно взять пример отчета СКД на УФ). Перейдите на вкладку "Макеты" и создайте новый макет типа "Схема Компоновки Данных".
2. Создание набора данных – Запрос: В открывшейся схеме СКД (хотя иногда применяются и две разных схемы в одном отчете) перейдите на вкладку "Наборы данных". Добавьте новый набор данных типа "Запрос".
3. Формирование текста запроса: В окне конструктора запросов, консоли запросов для УФ или вручную введите следующий текст запроса. Обратите внимание, что мы используем ВНУТРЕННЕЕ СОЕДИНЕНИЕ, а условие накладывается на поле КоличествоОстаток из виртуальной таблицы остатков.

Пример кода запроса с ВНУТРЕННИМ СОЕДИНЕНИЕМ:

ВЫБРАТЬ РАЗЛИЧНЫЕ
    Номенклатура.Ссылка КАК Номенклатура,
    Номенклатура.Артикул,
    Номенклатура.Наименование,
    ТоварыНаСкладахОстатки.КоличествоОстаток КАК КоличествоОстаток
ИЗ
    Справочник.Номенклатура КАК Номенклатура
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки КАК ТоварыНаСкладахОстатки
        ПО Номенклатура.Ссылка = ТоварыНаСкладахОстатки.Номенклатура
ГДЕ
    ТоварыНаСкладахОстатки.КоличествоОстаток > 0

Разберем этот запрос:

• ВЫБРАТЬ РАЗЛИЧНЫЕ Номенклатура.Ссылка КАК Номенклатура, Номенклатура.Артикул, Номенклатура.Наименование, ТоварыНаСкладахОстатки.КоличествоОстаток КАК КоличествоОстаток: Мы выбираем уникальные ссылки, артикул и наименование номенклатуры, а также поле КоличествоОстаток. Ключевое слово РАЗЛИЧНЫЕ важно, если одна и та же номенклатура имеет остатки на нескольких складах и вы не хотите ее дублировать в общем списке (хотя в случае дальнейшей группировки по номенклатуре СКД сделает это сама).
• ИЗ Справочник.Номенклатура КАК Номенклатура: Начинаем выборку из справочника номенклатуры, давая ему псевдоним Номенклатура.
• ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки КАК ТоварыНаСкладахОстатки ПО Номенклатура.Ссылка = ТоварыНаСкладахОстатки.Номенклатура: Здесь мы выполняем ВНУТРЕННЕЕ СОЕДИНЕНИЕ справочника Номенклатура с виртуальной таблицей остатков регистра накопления ТоварыНаСкладах. Псевдоним ТоварыНаСкладахОстатки позволяет удобно обращаться к полям этой таблицы. Условие соединения ПО Номенклатура.Ссылка = ТоварыНаСкладахОстатки.Номенклатура связывает записи по полю ссылки номенклатуры.
• ГДЕ ТоварыНаСкладахОстатки.КоличествоОстаток > 0: Это самое важное условие. Оно отфильтровывает все записи, где остаток равен нулю или отрицателен. Если нам нужны все ненулевые остатки (положительные и отрицательные), мы могли бы использовать ТоварыНаСкладахОстатки.КоличествоОстаток <> 0.

Настройка полей и отборов в СКД:

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

1. Закладка "Поля": Убедитесь, что все необходимые поля (Номенклатура, Артикул, Наименование, КоличествоОстаток) добавлены в список доступных полей.
2. Закладка "Параметры": Если виртуальная таблица ТоварыНаСкладах.Остатки имеет параметры (например, Период, Склад), они будут автоматически определены СКД. Вы можете установить их значения по умолчанию или сделать их доступными для пользователя. Например, для Период можно установить значение КонецПериода(Дата, "ДЕНЬ").
3. Закладка "Настройки":
   • Добавьте группировку по Номенклатура, чтобы получить итоговые данные по каждой позиции.
   • В "Выбранных полях" укажите Номенклатура.Артикул, Номенклатура.Наименование и Сумма(КоличествоОстаток).
   • Вы можете добавить отбор по значению КоличествоОстаток в общих отборах отчета, но запрос уже фильтрует данные на уровне источника, что обычно более оптимально.

Этот подход хорошо работает для относительно небольших и средних объемов данных, когда требуется вывести агрегированный остаток по номенклатуре. Однако, при очень больших объемах данных, где регистр остатков содержит миллионы записей, ВНУТРЕННЕЕ СОЕДИНЕНИЕ с последующей группировкой может быть не самым производительным решением, особенно если нам нужна только уникальная номенклатура, а не детализация по складам.

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

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

Шаги реализации в СКД:

1. Создание набора данных – Запрос: Аналогично первому методу, создайте новый набор данных типа "Запрос".
2. Формирование текста запроса с подзапросом: Введите следующий текст запроса. Здесь мы используем подзапрос в условии ГДЕ Номенклатура.Ссылка В (...).

Пример кода запроса с подзапросом:

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

Разберем этот запрос:

• ВЫБРАТЬ Номенклатура.Ссылка КАК Номенклатура, Номенклатура.Артикул, Номенклатура.Наименование: В основной выборке мы просто выбираем нужные поля из справочника Номенклатура. Нам не нужно сразу вытаскивать КоличествоОстаток в этой части, если мы хотим получить просто список номенклатуры.
• ИЗ Справочник.Номенклатура КАК Номенклатура: Основной источник данных – справочник Номенклатура.
• ГДЕ Номенклатура.Ссылка В (...): Здесь находится ключевая часть. Мы используем оператор В, чтобы отобрать только те записи номенклатуры, ссылки которых содержатся в результате выполнения подзапроса.
• (ВЫБРАТЬ ТоварыНаСкладахОстатки.Номенклатура ИЗ РегистрНакопления.ТоварыНаСкладах.Остатки КАК ТоварыНаСкладахОстатки ГДЕ ТоварыНаСкладахОстатки.КоличествоОстаток > 0): Это наш подзапрос. Он выполняет следующие действия:
  • Выбирает ссылки на номенклатуру из виртуальной таблицы РегистрНакопления.ТоварыНаСкладах.Остатки.
  • Применяет условие ГДЕ ТоварыНаСкладахОстатки.КоличествоОстаток > 0, чтобы отобрать только те записи, где остаток положительный.
  • Результатом подзапроса будет список ссылок на номенклатуру, по которой есть положительные остатки.

Преимущества подхода с подзапросом:

• Производительность: Для больших объемов данных подзапрос может быть более производительным, так как он сначала формирует список ссылок, а затем основная выборка работает только с этим уже отфильтрованным набором. Это позволяет СУБД лучше оптимизировать запрос.
• Явность: Структура запроса более четко отражает логику: "выбрать номенклатуру, где ее ссылка есть в списке номенклатуры с остатками".
• Точный результат: Этот метод гарантирует получение списка уникальной номенклатуры, по которой есть остатки, без необходимости использовать РАЗЛИЧНЫЕ в основной выборке, если мы не агрегируем данные.

Добавление остатков при использовании подзапроса:

Если после получения списка номенклатуры с остатками нам все же понадобится вывести и сами остатки (например, общий остаток по каждой позиции), мы можем добавить еще один набор данных или использовать ЛЕВОЕ СОЕДИНЕНИЕ во втором наборе данных СКД, уже зная, что вся номенклатура в первом наборе имеет остатки. Либо же, мы можем модифицировать запрос, добавив ЛЕВОЕ СОЕДИНЕНИЕ с таблицей остатков *после* фильтрации номенклатуры подзапросом.

Пример комбинированного запроса для получения номенклатуры и её общего остатка:

ВЫБРАТЬ
    Номенклатура.Ссылка КАК Номенклатура,
    Номенклатура.Артикул,
    Номенклатура.Наименование,
    СУММА(ТоварыНаСкладахОстатки.КоличествоОстаток) КАК ОбщийОстаток
ИЗ
    Справочник.Номенклатура КАК Номенклатура
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки КАК ТоварыНаСкладахОстатки
        ПО Номенклатура.Ссылка = ТоварыНаСкладахОстатки.Номенклатура
ГДЕ
    Номенклатура.Ссылка В
        (ВЫБРАТЬ РАЗЛИЧНЫЕ
            ТоварыНаСкладахОстаткиПодзапрос.Номенклатура
        ИЗ
            РегистрНакопления.ТоварыНаСкладах.Остатки КАК ТоварыНаСкладахОстаткиПодзапрос
        ГДЕ
            ТоварыНаСкладахОстаткиПодзапрос.КоличествоОстаток <> 0)
СГРУППИРОВАТЬ ПО
    Номенклатура.Ссылка,
    Номенклатура.Артикул,
    Номенклатура.Наименование
ИМЕЮЩИЕ
    СУММА(ТоварыНаСкладахОстатки.КоличествоОстаток) <> 0

В этом более сложном запросе мы сначала фильтруем номенклатуру подзапросом (чтобы в принципе работала основная выборка), затем соединяем ее с остатками (уже отфильтрованной номенклатуры), суммируем остатки и отсекаем те, у кого в итоге по всем складам остаток стал нулевым (это может произойти, например, если остаток на одном складе был +5, на другом -5, а нам нужны только ненулевые общие остатки).

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

Важные аспекты работы с виртуальными таблицами остатков

При работе с виртуальной таблицей РегистрНакопления.ТоварыНаСкладах.Остатки (или любой другой виртуальной таблицей остатков/оборотов) необходимо помнить о ее параметрах:

• Период: Всегда указывайте период, на который запрашиваются остатки. Если не указать, остатки будут получены на текущий момент времени. В СКД этот параметр автоматически создается и ему можно задать значение по умолчанию (например, &КонецПериода или КонецДня(ТекущаяДата())).
• Условия: Вы можете накладывать дополнительные условия на поля виртуальной таблицы, например, Склад = &ВыбранныйСклад, чтобы получить остатки только по конкретному складу. Эти условия можно указывать как в подзапросе, так и в основной части запроса при ВНУТРЕННЕМ СОЕДИНЕНИИ.

Корректная работа с параметрами виртуальных таблиц – ключ к получению точных и производительных отчетов.

Настройка СКД для окончательного вывода

После выбора и реализации запроса (будь то с ВНУТРЕННИМ СОЕДИНЕНИЕМ или с подзапросом), финальный этап – это настройка внешнего вида отчета в СКД. Мы уже кратко касались этого, но давайте углубимся:

1. Выбранные поля: На вкладке "Настройки" СКД, в разделе "Выбранные поля" определите, какие данные вы хотите видеть. Для номенклатуры с остатками это обычно Номенклатура.Наименование, Номенклатура.Артикул, и агрегированная сумма КоличествоОстаток (если вы используете ВНУТРЕННЕЕ СОЕДИНЕНИЕ).
2. Группировки: Добавьте группировку по Номенклатура. Это позволит собрать все остатки по разным складам (если они были выбраны в запросе) в одну строку для каждой позиции номенклатуры. Если вы хотите видеть детализацию по складам, то сначала сгруппируйте по Номенклатура, а затем по Склад.
3. Ресурсы: Если в вашем запросе есть количественные показатели (например, КоличествоОстаток), они должны быть определены как ресурсы на соответствующей вкладке СКД. Для остатков обычно используется функция СУММА().
4. Параметры и отборы: Проверьте параметры, которые СКД автоматически подтянула из запроса (например, Период). На вкладке "Параметры" вы можете настроить их видимость, значение по умолчанию и режим редактирования. На вкладке "Отборы" можно добавить дополнительные фильтры, например, по группе номенклатуры или по конкретным складам.

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

Заключение

Мы подробно рассмотрели два основных и наиболее эффективных способа вывода в отчете 1С на СКД только той номенклатуры, у которой есть ненулевые остатки. Выбор между ВНУТРЕННИМ СОЕДИНЕНИЕМ и подзапросом зависит от специфики вашей задачи, объема данных и требований к производительности. В большинстве случаев оба подхода приводят к желаемому результату.

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