При запуске и внедрении новых баз данных «1С:Предприятие» пользователи часто сталкиваются с типовой проблемой. В систему уже занесли весь справочник Номенклатура, но реальных движений товаров (поступлений, реализаций) еще не было или они были оформлены лишь частично. Когда руководство просит показать отчет, отражающий абсолютно все позиции на складе (включая те, по которым на остатках числится «0»), стандартный отчет «Ведомость по товарам на складах» выдает пустые строки или отображает только закупленный товар.
В этой статье мы подробно разберем, почему так происходит, рассмотрим профессиональные технические решения этой проблемы в Системе компоновки данных (СКД) для разработчиков, а также изучим альтернативные пользовательские способы получения такого отчета без изменения конфигурации и программирования.
Давайте выясним архитектурную причину такого поведения системы. Все стандартные складские отчеты в 1С строятся на основе виртуальных таблиц регистров накопления (например, РегистрНакопления.ТоварыНаСкладах.Остатки).
Особенность работы СУБД и платформы «1С:Предприятие» заключается в том, что в физических таблицах базы данных остатки хранятся только по тем измерениям (товарам, складам, характеристикам), по которым была совершена хотя бы одна приходная запись. Если по товару никогда не создавался документ «Поступление товаров и услуг» или «Ввод начальных остатков», то в регистре накопления физически не существует записи об этой номенклатуре. Для системы этого товара на складе «не существует» вовсе, а не просто «остаток равен нулю». Соответственно, обычные складские отчеты просто игнорируют такие позиции.
Если мы попытаемся наложить в настройках стандартного отчета фильтр вида Конечный остаток Больше или равно 0, это не решит задачу. Программа начнет отбор из уже существующих в регистре записей и отсечет отрицательные остатки, но новые товары из справочника, по которым не было движений, в отчет все равно не попадут.
Для того чтобы принудительно вывести все существующие товары, нам необходимо сделать справочник Номенклатура главным источником данных, а уже к нему присоединять остатки из регистра. В терминах теории баз данных это реализуется с помощью ЛЕВОГО СОЕДИНЕНИЯ (LEFT JOIN). Для предварительной отладки таких запросов и проверки схемы компоновки можно использовать консоль для отладки запросов и отчетов СКД.
Рассмотрим базовый запрос, который мы можем использовать при создании собственного внешнего отчета в СКД. Мы выбираем данные из справочника номенклатуры и левым соединением подтягиваем остатки:
ВЫБРАТЬ
спрНоменклатура.Ссылка КАК Ссылка,
ТоварыНаСкладахОстатки.Склад КАК Склад,
ЕСТЬNULL(ТоварыНаСкладахОстатки.ВНаличииОстаток, 0) КАК ВНаличииОстаток,
спрНоменклатура.Артикул КАК Артикул
ИЗ
Справочник.Номенклатура КАК спрНоменклатура
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки(, {(Склад В (&Склад))}) КАК ТоварыНаСкладахОстатки
ПО спрНоменклатура.Ссылка = ТоварыНаСкладахОстатки.Номенклатура
ГДЕ
НЕ спрНоменклатура.ЭтоГруппа И спрНоменклатура.Ссылка В ИЕРАРХИИ(&Группа)
Давайте проанализируем важные детали этого запроса:
ЛЕВОЕ СОЕДИНЕНИЕ: Слева находится справочник номенклатуры. Это гарантирует, что в итоговую выборку попадут абсолютно все элементы справочника, независимо от того, найдены ли для них соответствующие записи в таблице остатков справа.ЕСТЬNULL(..., 0): Если остатка по товару нет, то из правой таблицы придет значение NULL. Данная функция заменяет пустоту на красивый и понятный пользователю ноль.НЕ спрНоменклатура.ЭтоГруппа: Исключает из финального списка папки (группы) номенклатуры, оставляя только конечные элементы (товары и услуги).{(Склад В (&Склад))}: Фигурные скобки обозначают необязательное поле компоновки данных. Это позволяет пользователю накладывать отборы по складу прямо в настройках отчета без потери строк с нулевыми остатками.Хотя предыдущий запрос полностью рабочий, у него есть скрытая проблема. Если пользователь в настройках готового отчета решит сделать жесткий отбор по конкретному складу, СУБД выполнит оптимизацию, и наше левое соединение превратится во внутреннее (INNER JOIN). В результате все строки с нулевыми остатками снова исчезнут из отчета, так как поле Склад для них будет иметь значение NULL, не подходящее под отбор по конкретному складу.
Чтобы избежать этой проблемы, мы рекомендуем использовать архитектурное решение СКД с использованием двух независимых наборов данных:
ВЫБРАТЬ
Номенклатура.Ссылка КАК Номенклатура,
Номенклатура.Артикул КАК Артикул
ИЗ
Справочник.Номенклатура КАК Номенклатура
ГДЕ
НЕ Номенклатура.ЭтоГруппа
ВЫБРАТЬ
ТоварыОстатки.Номенклатура КАК Номенклатура,
ТоварыОстатки.Склад КАК Склад,
ТоварыОстатки.ВНаличииОстаток КАК Остаток
ИЗ
РегистрНакопления.ТоварыНаСкладах.Остатки КАК ТоварыОстатки
НаборДанных1НаборДанных2Номенклатура = НоменклатураПри такой схеме СКД сама корректно соединит таблицы на уровне платформы. Если пользователь установит отбор по конкретному складу, система сначала отфильтрует остатки во втором наборе, а затем соединит их с полным списком номенклатуры из первого набора. В итоге пользователь увидит всю номенклатуру, но остатки будут выведены только по выбранному складу, а по остальным позициям отобразится пустота (или ноль).
Если перед нами стоит задача вывести красивый двумерный отчёт (кросс-таблицу), где в строках идет «Номенклатура», а в колонках распределены «Склады», обычное левое соединение сгруппирует все товары с нулевыми остатками в одну колонку с пустым заголовком (поскольку поле Склад для них равно NULL).
Для решения этой проблемы мы применим продвинутый прием — построение виртуальной сетки с помощью полного (кросс) соединения всех товаров со всеми складами:
ВЫБРАТЬ
Номенклатура.Ссылка КАК Номенклатура,
Склады.Ссылка КАК Склад
ПОМЕСТИТЬ ВТ_КартаПроизведения
ИЗ
Справочник.Номенклатура КАК Номенклатура
КРОСС СОЕДИНЕНИЕ Справочник.Склады КАК Склады
ГДЕ
НЕ Номенклатура.ЭтоГруппа
И НЕ Склады.ЭтоГруппа
;
ВЫБРАТЬ
ВТ_Карта.Номенклатура,
ВТ_Карта.Склад,
ЕСТЬNULL(Остатки.ВНаличииОстаток, 0) КАК Остаток
ИЗ
ВТ_КартаПроизведения КАК ВТ_Карта
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки КАК Остатки
ПО ВТ_Карта.Номенклатура = Остатки.Номенклатура
И ВТ_Карта.Склад = Остатки.Склад
Этот запрос принудительно генерирует уникальную строку для каждого товара под каждый существующий склад. После этого левое соединение с остатками заполнит цифрами те ячейки, где товар действительно есть на складе, а во всех остальных выставит честный «0». При формировании кросс-таблицы структура отчета будет выглядеть безупречно.
Использование функции ЕСТЬNULL() непосредственно в тексте запроса на терабайтных базах данных с миллионами записей может приводить к снижению производительности СУБД. Профессиональным стандартом разработки в 1С является перенос форматирования пустых значений на уровень оформления интерфейса СКД.
Давайте настроим оформление в СКД по шагам:
ЧН=0 (это означает «Число Нуль равно 0»). Нажмем ОК.ВНаличииОстаток).NULL) на нули при выводе отчета пользователю, не нагружая СУБД лишними функциями в запросе.Если вы являетесь конечным пользователем программы и у вас нет возможности или навыков для создания внешних отчетов, рассмотрим штатные обходные пути в популярных конфигурациях 1С.
В некоторых старых и ERP-конфигурациях есть замечательная штатная возможность выгрузить всю номенклатуру через обработку формирования прайс-листов:
Справочники -> Номенклатура -> Прайс-лист (или через меню отчетов по продажам).Остаток.В современных конфигурациях, таких как «1С:Розница» или «1С:Управление нашей фирмой», прайс-листы часто не имеют такой гибкой настройки, но мы можем использовать складской механизм инвентаризации.
Склад и откроем список документов «Пересчеты товаров» (в старых версиях — «Инвентаризация товаров»).Для целей планирования закупок отлично подойдет специализированный инструмент:
Закупки -> Расчет потребностей.Выбирайте наиболее удобный для вас метод в зависимости от вашей роли в системе и используемой конфигурации «1С:Предприятие»!