Как учесть документы и вывести нулевые значения по отсутствующей номенклатуре в кросс-таблице СКД?

← На главную

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

Выясним причину такого поведения платформы и разберем практические способы решения этой задачи без искусственного изменения данных в базе.

Почему возникает проблема в СКД

В СКД ячейка на пересечении строки (Документ/Контрагент) и колонки (Номенклатура) отображается визуально в структуре кросс-таблицы, но вычисление ресурсов происходит исключительно по тем строкам выборки набора данных, которые соответствуют текущему контексту группировок. Если в табличной части документа строки с конкретным товаром нет, в наборе данных отсутствует сама запись с такой комбинацией измерений. В результате ресурс для данной ячейки не равен нулю или NULL — он просто не вычисляется, а агрегатная функция Количество(Различные Документ) не учитывает документ в этой колонке.

Способ 1. Формирование матрицы (декартова произведения) в запросе

Рассмотрим основной и самый надежный способ — дополнение выборки недостающими строками на уровне запроса. Для этого необходимо создать полную координатную сетку «Документ — Номенклатура», к которой затем левым соединением подтягиваются фактические данные из табличной части.

Разберем по шагам логику построения такого запроса:

Шаг 1. Выбираем уникальные документы за период со всеми необходимыми шапочными реквизитами во временную таблицу ВТ_Документы.
Шаг 2. Формируем список номенклатуры (все элементы справочника или только нужные) во временную таблицу ВТ_Номенклатура.
Шаг 3. Выполняем декартово произведение таблиц документов и номенклатуры без условий соединения, получая гарантированные строки под каждую колонку отчета.
Шаг 4. Присоединяем через ЛЕВОЕ СОЕДИНЕНИЕ реальные строки табличной части документа по двум полям: по ссылке на документ и по номенклатуре.
Шаг 5. Оборачиваем количество в функцию ЕСТЬNULL(..., 0).

Посмотрим на пример реализации запроса:


ВЫБРАТЬ РАЗЛИЧНЫЕ
	ПосещениеМагазина.Ссылка КАК Документ,
	ПосещениеМагазина.Адрес КАК Адрес,
	ПосещениеМагазина.Контрагент КАК Контрагент,
	ПосещениеМагазина.Контрагент.СборныйПоставщик КАК Поставщик
ПОМЕСТИТЬ ВТ_Документы
ИЗ
	Документ.ПосещениеМагазина КАК ПосещениеМагазина
ГДЕ
	ПосещениеМагазина.Дата МЕЖДУ &Дата1 И &Дата2
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	Номенклатура.Ссылка КАК Номенклатура
ПОМЕСТИТЬ ВТ_Номенклатура
ИЗ
	Справочник.Номенклатура КАК Номенклатура
ГДЕ
	НЕ Номенклатура.ЭтоГруппа
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	ВТ_Документы.Документ КАК Документ,
	ВТ_Документы.Адрес КАК Адрес,
	ВТ_Документы.Контрагент КАК Контрагент,
	ВТ_Документы.Поставщик КАК Поставщик,
	ВТ_Номенклатура.Номенклатура КАК Номенклатура,
	ЕСТЬNULL(ПосещениеМагазинаТовары.Количество, 0) КАК Количество,
	1 КАК КоличествоДокументов
ИЗ
	ВТ_Документы КАК ВТ_Документы
		КРОСС СОЕДИНЕНИЕ ВТ_Номенклатура КАК ВТ_Номенклатура
		ЛЕВОЕ СОЕДИНЕНИЕ Документ.ПосещениеМагазина.Товары КАК ПосещениеМагазинаТовары
		ПО (ПосещениеМагазинаТовары.Ссылка = ВТ_Документы.Документ)
			И (ПосещениеМагазинаТовары.Номенклатура = ВТ_Номенклатура.Номенклатура)

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

Способ 2. Использование двух наборов данных и связей между ними в СКД

Проанализируем альтернативный вариант без ручного написания полного декартова соединения — разделение данных на два независимых набора в схеме компоновки:

В первом наборе данных (НаборДанных_Документы) выбираем данные шапки: Ссылка КАК Документ, Контрагент, Адрес и поле 1 КАК КоличествоДокументов.
Во втором наборе данных (НаборДанных_Товары) выбираем данные табличной части: Ссылка КАК Документ, Номенклатура, Количество.
На вкладке Связи наборов данных настраиваем связь от источника НаборДанных_Документы к приемнику НаборДанных_Товары по полю Документ = Документ.

При такой структуре СКД сохраняет контекст документа верхнего уровня даже при пустых подчиненных записях по конкретным позициям номенклатуры.

Способ 3. Вычисление ресурсов на уровне родительских группировок

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

На вкладке Ресурсы для поля КоличествоДокументов настраивается формула с указанием контекста группировки в поле Рассчитывать по...:

ВычислитьВыражение("Количество(Различные Документ)", "Контрагент")

Если же необходимо получить общее число документов по всему отчету без учета текущей колонки, вторым параметром передается пустая строка:

ВычислитьВыражение("Количество(Различные Документ)", "")

Такой подход позволяет корректно отображать агрегированные показатели шапки документа в кросс-таблицах без дублирования строк в базе данных.