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