Как вывести элементы справочника, по которым не было движений в отчете СКД 1С?

← На главную

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

В этой статье мы подробно разберем, почему при попытке вывести список всей техники с помощью левого соединения элементы без движений внезапно «исчезают» из отчета, детально проанализируем реляционную природу этой ошибки и рассмотрим эффективные способы ее решения как на уровне чистого SQL/запроса 1С, так и архитектурными средствами самой СКД.

Суть проблемы: как ЛЕВОЕ СОЕДИНЕНИЕ незаметно превращается во ВНУТРЕННЕЕ

Рассмотрим классическую ситуацию. Мы пишем запрос, в котором берем справочник-источник (например, Справочник.уатТС) и связываем его левым соединением с виртуальной таблицей оборотов регистра накопления (например, РегистрНакопления.уатВыработкаТС.Обороты). Наша цель — получить все машины из справочника, а для тех, которые выезжали на линию, вывести показатели выработки.

Запрос выглядит следующим образом:


ВЫБРАТЬ РАЗРЕШЕННЫЕ
    уатТС.Ссылка КАК ТС,
    уатВыработкаОбороты.КоличествоОборот КАК Количество
ИЗ 
    Справочник.уатТС КАК уатТС
ЛЕВОЕ СОЕДИНЕНИЕ 
    РегистрНакопления.уатВыработкаТС.Обороты(, , Регистратор, ) КАК уатВыработкаОбороты
    ПО уатТС.Ссылка = уатВыработкаОбороты.ТС
ГДЕ 
    уатВыработкаОбороты.ПараметрВыработки = &ПараметрВыработки
    И (&ОтображатьВыбывшиеТС ИЛИ уатВыработкаОбороты.ТС.ДатаВыбытия = ДАТАВРЕМЯ(1, 1, 1))

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

Выясним причину этого поведения. С точки зрения реляционной теории СУБД (будь то MS SQL, PostgreSQL или IBM DB2), когда выполняется левое соединение, для записей левой таблицы, не имеющих соответствия в правой таблице, все поля правой таблицы заполняются значениями NULL.

Когда СУБД начинает обрабатывать секцию ГДЕ (внешний фильтр запроса), она проверяет условия для каждой строки итоговой выборки. В нашем условии написано:

уатВыработкаОбороты.ПараметрВыработки = &ПараметрВыработки

Для техники без движений поле уатВыработкаОбороты.ПараметрВыработки содержит значение NULL. СУБД пытается выполнить сравнение: NULL = &ПараметрВыработки. Результатом любого сравнения с NULL всегда является значение «Ложь» (или «Неизвестно»). Строка, не прошедшая фильтр ГДЕ, безжалостно отбрасывается из результирующего набора данных. Таким образом, оптимизатор СУБД фактически преобразует наше левое соединение во внутреннее (INNER JOIN).

Давайте разберем по шагам, как исправить эту ситуацию и заставить отчет выводить все элементы справочника.

---

Способ 1. Перенос условий в параметры виртуальной таблицы

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

Перенесем отбор по параметру выработки в параметры виртуальной таблицы оборотов:


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

При таком подходе виртуальная таблица выберет только те записи регистра, которые соответствуют нашему параметру выработки. Затем СУБД выполнит честное левое соединение со справочником. Для всей остальной техники, у которой не было работы по этому параметру выработки, в колонках регистра вернется законный NULL, но сами строки со справочником ТС из итогового результата не исчезнут.

---

Способ 2. Перенос условий в секцию связи соединения (ПО)

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

Выходом является перенос условий фильтрации правой таблицы непосредственно в секцию связи ПО (ON) левого соединения. Рассмотрим пример:


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

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

---

Архитектурный анализ: Ошибки разыменования и неявные левые соединения

Проанализируем исходный запрос автора темы более глубоко. В секции ГДЕ содержится следующее условие:

&ОтображатьВыбывшиеТС ИЛИ уатВыработкаОбороты.ТС.ДатаВыбытия = ДАТАВРЕМЯ(1, 1, 1)

Здесь допущена грубая архитектурная ошибка проектирования запросов 1С — обращение к реквизитам через точку от ссылочного поля правой (присоединяемой) таблицы.

