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