Часто при разработке отчетов в системе компоновки данных (СКД) программисты сталкиваются с ситуацией, когда в результат попадают только те позиции номенклатуры, по которым есть остатки на складах или были совершены продажи. Однако бизнес-требования часто диктуют обратное: пользователь хочет видеть полный список товаров (прайс-лист), где напротив отсутствующих позиций будут стоять нули. Разберем детально, почему так происходит и как изменить запрос, чтобы вывести все 100% элементов справочника.
Основная причина кроется в природе виртуальных таблиц остатков и оборотов. Когда мы выбираем данные, например, из РегистрНакопления.СвободныеОстатки.Остатки, система обращается к физическим таблицам итогов. Если по конкретному товару не было ни одного движения, записи о нем в таблице остатков просто не существует. При использовании обычного соединения (INNER JOIN) или простом объединении данных из нескольких регистров, товары без движений игнорируются.
Рассмотрим самый надежный способ — сделать справочник Номенклатура основным источником данных, к которому мы будем "приклеивать" показатели из регистров. Проанализируем пример кода, который решает эту задачу. Суть метода заключается в том, что мы сначала выбираем все ссылки из справочника, а затем через ЛЕВОЕ СОЕДИНЕНИЕ добавляем к ним данные о заказах и остатках.
Посмотрим на структуру правильного запроса:
ВЫБРАТЬ РАЗРЕШЕННЫЕ
СправочникНоменклатура.Ссылка КАК Номенклатура,
ЕСТЬNULL(НаборДанных.ВНаличии, 0) КАК ВНаличии,
ЕСТЬNULL(НаборДанных.ПриходОсновной, 0) КАК ПриходОсновной,
ЕСТЬNULL(НаборДанных.РасходОсновной, 0) КАК РасходОсновной
ИЗ
Справочник.Номенклатура КАК СправочникНоменклатура
ЛЕВОЕ СОЕДИНЕНИЕ (
// Здесь располагается ваш основной запрос с объединениями регистров
ВЫБРАТЬ
ТаблицаОстатков.Номенклатура КАК Номенклатура,
СУММА(ТаблицаОстатков.КоличествоОстаток) КАК ВНаличии,
0 КАК ПриходОсновной,
0 КАК РасходОсновной
ИЗ
РегистрНакопления.ТоварыНаСкладах.Остатки КАК ТаблицаОстатков
СГРУППИРОВАТЬ ПО
ТаблицаОстатков.Номенклатура
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ТаблицаЗаказов.Номенклатура,
0,
ТаблицаЗаказов.КоличествоОстаток,
0
ИЗ
РегистрНакопления.ЗаказыКлиентов.Остатки КАК ТаблицаЗаказов
) КАК НаборДанных
ПО СправочникНоменклатура.Ссылка = НаборДанных.Номенклатура
ГДЕ
НЕ СправочникНоменклатура.ЭтоГруппа
Важный нюанс: обратите внимание на использование функции ЕСТЬNULL. Если по товару нет данных в правой таблице (регистре), соединение вернет значение NULL. Чтобы в отчете красовались аккуратные нули вместо пустых ячеек, мы заменяем их программно.
Выясним, как реализовать ту же задачу более "чистым" методом с точки зрения архитектуры СКД. Вместо одного огромного запроса с вложенными соединениями мы можем создать два отдельных набора данных.
ВЫБРАТЬ Ссылка КАК Номенклатура ИЗ Справочник.Номенклатура.После создания наборов перейдем на закладку "Связи наборов данных". Выберем НаборДанных_Справочник как источник связи, а НаборДанных_Движения как приемник. В качестве условия связи укажем поле Номенклатура. Главное — не устанавливать галочку "Обязательная связь". В этом случае СКД сама выполнит аналог левого соединения на уровне движка компоновки, и вы получите все элементы справочника в итоговой таблице.
Рассмотрим ситуацию с правами доступа. Использование ВЫБРАТЬ РАЗРЕШЕННЫЕ критически важно. Если в системе настроен RLS (ограничение доступа на уровне записей), то без этого слова запрос может упасть с ошибкой у пользователя с ограниченными правами. Однако помните, что РАЗРЕШЕННЫЕ также является своего рода фильтром: если пользователю запрещено видеть определенную группу товаров, они не попадут в отчет даже при левом соединении.
Проанализируем также совет по поводу синонимов полей. В СКД крайне нежелательно называть поле в запросе так же, как оно называется в измерениях регистров (например, просто Номенклатура), особенно на огромных базах данных (свыше 300 млн записей). Опыт подсказывает, что переименование поля в Товар или СсылкаНоменклатура может существенно ускорить генерацию временных таблиц внутри SQL Server, так как это исключает конфликты имен при построении плана запроса оптимизатором 1С.
Выделим моменты, на которых чаще всего "спотыкаются" разработчики:
ГДЕ НаборДанных.Склад = &Склад после левого соединения, вы фактически превратите его во внутреннее (INNER JOIN). Все товары с нулевыми остатками (у которых склад NULL) отсеются. Условия на склад или период должны быть строго внутри параметров виртуальных таблиц регистров.НЕ Номенклатура.ЭтоГруппа, если ваш отчет плоский, иначе в список попадут и папки, что обычно не требуется для анализа остатков.Номенклатура недостаточно. Вам придется соединяться с таблицей Справочник.ХарактеристикиНоменклатуры (или комбинацией Номенклатура + Характеристика), иначе вы получите одну строку на товар, даже если у него 10 разных размеров/цветов.Подводя итог, можно сказать: чтобы видеть всё, нужно всегда начинать запрос "от объекта", то есть от справочника, и лишь потом аккуратно дополнять его данными из регистров накопления или сведений.