При разработке сложных отчетов на Системе компоновки данных (СКД) часто возникают задачи по реализации нестандартной логики агрегации. Одной из таких задач является вычисление итога по группировке, где из значения первой строки необходимо последовательно вычесть значения всех остальных строк этой же группы. Например, если в группе есть три записи со значениями 100, 15 и 5, итогом должно стать число 80 (100 - 15 - 5).
Стандартные функции агрегации (Сумма, Минимум, Максимум) не позволяют решить эту задачу напрямую. В данной статье мы разберем несколько профессиональных подходов к реализации этой логики: от использования функций 1С:Предприятия для работы с выражениями до манипуляций на уровне текста запроса.
Рассмотрим самый гибкий и «чистый» с точки зрения методологии СКД способ — использование функции ВычислитьВыражение. Эта функция позволяет обращаться к значениям других строк выборки внутри текущей группировки.
Для того чтобы получить разность первой записи и суммы всех последующих, проанализируем следующую формулу в поле ресурса:
ВычислитьВыражение("Сумма(Часов)", , , "Первая", "Первая") - ВычислитьВыражение("Сумма(Часов)", , , "Первая(2)", "Последняя")
Разберем параметры этой функции подробнее:
ВычислитьВыражение("Сумма(Часов)", , , "Первая", "Первая") — здесь мы жестко ограничиваем область расчета только самой первой строкой группировки. Вне зависимости от того, на какой строке находится расчет, это выражение всегда будет возвращать 100 (из нашего примера).ВычислитьВыражение("Сумма(Часов)", , , "Первая(2)", "Последняя") — этот фрагмент вычисляет сумму всех значений, начиная со второй строки (смещение 2 от начала) и до самого конца группировки. В нашем примере это будет 15 + 5 = 20.Важно помнить, что если в группировке всего одна запись, выражение Первая(2) может вернуть NULL. Чтобы отчет не выдавал пустые значения, рекомендуется оборачивать вычисления в функцию ЕстьNULL:
ЕстьNULL(ВычислитьВыражение("Сумма(Поле)", , , "Первая", "Первая"), 0)
- ЕстьNULL(ВычислитьВыражение("Сумма(Поле)", , , "Первая(2)", "Последняя"), 0)
Проанализируем интересную математическую особенность. Если нам нужно из первого элемента (X1) вычесть все остальные (X2, X3... Xn), мы можем выразить это через общую сумму всех элементов группы (S).
Формула выглядит так: X1 - (X2 + X3 + ... + Xn).
Заметим, что S = X1 + X2 + X3 + ... + Xn. Следовательно, (X2 + X3 + ... + Xn) = S - X1.
Подставим это в исходную задачу: X1 - (S - X1) = 2 * X1 - S.
Таким образом, в ресурсах СКД мы можем написать более лаконичное выражение, которое работает быстрее, так как требует меньше обращений к выборке:
2 * ВычислитьВыражение("Сумма(Количество)", , , "Первая", "Первая") - Сумма(Количество)
Преимущество метода: он корректно отрабатывает ситуацию, когда в группе всего одна запись. В этом случае получится 2 * X1 - X1 = X1, что логически верно и обеспечивает быстрый анализ данных.
Иногда проще всего решить задачу на этапе получения данных. Если мы точно знаем, какая запись будет первой (например, используя сравнение строки с представлением ссылки в запросе), мы можем добавить в запрос вычисляемое поле со знаком.
Рассмотрим пример реализации в тексте запроса через использование оконных функций или нумерации строк:
ВЫБРАТЬ
Продажи.Номенклатура КАК Номенклатура,
Продажи.Сумма КАК ИсходнаяСумма,
ВЫБОР
КОГДА Продажи.НомерСтроки = 1 ТОГДА Продажи.Сумма
ИНАЧЕ -Продажи.Сумма
КОНЕЦ КАК СуммаДляИтогов
ИЗ
РегистрНакопления.Продажи КАК Продажи
В СКД мы выводим в детальные записи поле ИсходнаяСумма (чтобы пользователь видел положительные числа), а в ресурсы для итогов группировки добавляем Сумма(СуммаДляИтогов). В результате 100 + (-15) + (-5) даст искомые 80.
Если явного номера строки в данных нет, мы можем получить его, используя прием с АвтономерЗаписи() во временной таблице или через соединение таблицы самой с собой для подсчета количества записей с меньшим внутренним идентификатором.
Если расчет должен производиться на уровне итогов сложной кросс-таблицы или многоуровневой иерархии, полезно использовать функцию ВычислитьВыражениеСГруппировкойМассив. Рассмотрим ситуацию, когда поле Поле1 уже содержит рассчитанную разность для каждой группы.
Чтобы итог по вышестоящей группе (например, по всей организации) не складывал эти разности простым суммированием, а отражал максимальное или специфическое значение, в поле «Выражение» ресурса можно указать:
Максимум(ВычислитьВыражениеСГруппировкойМассив("Поле1"))
Это позволит «пробросить» вычисленное значение с нижнего уровня группировки на верхний без искажений, которые вносит стандартная агрегация Сумма.
Проанализируем ключевые моменты, которые могут повлиять на корректность отчета:
ВычислитьВыражение критически зависит от порядка записей. Если пользователь изменит сортировку в настройках отчета, «первая» запись может физически стать другой. Всегда старайтесь фиксировать порядок в настройках группировки или используйте 7-й параметр функции для указания сортировки внутри выражения."Группировка" (6-й параметр), чтобы СКД понимала масштаб вычислений.Выяснив причину сложности — отсутствие прямого оператора «вычитание последующих» — мы применили комбинацию математических методов и встроенных функций языка выражений СКД. Выбор конкретного метода зависит от производительности системы и того, насколько стабилен порядок строк в вашем отчете.