Разберем типичную задачу разработки отчетов в системе компоновки данных (СКД). Предположим, что строится иерархическая структура группировок: Клиент → Накладная → Товар. В отчете присутствует числовой показатель Оплата, который относится непосредственно к документу (шапке накладной), а не к строкам его табличной части.
При формировании такого отчета часто требуется получить следующие результаты:
Если попытаться решить эту задачу простым указанием выражений на вкладке ресурсов СКД (например, задать 0 для группировки Товар и Сумма(Оплата) для группировки Клиент), отчет посчитает итоги неверно: на уровне клиента сумма оплаты многократно увеличится. Выясним причину такого поведения компоновки данных и рассмотрим надежные способы решения проблемы.
Проанализируем исходный запрос в наборе данных отчета. Как правило, документ связывается со своей табличной частью через соединение:
ВЫБРАТЬ
РеализацияТоваровУслуг.Ссылка КАК Накладная,
РеализацияТоваровУслуг.Партнер КАК Клиент,
РеализацияТоваровУслуг.СуммаПоступления КАК Оплата,
РеализацияТовары.Номенклатура КАК Товар
ИЗ
Документ.РеализацияТоваровУслуг КАК РеализацияТоваровУслуг
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.РеализацияТоваровУслуг.Товары КАК РеализацияТовары
ПО РеализацияТовары.Ссылка = РеализацияТоваровУслуг.Ссылка
В результате такого соединения строка документа дублируется столько раз, сколько позиций номенклатуры содержится в его табличной части. Поле Оплата попадает в каждую полученную детальную запись набора данных.
Механизм расчета ресурсов в СКД вычисляет агрегатные функции (такие как Сумма) не «снизу вверх» по уже рассчитанным строкам вышестоящих группировок, а напрямую по исходным детальным записям выборки, входящим в текущую ветвь. Указание выражения 0 в поле «Рассчитывать по...» для группировки Товар лишь переопределяет вывод в строках товаров, но не удаляет и не обнуляет сами детальные записи в памяти СКД. Когда компоновщик считает Сумма(Оплата) для группировки Клиент, он суммирует поле Оплата по всем строкам табличных частей накладных, что приводит к многократному завышению итогов.
Наиболее простое и быстрое решение, не требующее переписывания запроса, заключается в правильной настройке вкладки Ресурсы с помощью функции языка выражений СКД ВычислитьВыражениеСГруппировкойМассив.
Разберем по шагам, как настроить ресурсы:
Оплата. В поле «Рассчитывать по...» выберем группировку Товар, а в колонке «Выражение» укажем константу: 0.Оплата повторно для вышестоящих группировок. В колонке «Рассчитывать по...» укажем группировки Накладная, Клиент и Общий итог.Сумма(ВычислитьВыражениеСГруппировкойМассив("Максимум(Оплата)", "Накладная"))
Рассмотрим подробнее работу этой формулы. Функция ВычислитьВыражениеСГруппировкойМассив формирует массив, элементами которого являются результаты вычисления первого выражения (в нашем случае берется максимальное значение оплаты по накладной, чтобы отсечь дубли) для каждого уникального значения поля группировки, указанного во втором параметре ("Накладная"). Внешняя функция Сумма складывает полученные элементы массива. В результате на уровне клиента и в общем итоге суммируются именно документы, а не строки их табличных частей.
Обратим внимание на важный нюанс: во втором параметре функции указывается имя поля группировки из схемы, а не имя элемента структуры отчета. В первом параметре рекомендуется явно применять агрегатную функцию (например, Максимум или Среднее), чтобы гарантировать корректное свертывание значения в контексте группировки.
Если отчет строится по огромному массиву данных (сотни тысяч строк), обработка массивов выражениями СКД в оперативной памяти может снизить скорость формирования отчета. В таких ситуациях эффективнее перенести логику исключения дублей на уровень СУБД в текст запроса.
Рассмотрим вариант с использованием оконных функций. Сделаем так, чтобы ресурс Оплата заполнялся только для первой строки табличной части документа, а во всех остальных строках принимал значение 0:
ВЫБРАТЬ
РеализацияТоваровУслуг.Ссылка КАК Накладная,
РеализацияТоваровУслуг.Партнер КАК Клиент,
ВЫБОР
КОГДА ROW_NUMBER() OVER (
РАЗДЕЛИТЬ ПО РеализацияТовары.Ссылка
УПОРЯДОЧИТЬ ПО РеализацияТовары.НомерСтроки
) = 1
ТОГДА РеализацияТоваровУслуг.СуммаПоступления
ИНАЧЕ 0
КОНЕЦ КАК Оплата,
РеализацияТовары.Номенклатура КАК Товар
ИЗ
Документ.РеализацияТоваровУслуг КАК РеализацияТоваровУслуг
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.РеализацияТоваровУслуг.Товары КАК РеализацияТовары
ПО РеализацияТовары.Ссылка = РеализацияТоваровУслуг.Ссылка
При таком подходе на вкладке ресурсов СКД для поля Оплата достаточно указать стандартное выражение Сумма(Оплата) для всех группировок без исключения. Итоги по накладным, клиентам и общему отчету сойдутся автоматически.
Если используемая конфигурация или режим совместимости не поддерживают оконные функции, можно применить распределение суммы шапки делением на общее количество строк табличной части документа:
РеализацияТоваровУслуг.СуммаПоступления / РеализацияТовары.Ссылка.Товары.Количество() КАК Оплата
В таком случае сумма дробных частей по строкам документа в итоге составит точную сумму шапки, и стандартная функция Сумма(Оплата) в СКД выдаст правильный результат на любом уровне иерархии.
Еще один архитектурно правильный способ обойти проблему дублирования данных шапки — разделить получение данных на два отдельных набора данных типа Запрос:
Ссылка (Накладная), Партнер (Клиент) и СуммаПоступления (Оплата) непосредственно из шапки документов без обращения к табличным частям.Ссылка и Номенклатура (Товар) из табличной части документа.Ссылка.В такой структуре поле Оплата присутствует только в главном наборе. СКД агрегирует ресурс независимо от подчиненных строк товаров, благодаря чему суммирование работает корректно со стандартной формулой Сумма(Оплата).