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