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

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

Рассмотрим ситуацию, когда требуется, чтобы в итоговой сумме ресурс учитывался по одному правилу, а при выполнении определенного условия в другом столбце — по другому. Например, если Столбец1 = "Х", то итоговая сумма должна быть Значение2 + Значение3, а в противном случае (Столбец1 <> "Х") — Значение1 + Значение2 + Значение3. Главная сложность состоит в том, что необходимо не просто отфильтровать строки, а выполнить условный расчет именно для итоговой суммы, сохраняя при этом все строки в детализации.

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

Решение 1: Условное суммирование с помощью конструкции СУММА(ВЫБОР КОГДА ... КОНЕЦ)

Один из наиболее прямых и часто используемых способов реализации условных итогов в СКД — это применение конструкции ВЫБОР КОГДА непосредственно внутри агрегатной функции СУММА(). Этот метод позволяет нам определить, какое значение будет участвовать в суммировании для каждой отдельной записи набора данных.

Как это работает:

Функция ВЫБОР КОГДА выполняется для каждой детальной строки набора данных. На основе заданного условия она возвращает определенное значение: либо исходное значение ресурса, либо ноль (если мы хотим исключить его из суммы), либо какое-либо другое вычисленное значение. После этого агрегатная функция СУММА применяет операцию сложения к этим уже условно обработанным значениям.

Предположим, у нас есть ресурс ОтработаноЧасов, и мы хотим исключить его из общего итога, если поле ТехнологическаяОперация.Наименование равно "Актированный день". Вот как это можно реализовать в выражении ресурса на закладке "Ресурсы" схемы компоновки данных:

СУММА(ВЫБОР
    КОГДА ТехнологическаяОперация.Наименование = "Актированный день" ТОГДА 0
    ИНАЧЕ ОтработаноЧасов
КОНЕЦ )

Пояснение к примеру:

1. Для каждой строки данных СКД проверяет условие ТехнологическаяОперация.Наименование = "Актированный день".
2. Если условие истинно, то ВЫБОР КОГДА вернет 0. Это означает, что для данной строки в суммирование будет передан ноль, фактически обнуляя вклад ОтработаноЧасов этой строки в общий итог.
3. Если условие ложно, ВЫБОР КОГДА вернет исходное значение ОтработаноЧасов, которое и будет участвовать в суммировании.
4. Вся конструкция СУММА(...) затем просуммирует эти условно обработанные значения для всех строк, формируя итоговую сумму с учетом наших правил.

Этот метод позволяет эффективно управлять вкладом каждой записи в итоговую сумму, не удаляя сами записи из отчета.

Решение 2: Использование вычисляемых полей

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

Как это работает:

1. Создаем вычисляемое поле: Перейдите на закладку "Вычисляемые поля" в схеме компоновки данных. Создайте новое поле, например, назовите его ОтработаноЧасовДляИтога. В выражении этого поля мы прописываем наше условие:

   Скопировать код

   
   ВЫБОР
       КОГДА ТехнологическаяОперация.Наименование = "Актированный день" ТОГДА 0
       ИНАЧЕ ОтработаноЧасов
   КОНЕЦ
   

   Здесь ОтработаноЧасов — это исходный ресурс, а ТехнологическаяОперация.Наименование — поле, по которому мы устанавливаем условие.

2. Добавляем вычисляемое поле в ресурсы: После создания вычисляемого поля ОтработаноЧасовДляИтога, перейдите на закладку "Ресурсы". Добавьте это новое поле в список ресурсов и для него укажите агрегатную функцию СУММА(). Теперь система будет суммировать значения, уже обработанные нашим условным выражением.

   СУММА(ОтработаноЧасовДляИтога)

Преимущества этого подхода:

• Читаемость: Если условие является сложным или содержит множество ветвлений, вынесение его в отдельное вычисляемое поле значительно улучшает читаемость схемы компоновки данных.
• Переиспользование: Если одно и то же условное значение требуется для нескольких различных расчетов или для вывода в разных местах отчета, вычисляемое поле позволяет избежать дублирования кода.
• Гибкость: Вычисляемые поля позволяют создавать новые «виртуальные» поля на лету, что расширяет возможности по настройке данных для агрегации или отображения.

Ограничения:

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

Решение 3: Функция ВычислитьВыражениеСГруппировкойМассив для продвинутых сценариев

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

Как это работает:

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

