Как соединить таблицы из регистров 1С с разным количеством измерений и избежать задвоения показателей

При разработке отчетов и обработок в 1С разработчики часто сталкиваются с задачей сведения данных из двух регистров накопления, имеющих разную степень детализации по измерениям. Рассмотрим типичный пример: первый регистр содержит измерения Номенклатура, Склад, Организация и ресурс Стоимость, а второй — только Номенклатура, Склад и ресурс Количество.

Попытка объединить эти данные с помощью обычного левого или правого соединения приводит к искажению итогов. Разберем по шагам, почему так происходит и как правильно решить эту задачу.

Выясним причину задвоения данных при соединении таблиц

Проанализируем механику работы языка запросов при выполнении соединения ПО Номенклатура И Склад. Если для одной номенклатуры и склада во втором регистре числится остаток в 40 штук, а в первом регистре по этой же позиции есть две записи по разным организациям (например, по Организации 1 — 100 рублей, по Организации 2 — 250 рублей), то СУБД выполняет связь «один ко многим».

В результате реляционного соединения строка с количеством 40 штук дублируется для каждой связанной организации. При последующем применении агрегатной функции СУММА(Количество) платформа складывает 40 и 40, выдавая итоговый результат в 80 штук, что является критической ошибкой.

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

Способ 1. Использование конструкции ОБЪЕДИНИТЬ ВСЕ и группировки

Наиболее эффективным решением в языке запросов 1С является замена горизонтального СОЕДИНЕНИЯ на вертикальное ОБЪЕДИНИТЬ ВСЕ с последующей группировкой результатов во временной таблице или вложенном запросе.

Рассмотрим порядок действий:

1. В первой выборке получаем количественные остатки из регистра с измерениями Номенклатура и Склад. В качестве организации подставляем пустую ссылку ЗНАЧЕНИЕ(Справочник.Организации.ПустаяСсылка), а вместо стоимости — число 0.
2. Во второй выборке получаем стоимостные остатки из регистра с измерениями Номенклатура, Склад и Организация. Вместо количества подставляем 0.
3. Объединяем обе выборки оператором ОБЪЕДИНИТЬ ВСЕ и помещаем во временную таблицу.
4. Во внешнем запросе группируем данные по полям Номенклатура, Склад и Организация, вычисляя функции СУММА(КоличествоОстаток) и СУММА(СтоимостьОстаток).

Посмотрим на пример кода 1С:

ВЫБРАТЬ
	ТоварыНаСкладахОстатки.Номенклатура КАК Номенклатура,
	ТоварыНаСкладахОстатки.Склад КАК Склад,
	ЗНАЧЕНИЕ(Справочник.Организации.ПустаяСсылка) КАК Организация,
	ТоварыНаСкладахОстатки.КоличествоОстаток КАК КоличествоОстаток,
	0 КАК СтоимостьОстаток
ПОМЕСТИТЬ ВТ_Данные
ИЗ
	РегистрНакопления.ТоварыНаСкладах.Остатки(&Период, ) КАК ТоварыНаСкладахОстатки

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
	ПартииТоваровОстатки.Номенклатура,
	ПартииТоваровОстатки.Склад,
	ПартииТоваровОстатки.Организация,
	0,
	ПартииТоваровОстатки.СтоимостьОстаток
ИЗ
	РегистрНакопления.ПартииТоваровНаСкладах.Остатки(&Период, ) КАК ПартииТоваровОстатки
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	ВТ_Данные.Номенклатура КАК Номенклатура,
	ВТ_Данные.Склад КАК Склад,
	ВТ_Данные.Организация КАК Организация,
	СУММА(ВТ_Данные.КоличествоОстаток) КАК КоличествоОстаток,
	СУММА(ВТ_Данные.СтоимостьОстаток) КАК СтоимостьОстаток
ИЗ
	ВТ_Данные КАК ВТ_Данные
СГРУППИРОВАТЬ ПО
	ВТ_Данные.Номенклатура,
	ВТ_Данные.Склад,
	ВТ_Данные.Организация

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

Способ 2. Настройка связей наборов данных в СКД

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

Разберем по шагам настройку СКД:

1. В схеме компоновки данных создаем два отдельных набора данных типа Запрос.
2. В первом наборе получаем данные по количеству (из измерения Номенклатура и Склад).
3. Во втором наборе получаем данные по стоимости с измерением Организация.
4. На вкладке Связи наборов данных добавляем связь от первого набора к второму по полям Номенклатура и Склад.
5. На вкладке Ресурсы указываем функции расчета итогов СУММА(КоличествоОстаток) и СУММА(СтоимостьОстаток).

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