Как в ресурсах СКД рассчитать процент относительно другой строки того же уровня?

← На главную

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

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

Анализ задачи и подготовка данных

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

Выясним причину возможных ошибок: если знаменатель формулы вернет ноль или NULL, отчет выдаст ошибку формирования или пустые значения. Поэтому при расчете процента обязательно предусматриваем проверку деления на ноль.

Способ 1. Использование функции ВычислитьВыражение в контексте родителя

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

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

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


ВЫБОР
    КОГДА ВычислитьВыражение("Сумма(ВЫБОР КОГДА Статья = Значение(Справочник.Статьи.Доход) ТОГДА Сумма ИНАЧЕ 0 КОНЕЦ)", "ВышестоящаяГруппировка") = 0
        ИЛИ ВычислитьВыражение("Сумма(ВЫБОР КОГДА Статья = Значение(Справочник.Статьи.Доход) ТОГДА Сумма ИНАЧЕ 0 КОНЕЦ)", "ВышестоящаяГруппировка") ЕСТЬ NULL
    ТОГДА 0
    ИНАЧЕ Сумма(Сумма) * 100 / ВычислитьВыражение("Сумма(ВЫБОР КОГДА Статья = Значение(Справочник.Статьи.Доход) ТОГДА Сумма ИНАЧЕ 0 КОНЕЦ)", "ВышестоящаяГруппировка")
КОНЕЦ

Если статьи выводятся на самом верхнем уровне отчета (без родительской группировки), вторым параметром функции передается строковый литерал "ОбщийИтог".

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

Рассмотрим альтернативный способ через агрегацию массива. Функция ВычислитьВыражениеСГруппировкойМассив собирает массив значений по группировке с возможностью наложения дополнительного отбора. Из полученного массива выделяется значение с помощью агрегатной функции Максимум.

Посмотрим на пример формулы в ресурсах:


ВЫБОР
    КОГДА Максимум(ВычислитьВыражениеСГруппировкойМассив("Сумма(Сумма)", "Статья",, "Статья = Значение(Справочник.Статьи.Доход)")) = 0
        ИЛИ Максимум(ВычислитьВыражениеСГруппировкойМассив("Сумма(Сумма)", "Статья",, "Статья = Значение(Справочник.Статьи.Доход)")) ЕСТЬ NULL
    ТОГДА 0
    ИНАЧЕ Сумма(Сумма) * 100 / Максимум(ВычислитьВыражениеСГруппировкойМассив("Сумма(Сумма)", "Статья",, "Статья = Значение(Справочник.Статьи.Доход)"))
КОНЕЦ

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

Способ 3. Разворот показателей в запросе

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


ВЫБОР
    КОГДА Таблица.Статья = &СтатьяДоход ТОГДА Таблица.Сумма
    ИНАЧЕ 0
КОНЕЦ КАК СуммаДоход,
ВЫБОР
    КОГДА Таблица.Статья = &СтатьяСебестоимость ТОГДА Таблица.Сумма
    ИНАЧЕ 0
КОНЕЦ КАК СуммаСебестоимость

В этом случае на вкладке Ресурсы расчет процента сводится к базовой формуле без контекстных функций:


ВЫБОР
    КОГДА Сумма(СуммаДоход) = 0 ИЛИ Сумма(СуммаДоход) ЕСТЬ NULL
    ТОГДА 0
    ИНАЧЕ Сумма(СуммаСебестоимость) * 100 / Сумма(СуммаДоход)
КОНЕЦ

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