Во-первых, уатВыработкаОбороты.ТС — это поле правой таблицы. Если по машине не было движений, это поле равно NULL. Попытка получить уатВыработкаОбороты.ТС.ДатаВыбытия вернет NULL, и условие снова превратит левое соединение во внутреннее!

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

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

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


ВЫБРАТЬ РАЗРЕШЕННЫЕ
    уатТС.Ссылка КАК ТС,
    уатТС.Модель КАК МодельТС,
    ВЫБОР КОГДА уатТС.ВидМоделиТС = ЗНАЧЕНИЕ(Перечисление.уатВидыМоделейТС.Автотранспорт) 
        ТОГДА уатТС.ГосударственныйНомер 
        ИНАЧЕ уатТС.Наименование 
    КОНЕЦ КАК ТСГосНомер,
    ВЫБОР КОГДА уатТС.ВидМоделиТС = ЗНАЧЕНИЕ(Перечисление.уатВидыМоделейТС.Автотранспорт) 
        ТОГДА уатТС.ГаражныйНомер 
        ИНАЧЕ уатТС.Наименование 
    КОНЕЦ КАК ТСГарНомер,
    уатВыработкаОбороты.Период КАК Период,
    уатВыработкаОбороты.КоличествоОборот КАК Количество
ИЗ 
    Справочник.уатТС КАК уатТС
ЛЕВОЕ СОЕДИНЕНИЕ 
    РегистрНакопления.уатВыработкаТС.Обороты(, , Регистратор, ПараметрВыработки = &ПараметрВыработки) КАК уатВыработкаОбороты
    ПО уатТС.Ссылка = уатВыработкаОбороты.ТС
ГДЕ 
    (&ОтображатьВыбывшиеТС ИЛИ уатТС.ДатаВыбытия = ДАТАВРЕМЯ(1, 1, 1))

Посмотрите, как элегантно решилась проблема! Условие на выбытие теперь накладывается на гарантированно непустую левую таблицу уатТС.ДатаВыбытия. Все реквизиты ТС берутся непосредственно из справочника. Левое соединение теперь работает идеально, возвращая полный перечень машин, а для тех, которые не выходили на линию, показатели выработки будут заполнены значениями NULL.

---

Способ 3. Связи наборов данных в СКД (Best Practice)

Разработка сложных отчетов «одним большим запросом с кучей левых соединений» в среде 1С часто считается неоптимальным решением. Сама система компоновки данных предоставляет превосходную альтернативу — использование нескольких наборов данных и связей между ними.

Давайте разберем по шагам, как настроить этот механизм для нашей задачи:

Создадим НаборДанных1 (Источник). Это будет простой запрос по справочнику транспортных средств:


ВЫБРАТЬ РАЗРЕШЕННЫЕ
    уатТС.Ссылка КАК ТС,
    уатТС.Модель КАК МодельТС,
    уатТС.ГосударственныйНомер КАК ТСГосНомер,
    уатТС.ГаражныйНомер КАК ТСГарНомер
ИЗ
    Справочник.уатТС КАК уатТС
ГДЕ
    (&ОтображатьВыбывшиеТС ИЛИ уатТС.ДатаВыбытия = ДАТАВРЕМЯ(1, 1, 1))

Создадим НаборДанных2 (Приемник). Это запрос к виртуальной таблице регистра накопления с движениями:


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

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

Преимущества архитектуры связей наборов данных в СКД:

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

---

Как вывести пустые периоды по дням (Дополнение периодов в СКД)

Если мы строим табель работы техники по дням, пользователь хочет видеть красивую сетку дат: например, с 1 по 31 число месяца. Если машина 5-го числа не выходила на линию, в колонке за 5-е число должен стоять прочерк или ноль, а не просто пропускаться эта колонка.

Для реализации этой возможности в СКД встроен специальный механизм — Дополнение периодов. Настроить его можно следующим образом:

В схеме компоновки данных переходим на вкладку «Настройки».
Находим группировку по полю Период (или ПериодДень).
В окне настроек группировки переходим на вкладку «Системные поля» или свойства группировки и находим параметры дополнения периода.
Устанавливаем флаг «Использовать дополнение».
Задаем Тип дополнения — День.
Указываем Начало периода дополнения и Конец периода дополнения, связав их с общими параметрами отчета &НачалоПериода и &КонецПериода.

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