При разработке отчетов на базе Системы компоновки данных (СКД) программисты часто сталкиваются с неприятной ситуацией: в консоли запросов результат кажется верным, но как только отчет формируется в пользовательском режиме, числовые показатели (ресурсы) начинают необъяснимым образом расти, увеличиваясь в два, три или более раз. Особенно часто это происходит, когда нам необходимо «вытащить» дополнительные данные из табличных частей документов-регистраторов, выполняя ЛЕВОЕ СОЕДИНЕНИЕ виртуальной таблицы регистра с табличной частью документа.
В этой статье мы подробно разберем, почему возникает эффект «размножения» строк, проанализируем механику работы СКД и рассмотрим три надежных способа решения этой задачи, которые помогут получить корректные итоги в любой ситуации.
Прежде чем исправлять код, нам необходимо понять физику процесса. Представим ситуацию: у нас есть запись в регистре накопления ДоходыИРасходы по конкретному документу Выписка на сумму 1000 рублей. В самом документе Выписка в табличной части УслугиБанкаЭквайринг введено две строки (например, оплата услуг двух разных терминалов).
Когда мы выполняем ЛЕВОЕ СОЕДИНЕНИЕ регистра с этой табличной частью по полю Регистратор = Ссылка, СУБД находит для одной строки регистра две подходящие строки из документа. В результате выполнения запроса получается две записи, в каждой из которых фигурирует сумма 1000 рублей. Для консоли запросов это выглядит как две строки, но СКД, видя поле суммы как Ресурс, при расчете общих итогов или группировок просто складывает их, получая 2000 рублей вместо исходной тысячи. Это и есть классическое «задваивание» из-за декартова произведения.
Ситуация осложняется в СКД тем, что если мы добавляем группировку по регистратору, нам может казаться, что всё в порядке (потому что мы видим детальные записи), но на уровне итогов по иерархии ошибка сохраняется. Проанализируем способы, как этого избежать.
Если нам нужно получить из табличной части документа только одно значение (например, подразделение, как в вопросе автора), самым правильным решением будет предварительная «свёртка» табличной части документа до уникальных ссылок. Таким образом, мы гарантируем, что на один регистратор будет приходиться ровно одна строка из документа.
Рассмотрим, как изменить запрос. Вместо прямого соединения с таблицей Документ.Выписка.УслугиБанкаЭквайринг, мы будем использовать вложенный запрос с использованием агрегатной функции МАКСИМУМ или МИНИМУМ.
Пример преобразованного кода запроса:
ВЫБРАТЬ РАЗРЕШЕННЫЕ
ОстаткиИОбороты.ПодразделениеКомпании КАК ПодразделениеОригинал,
ЕСТЬNULL(ВыпискаПодразделения.ПодразделениеСписания, ОстаткиИОбороты.ПодразделениеКомпании) КАК ИтоговоеПодразделение,
ОстаткиИОбороты.ДоходУпрПриход КАК ДоходУпрПриход,
ОстаткиИОбороты.Регистратор КАК Регистратор
ИЗ
РегистрНакопления.ДоходыИРасходы.ОстаткиИОбороты(&ДатаНачала, &ДатаОкончания, Авто, , ) КАК ОстаткиИОбороты
ЛЕВОЕ СОЕДИНЕНИЕ (
ВЫБРАТЬ
Услуги.Ссылка КАК Ссылка,
МАКСИМУМ(Услуги.ПодразделениеСписанияЗатрат) КАК ПодразделениеСписания
ИЗ
Документ.Выписка.УслугиБанкаЭквайринг КАК Услуги
СГРУППИРОВАТЬ ПО
Услуги.Ссылка
) КАК ВыпискаПодразделения
ПО ОстаткиИОбороты.Регистратор = ВыпискаПодразделения.Ссылка
Почему это работает? Благодаря группировке СГРУППИРОВАТЬ ПО Ссылка во вложенном запросе, мы получаем таблицу, где каждому документу соответствует только одна строка. Соединение типа «1 к 1» никогда не вызывает дублирования сумм в основной таблице.
Второй способ — это использование архитектурных возможностей самой СКД. Вместо одного сложного запроса с соединениями (Join), мы создаем два независимых Набора данных - запрос.
После создания наборов переходим на вкладку Связи наборов данных и настраиваем связь:
Важный нюанс: СКД выполняет соединение наборов данных уже после того, как получит результаты из СУБД. При правильной настройке ресурсов, если в НабореДанных2 на один регистратор окажется несколько строк, СКД не будет дублировать значения ресурсов из первого набора. Однако, для корректной работы с измерениями, этот способ требует внимательной настройки ролей полей.
Если бизнес-логика отчета требует вывода всех строк из табличной части документа (например, вы хотите видеть расшифровку по каждой услуге внутри документа), но при этом сумма регистра не должна множиться, можно использовать специализированные функции языка выражений СКД.
Вкладка Ресурсы позволяет задать выражение для расчета итогов. Вместо стандартного Сумма(ДоходУпрПриход) можно использовать функцию ВычислитьВыражениеСГруппировкойТаблица. Рассмотрим пример выражения:
Сумма(ВычислитьВыражениеСГруппировкойТаблица("Максимум(ДоходУпрПриход)", "Регистратор"))
Проанализируем эту конструкцию:
Регистратор.Максимум от суммы (так как сумма во всех дублях одинаковая, максимум вернет исходное значение).При работе с ОстаткиИОбороты в СКД важно не забывать про роли полей на вкладке «Наборы данных». Чтобы остатки считались корректно при наличии соединений, у полей периода (Секунда, День, Месяц) и регистратора должны быть правильно проставлены роли Период с соответствующими номерами (1, 2, 3...).
Также обратим внимание на параметр Метод дополнения в настройках компоновки. Если вы используете левое соединение, и в правой таблице (документе) нет данных, поля документа примут значение NULL. В выражении ВЫБОР КОГДА... обязательно используйте проверку на NULL или функцию ЕСТЬNULL, чтобы избежать пустых ячеек в группировках, которые могут привести к визуальному «разрыву» отчета.
Подводя итог: если вы видите лишние суммы, первым делом проверьте количество строк, возвращаемых запросом для одного регистратора. В 90% случаев использование сгруппированного подзапроса (Решение №1) полностью устраняет проблему и является наиболее производительным с точки зрения нагрузки на сервер базы данных.