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