Как в отчете СКД рассчитать итоги по группировкам без задвоения сумм из-за детальных записей?

← На главную

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

Проанализируем природу этой проблемы и разберем проверенные способы корректного расчета итогов в СКД.

Почему происходит задвоение и почему не помогают простые настройки?

Когда мы соединяем в одном SQL-запросе таблицу документов планирования с таблицей оплат через ЛЕВОЕ СОЕДИНЕНИЕ, СУБД возвращает столько строк, сколько найдено платежек. Значение поля План дублируется в каждой строке результирующего набора данных.

Посмотрим, почему стандартные попытки скрыть поле не решают проблему математики:

Условное оформление: скрытие текста в детальных записях (установка пустого текста) лишь убирает видимость цифр в ячейках отчета. СКД продолжает суммировать значения на уровне выборки снизу вверх, поэтому общие итоги остаются неверными.
Колонка «Рассчитывать по...» на вкладке «Ресурсы»: ограничение расчета только определенной группировкой отключает вывод ресурса для других уровней, но внутри самой группировки СКД все равно просуммирует все строки набора данных, принадлежащие этой ветке.
Удаление поля из выбранных полей детальных записей: исключение ресурса из состава полей детальной группировки также не меняет способ расчета итогов в группировках-родителях.

Для получения правильных итогов необходимо либо изменить формулу вычисления ресурса на вкладке Ресурсы, либо скорректировать архитектуру наборов данных в схеме компоновки.

Способ 1. Использование функции «ВычислитьВыражениеСГруппировкойМассив»

Рассмотрим наиболее гибкий способ настройки ресурсов, который не требует изменения текста исходных запросов. СКД предоставляет специальные функции агрегации массивов для расчета по схеме «сумма максимумов».

Разберем по шагам настройку вкладки Ресурсы:

Переходим на вкладку Ресурсы и добавляем resource-поле План.
В первой строке настраиваем расчет для уровня самого документа:
В поле Выражение указываем:
Максимум(План)
В колонке Рассчитывать по... выбираем группировку: ДокументПланирования.
Добавляем вторую строку для того же поля План для расчета вышестоящих уровней и общих итогов:
В поле Выражение указываем:
Сумма(ВычислитьВыражениеСГруппировкойМассив("Максимум(План)", "ДокументПланирования, Период"))
Колонку Рассчитывать по... оставляем пустой (тогда формула применится ко всем остальным группировкам и общим итогам отчета).

Важный нюанс для кросс-таблиц: если отчет строится в виде таблицы, где есть группировки и по строкам (например, ДокументПланирования), и по колонкам (например, Период), во втором параметре функции ВычислитьВыражениеСГруппировкойМассив обязательно перечисляются группировки обеих осей через запятую. Если опустить измерение колонок, горизонтальные итоги могут быть рассчитаны некорректно.

Способ 2. Разделение на два набора данных в СКД

Другой надежный архитектурный подход — отказаться от объединения данных в SQL-запросе и доверить связывание платформе 1С через механизм связей наборов данных СКД.

Разберем процесс настройки:

НаборДанных1 (План): запрос выбирает данные только по плановым документам (ссылка на документ, период, сумма плана).
НаборДанных2 (Факт): запрос выбирает детальные записи платежей (ссылка на документ планирования, платежка, сумма платежа).
На вкладке Связи наборов данных создаем связь:
Источник связи: НаборДанных1

Приемник связи: НаборДанных2

Условие связи: ДокументПланирования = ДокументПланирования

Параметры: передаем ключевые поля связи.
На вкладке Ресурсы для поля План используется стандартная формула: Сумма(План)

При такой схеме СКД вычисляет итоги по каждому набору данных независимо, исключая проблему задвоения сумм первого набора при наличии множества строк во втором.

Способ 3. Объединение запросов («ОБЪЕДИНИТЬ ВСЕ») вместо соединения

Если требуется оставить один набор данных, можно переписать текст запроса так, чтобы строка с планом попадала в выборку только один раз, без размножения по строкам оплат.

Посмотрим на пример структуры такого запроса:


ВЫБРАТЬ
    ДокументПланирования КАК ДокументПланирования,
    Период КАК Период,
    НЕОПРЕДЕЛЕНО КАК ПлатежныйДокумент,
    СуммаПлана КАК План,
    0 КАК Факт
ИЗ
    Документ.ПланПлатежей КАК План

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
    Платеж.ДокументПланирования,
    Платеж.Период,
    Платеж.Ссылка,
    0,
    Платеж.СуммаОплаты
ИЗ
    Документ.ПлатежноеПоручение КАК Платеж

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

Способ 4. Пропорциональное разделение суммы в запросе

В некоторых случаях (когда нельзя переписать структуру наборов данных и сложно настроить формулы СКД) разработчики прибегают к приему распределения суммы плана на количество строк детальных записей прямо в тексте запроса:


ВЫБРАТЬ
    Платежи.ДокументПланирования КАК ДокументПланирования,
    Платежи.Ссылка КАК Платежка,
    Планы.СуммаПлана / КоличествоСтрок.ВсегоСтрок КАК План,
    Платежи.Сумма КАК Факт
ИЗ
    ТаблицаПлатежей КАК Платежи
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ ТаблицаПланов КАК Планы 
        ПО Платежи.ДокументПланирования = Планы.Ссылка
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ (
        ВЫБРАТЬ ДокументПланирования, КОЛИЧЕСТВО(*) КАК ВсегоСтрок
        ИЗ ТаблицаПлатежей ГРУППИРОВАТЬ ПО ДокументПланирования
    ) КАК КоличествоСтрок 
        ПО Платежи.ДокументПланирования = КоличествоСтрок.ДокументПланирования

В этом варианте стандартная сумма Сумма(План) при сворачивании строк на уровне группировки вернет исходное значение. Однако следует помнить, что из-за погрешностей деления с плавающей точкой на больших объемах данных могут возникать расхождения в копейках, поэтому предпочтительнее использовать Способ 1 или Способ 2.