Как объединить дублирующиеся строки и правильно рассчитать итоги в отчете СКД?

← На главную

При разработке отчетов на Системе компоновки данных (СКД) начинающие разработчики часто сталкиваются с ситуацией, когда данные в отчете начинают «раздваиваться» или «расстраиваться». Типичный пример: нам нужно вывести список аукционов или заказов, а рядом — сумму оплат по ним. Поскольку по одному заказу может быть несколько платежных документов, использование простого ЛЕВОЕ СОЕДИНЕНИЕ приводит к тому, что строка основного документа дублируется столько раз, сколько было платежей. В результате итоги по суммам заказов считаются неверно, а визуально отчет становится трудночитаемым.

В этой статье мы разберем, как правильно спроектировать запрос и настроить СКД, чтобы строки «схлопывались», а суммы и задолженность рассчитывались корректно. Мы проанализируем ситуацию на примере конфигурации «Управление торговлей 11».

Причина возникновения проблемы: избыточность данных

Рассмотрим ситуацию. У нас есть документ Аукцион с суммой 400 рублей. По нему прошло две оплаты: 100 и 300 рублей. Если мы просто соединим эти таблицы в запросе, СУБД вернет две строки:

Аукцион 1 (400 руб.) — Оплата 1 (100 руб.)
Аукцион 1 (400 руб.) — Оплата 2 (300 руб.)

В итоге СКД просуммирует колонку «Выиграно» и покажет 800 рублей вместо 400. Чтобы этого избежать, нам нужно применить один из подходов, описанных ниже.

Способ 1. Агрегация данных на уровне вложенного запроса

Проанализируем наиболее правильный с точки зрения производительности метод — подготовку данных во вложенном запросе. Вместо того чтобы соединять основную таблицу с «сырыми» данными о платежах, мы сначала сгруппируем платежи по объекту-основанию.

Посмотрим, как должен выглядеть фрагмент запроса для получения оплат:


ЛЕВОЕ СОЕДИНЕНИЕ (
    ВЫБРАТЬ
        РасшифровкаПлатежа.ОснованиеПлатежа.Конкурс КАК Конкурс,
        СУММА(РасшифровкаПлатежа.Сумма) КАК СуммаОплаты
    ИЗ
        Документ.ПоступлениеБезналичныхДенежныхСредств.РасшифровкаПлатежа КАК РасшифровкаПлатежа
    ГДЕ
        РасшифровкаПлатежа.Ссылка.Проведен = ИСТИНА
    СГРУППИРОВАТЬ ПО
        РасшифровкаПлатежа.ОснованиеПлатежа.Конкурс
) КАК Поступление
ПО Аукцион.Ссылка = Поступление.Конкурс

Важный момент: обратите внимание, что во вложенном запросе мы исключили поле ПлатежныйДокумент (ссылку на саму оплату). Если оставить ссылку на документ во вложенном запросе и включить её в СГРУППИРОВАТЬ ПО, то строки все равно будут двоиться. Мы должны группировать строго по тому полю, по которому происходит связь с основной таблицей (в данном примере — Конкурс или Аукцион).

Способ 2. Настройка ресурсов в СКД

Если специфика отчета требует наличия детальных записей (например, вы хотите видеть список платежек при расшифровке), но в основных группировках данные должны суммироваться корректно, нам помогут настройки на закладке «Ресурсы».

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

Для поля Оплата оставляем выражение СУММА(Оплата).
Для поля СуммаВыигранная (которая дублируется) используем выражение МАКСИМУМ(СуммаВыигранная) в контексте группировки по Аукциону.
Для правильного расчета общего итога по всем аукционам можно использовать более сложную конструкцию: Сумма(ВычислитьВыражениеСГруппировкойМассив("Максимум(СуммаВыигранная)", "Аукцион")).

Это заставит систему сначала найти максимальную сумму внутри каждого аукциона (что исключит влияние дублей), а затем сложить эти максимумы для получения общего итога.

Способ 3. Использование ролей полей

Проанализируем еще один мощный инструмент СКД — Роли. В схеме компоновки данных на закладке «Наборы данных» у каждого поля есть колонка «Роль». Если мы укажем для поля Аукцион, что это «Обязательное» поле и ключ, а для числового поля СуммаВыигранная настроим игнорирование дублей, СКД сможет самостоятельно обработать ситуацию с LEFT JOIN.

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

Разбор типовой ошибки в группировке

Рассмотрим пример кода, который часто приводит к сохранению нескольких строк, даже если суммы считаются верно:


ВЫБРАТЬ
    Аукцион.Ссылка КАК Аукцион,
    Поступление.ПлатежныйДокумент, -- ВОТ ОШИБКА
    СУММА(ЕСТЬNULL(Поступление.Сумма, 0)) КАК Оплата
...
СГРУППИРОВАТЬ ПО
    Аукцион.Ссылка,
    Поступление.ПлатежныйДокумент -- Поле заставляет отчет выводить по строке на каждый платеж

Чтобы схлопнуть отчет до одной строки по аукциону, мы должны полностью удалить поле ПлатежныйДокумент из списка выбора ВЫБРАТЬ и из секции СГРУППИРОВАТЬ ПО. Если нам все же нужно видеть, из чего сложилась оплата, лучше использовать механизм иерархических группировок в настройках отчета (вкладка «Настройки»), добавив детальные записи подчиненным уровнем к группировке по Аукциону.

Итоговый алгоритм действий для исправления отчета:

Проверяем вложенный запрос: убеждаемся, что в нем нет «лишних» полей, которые мешают группировке. Оставляем только идентификатор связи и агрегированную сумму.
Обрабатываем NULL: всегда используем ЕСТЬNULL(Поступление.Сумма, 0), так как арифметические операции с NULL (например, при расчете долга) вернут NULL, и данные пропадут из отчета.
Настраиваем ресурсы: для всех суммовых полей, которые мы хотим видеть в итогах, прописываем правила агрегации на соответствующей вкладке СКД.
Проверяем структуру отчета: в настройках компоновки (нижнее окно конструктора) убеждаемся, что группировка идет по объекту Аукцион, а не по детальным записям платежей.

Соблюдение этих правил гарантирует, что ваш отчет будет выдавать корректную информацию: один аукцион — одна строка, с правильной суммой всех поступлений и точно рассчитанным остатком долга.