ВычислитьВыражениеСГруппировкойМассив(
    "Выражение",
    "СписокПолейГруппировки",
    "ОтборЗаписейНабораДанных",
    "ОтборГруппировок",
    "ИспользованиеИерархии"
)

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

Пример использования (концептуально):

Допустим, мы хотим, чтобы в промежуточных итогах по дням сумма ОтработаноЧасов включала все значения, а в общем итоге по всем дням записи с ТехнологическаяОперация.Наименование = "Актированный день" были исключены.

В этом случае, мы могли бы создать несколько ресурсов или использовать эту функцию, чтобы динамически менять поведение агрегации в зависимости от текущего уровня группировки. Например, один ресурс будет суммировать все, а другой, используя ВычислитьВыражениеСГруппировкойМассив и проверяя текущую группировку, будет возвращать 0 для "Актированного дня" только на уровне общего итога.

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

Решение 4: Настройка свойства "Рассчитывать по..." для ресурсов

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

Как это работает:

1. Поведение по умолчанию: Если свойство "Рассчитывать по..." не заполнено, выражение ресурса, указанное нами, будет применяться для всех полей группировки отчета, а также для общего итога.

2. Явное указание группировок: Мы можем явно указать, для каких именно группировок будет рассчитываться и отображаться данный ресурс. Например, мы можем создать один ресурс, который считает полную сумму для детализации и промежуточных группировок, и другой ресурс, который использует условное суммирование (как в Решении 1) и который мы явно свяжем только с общим итогом.

3. Использование ОбщийИтог: Существует специальное значение ОбщийИтог, которое можно указать в свойстве "Рассчитывать по...". Это позволяет использовать выражение ресурса исключительно для вывода общего итога отчета, игнорируя его на уровнях детализации и промежуточных группировок. Таким образом, мы можем иметь одно выражение для детализации (например, просто СУММА(ОтработаноЧасов)) и другое, условное выражение, специально для ОбщийИтог.

Практическое применение:

Предположим, нам нужно всегда видеть полное количество ОтработаноЧасов в детализации и промежуточных итогах, но в общем итоге, чтобы "Актированный день" не учитывался. Мы можем сделать так:

1. Создаем ресурс "ОтработаноЧасовБезУсловия" с выражением СУММА(ОтработаноЧасов). Устанавливаем для него свойство "Рассчитывать по..." для всех нужных группировок, кроме ОбщийИтог (или не заполняем, если это основное поле).

2. Создаем ресурс "ОтработаноЧасовДляОбщегоИтога" с выражением:

   Скопировать код

   
   СУММА(ВЫБОР
       КОГДА ТехнологическаяОперация.Наименование = "Актированный день" ТОГДА 0
       ИНАЧЕ ОтработаноЧасов
   КОНЕЦ )
   

   И для этого ресурса в свойстве "Рассчитывать по..." явно указываем только ОбщийИтог.

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

Важное уточнение: Отличие отбора от условного суммирования

В ходе обсуждения часто возникает путаница между использованием "Отбора" и механизмами условного суммирования. Важно четко понимать разницу между этими двумя подходами.

Отбор (Закладка "Отбор" в СКД):

• Отбор применяется *до* того, как данные попадут в отчет для дальнейшей обработки и агрегации.
• Его основная функция — полностью исключить строки данных, которые не соответствуют заданному условию.
• Если вы используете отбор, например, Столбец1 <> "Х", то все строки, где Столбец1 = "Х", не попадут в отчет вообще. Они не будут отображены в детализации и, соответственно, не будут участвовать ни в каких расчетах итогов.

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

Условное суммирование (СУММА(ВЫБОР КОГДА ... КОНЕЦ), вычисляемые поля):

• Эти методы работают с уже отобранными (или всеми) детальными строками.
• Они изменяют значение, которое будет передано в агрегатную функцию (например, СУММА) для каждой строки, на основе заданных условий.
• Сами строки при этом остаются в отчете и отображаются в детализации, но их вклад в итоговую сумму может быть изменен (например, обнулен).

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

В конечном итоге, выбор конкретного метода зависит от сложности вашей задачи, требуемого уровня детализации контроля и предпочтений в читаемости кода. Начиная с простого СУММА(ВЫБОР КОГДА ... КОНЕЦ) и продвигаясь к вычисляемым полям или тонким настройкам ресурсов, вы сможете эффективно реализовать практически любые условные итоги в ваших отчетах на СКД.