Как в СКД исправить задвоение сумм бюджета при соединении с детальными документами?

← На главную

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

Разберем подробно причины возникновения этой проблемы и рассмотрим практические варианты её решения средствами СКД без изменения структуры базы данных.

Причина возникновения проблемы в запросе

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


ВЫБРАТЬ
	ОперацииПоРасчетномуСчетуСтрокиДокумента.ВидДвижения,
	ОперацииПоРасчетномуСчетуСтрокиДокумента.Объект КАК Аналитика,
	ОперацииПоРасчетномуСчетуСтрокиДокумента.Комментарий,
	ОперацииПоРасчетномуСчетуСтрокиДокумента.Ссылка КАК Документ,
	ОперацииПоРасчетномуСчетуСтрокиДокумента.Приход,
	ОперацииПоРасчетномуСчетуСтрокиДокумента.Расход
ПОМЕСТИТЬ Док
ИЗ
	Документ.ОперацииПоРасчетномуСчету.СтрокиДокумента КАК ОперацииПоРасчетномуСчетуСтрокиДокумента

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

ВЫБРАТЬ
	ОперацииПоКассеСтрокиДокумента.ВидДвижения,
	ОперацииПоКассеСтрокиДокумента.Объект,
	ОперацииПоКассеСтрокиДокумента.Комментарий,
	ОперацииПоКассеСтрокиДокумента.Ссылка,
	ОперацииПоКассеСтрокиДокумента.ПриходВалюта1,
	ОперацииПоКассеСтрокиДокумента.РасходВалюта1
ИЗ
	Документ.ОперацииПоКассе.СтрокиДокумента КАК ОперацииПоКассеСтрокиДокумента
;

ВЫБРАТЬ
	Док.ВидДвижения,
	Док.Аналитика,
	Док.Комментарий,
	Док.Документ,
	СУММА(Док.Приход) КАК Приход,
	СУММА(Док.Расход) КАК Расход,
	БюджетДоходовРасходовОбороты.СуммаОборот - СУММА(Док.Приход) - СУММА(Док.Расход) КАК ОстатокБюджета
ИЗ
	Док КАК Док
		ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.БюджетДоходовРасходов.Обороты КАК БюджетДоходовРасходовОбороты
		ПО Док.Аналитика = БюджетДоходовРасходовОбороты.Аналитика
СГРУППИРОВАТЬ ПО
	Док.Аналитика,
	Док.ВидДвижения,
	Док.Комментарий,
	Док.Документ,
	БюджетДоходовРасходовОбороты.СуммаОборот

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

Способ 1. Настройка агрегации ресурса по конкретным группировкам

Рассмотрим первый способ решения проблемы. Чтобы исключить суммирование бюджета на уровне детальных записей, настроим расчет ресурсов в СКД:

Перейдем на закладку Ресурсы в схеме компоновки данных.
Для поля бюджета установим выражение без суммирования детализации — например, Максимум(СуммаОборот) или Бюджет.
В колонке Рассчитывать по... укажем только верхнеуровневую группировку Аналитика и Общий итог.
Для группировки Документ поле бюджета из выбранных полей выводить не будем или заблокируем его расчет на этом уровне.

Таким образом, на уровне каждого документа бюджет выводиться не будет, а на уровне группировки Аналитика возьмется одно максимизированное или среднее значение планового показателя, что полностью уберет дублирование.

Способ 2. Вынос расчета остатка бюджета в вычисляемые поля

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

Перенесем поле ОстатокБюджета из запроса на закладку Вычисляемые поля.
В колонке Выражение пропишем формулу: СуммаОборот - СУММА(Приход) - СУММА(Расход)
В настройках структуры отчета для группировки Документ укажем явный состав полей вместо автополей.

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

Способ 3. Использование функции СКД ВычислитьВыражениеСГруппировкойМассив

Рассмотрим продвинутое решение с использованием специализированных функций СКД. На закладке Ресурсы для расчета итогов по плановым данным можно применить функцию ВычислитьВыражениеСГруппировкойМассив.

Пример выражения для ресурса бюджета:


Сумма(ВычислитьВыражениеСГруппировкойМассив("Максимум(СуммаОборот)", "Аналитика"))

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

Альтернативный подход: Разделение наборов данных

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

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

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