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

← На главную

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

В этой статье мы подробно и по шагам разберем, почему возникает эта проблема, и рассмотрим несколько эффективных способов ее решения, предложенных практикующими разработчиками.

Выясняем причину проблемы задвоения сумм

Давайте проанализируем стандартную ситуацию. Допустим, у нас есть документ, в шапке которого указана общая СуммаДокумента (например, 10 000 рублей), а в табличной части содержится 5 строк с товарами. Если мы напишем простой запрос, соединяющий шапку документа с его табличной частью по ссылке, СУБД вернет нам 5 строк. В каждой из этих 5 строк в поле СуммаДокумента будет стоять значение 10 000 рублей.

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

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

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

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

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

Разберем по шагам, как настроить это решение:

Откроем схему компоновки данных и перейдем на вкладку Ресурсы.
Найдем наше проблемное поле (например, СуммаДокумента) в списке ресурсов.
В колонке Выражение вместо стандартного Сумма(СуммаДокумента) запишем специальную формулу:


Сумма(ВычислитьВыражениеСГруппировкойМассив("Максимум(СуммаДокумента)", "Документ"))

Проанализируем, как работает эта конструкция изнутри:

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

Важный нюанс настройки: Чтобы данная формула работала корректно, в колонке Рассчитывать по... таблицы ресурсов необходимо явно указать только те группировки, которые находятся выше уровня самого документа (например, Контрагент, Организация или ОбщийИтог). Для уровня детальных записей или самой группировки Документ рассчитывать этот ресурс по такой сложной формуле не нужно — там достаточно выводить простое значение поля.

Решение 2. Разделение данных на два набора (подход автора темы)

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

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

Рассмотрим этот процесс подробнее:

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

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

Анализ производительности метода: Этот способ очень прост в настройке и интуитивно понятен. Однако у него есть существенный минус. При связывании наборов данных СКД может выполнять запросы к СУБД в цикле (так называемая проблема N+1 запросов). Для каждого документа из первого набора система может делать отдельный запрос ко второму набору. На небольших базах и отчетах за короткий период это незаметно, но при формировании отчета по десяткам тысяч документов скорость работы может серьезно упасть. Поэтому используйте этот метод с осторожностью на высоконагруженных системах.

Решение 3. Оптимизация на уровне SQL-запроса через временные таблицы и ОБЪЕДИНИТЬ ВСЕ

Если вам важна максимальная производительность отчета и при этом вы хотите избежать задвоения итогов, лучшим решением будет оптимизация исходного текста запроса в первом наборе данных. Мы можем уйти от соединения таблиц типа ЛЕВОЕ СОЕДИНЕНИЕ (LEFT JOIN) и заменить его на операцию объединения.

Разберем, как построить такой запрос по шагам:

В первом пакете запроса выбираем уникальные данные по документам и их суммам во временную таблицу:


ВЫБРАТЬ
    Ссылка КАК Документ,
    СуммаДокумента КАК Сумма
ПОМЕСТИТЬ ВТ_СуммыДокументов
ИЗ
    Документ.РеализацияТоваровУслуг
ГДЕ
    Ссылка В (&ПериодДокументов)
;

Во втором пакете запроса выбираем детальные записи табличной части во вторую временную таблицу:


ВЫБРАТЬ
    Ссылка КАК Документ,
    Номенклатура,
    Количество,
    Сумма КАК СуммаСтроки
ПОМЕСТИТЬ ВТ_ДетальныеЗаписи
ИЗ
    Документ.РеализацияТоваровУслуг.Товары
ГДЕ
    Ссылка В (&ПериодДокументов)
;

В финальном запросе объединяем эти данные через оператор ОБЪЕДИНИТЬ ВСЕ, разделяя показатели по разным колонкам:


ВЫБРАТЬ
    Документ,
    NULL КАК Номенклатура,
    0 КАК Количество,
    0 КАК СуммаСтроки,
    Сумма КАК СуммаДокументаДляИтогов
ИЗ
    ВТ_СуммыДокументов

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

ВЫБРАТЬ
    Документ,
    Номенклатура,
    Количество,
    СуммаСтроки,
    0 КАК СуммаДокументаДляИтогов
ИЗ
    ВТ_ДетальныеЗаписи

Проанализируем структуру полученного результата. В итоговой выборке строки с суммами документов будут идти отдельно (поля номенклатуры и количества в них будут заполнены значениями NULL или 0), а детальные строки товаров будут идти отдельно (поле СуммаДокументаДляИтогов в них будет равно 0). При группировке по документу в СКД все эти строки схлопнутся. В итог по документу попадет ровно одна правильная сумма из шапки, а детальные строки номенклатуры выведутся ниже и не окажут никакого влияния на расчет общих сумм. Этот метод работает быстрее всего на стороне СУБД.

Решение 4. Использование вычисляемого поля для скрытия дублей в детальных записях

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

Для реализации этого интерфейсного решения выполним следующие шаги:

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

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

Резюме

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

Если вам нужно быстро решить задачу без изменения структуры запросов — используйте формулу с функцией ВычислитьВыражениеСГруппировкойМассив в ресурсах.
Если вам проще проектировать схему визуально и объем данных невелик — разделите выборку на два набора данных и свяжите их по ключевым полям.
Если отчет строится по огромным массивам информации и важна максимальная производительность — оптимизируйте исходный SQL-запрос через временные таблицы и объединения.
Если требуется скрыть дублирующиеся данные в строках товаров для улучшения читаемости отчета — примените технику с пустым вычисляемым полем и выборочным расчетом ресурсов.