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

← На главную

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

Постановка задачи и анализ структуры данных

Представим, что у нас есть три показателя, которые выводятся в строках отчета, а колонками выступает период (месяц). Нам необходимо добиться следующего поведения:

Показатель 1 и 2: По вертикали (в разрезе подразделений или других аналитик) должна считаться Сумма. По горизонтали (в итоговой колонке за год) должен выводиться Максимум за все месяцы.
Показатель 3: Это расчетный коэффициент. Его итог должен всегда рассчитываться как Сумма(Показатель 1) / Сумма(Показатель 2), независимо от того, смотрим мы итог по строке или по колонке.

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

Метод 1: Использование колонки «Рассчитывать по» в ресурсах

Этот метод идеально подходит для решения задачи с «Показателем 1» и «Показателем 2». В СКД для одного и того же поля можно добавить несколько записей в список ресурсов с разными выражениями агрегации.

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

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

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

Метод 2: Настройка формул для расчетных показателей

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

Рассмотрим, как прописать выражение для такого ресурса. В поле выражения на вкладке Ресурсы мы можем использовать конструкцию ВЫБОР, чтобы разделить логику для разных типов данных, если они приходят в одной колонке (например, если «ИмяПоказателя» — это измерение):


ВЫБОР 
    КОГДА ИмяПоказателя = "Показатель3" 
    ТОГДА ВычислитьВыражение("Сумма(Значение)", "ИмяПоказателя = 'Показатель1'") / 
          ЕСТЬNULL(ВЫБОР КОГДА ВычислитьВыражение("Сумма(Значение)", "ИмяПоказателя = 'Показатель2'") = 0 
                   ТОГДА 1 
                   ИНАЧЕ ВычислитьВыражение("Сумма(Значение)", "ИмяПоказателя = 'Показатель2'") 
                   КОНЕЦ, 1)
    ИНАЧЕ Сумма(Значение)
КОНЕЦ

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


Сумма(Показатель1) / ВЫБОР КОГДА Сумма(Показатель2) = 0 ТОГДА 1 ИНАЧЕ Сумма(Показатель2) КОНЕЦ

Важный момент: Чтобы этот расчет корректно работал в кросс-таблице как по строкам, так и по колонкам, убедитесь, что в колонке Рассчитывать по для этого ресурса установлены все необходимые группировки и общие итоги.

Метод 3: Сложные итоги через «ВычислитьВыражениеСГруппировкойТаблицыЗначений»

Бывают случаи, когда итог по горизонтали должен считаться как «среднее из максимальных значений по подразделениям». В таких ситуациях обычных агрегатных функций недостаточно. Выясним, как применить функцию ВычислитьВыражениеСГруппировкойТаблицыЗначений.

Эта функция позволяет СКД свернуть данные во внутреннюю временную таблицу и выполнить над ними повторную агрегацию. Проанализируем пример выражения:


Максимум(ВычислитьВыражениеСГруппировкойТаблицыЗначений("Сумма(Показатель1)", "Месяц"))

Разберем, что здесь происходит:

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

Это позволяет реализовать логику «итог от итогов», которая часто требуется в управленческой отчетности.

Управление визуальным расположением итогов

Помимо формул, важно правильно настроить отображение. В СКД на уровне настроек варианта отчета (вкладка Другие настройки) можно управлять параметрами:

Расположение итогов по горизонтали: позволяет перенести итоговую колонку в начало или конец таблицы.
Расположение итогов по вертикали: управляет выводом итоговых строк (подвал отчета).

Если для какого-то конкретного показателя итог по горизонтали не имеет смысла, мы можем воспользоваться Условным оформлением. Выберем область «В ячейке ресурса», установим условие (например, ИмяПоказателя = "Показатель3") и в оформлении укажем текст «-» или просто обнулим значение для определенных группировок, чтобы не путать пользователя лишними цифрами. Для дополнительной настройки визуального вида кросс-таблицы пригодится прием скрытия пустых столбцов в СКД.

Резюме

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

Используйте несколько строк ресурсов для одного поля, если функции агрегации для строк и колонок различаются.
Применяйте условные выражения в ресурсах (ВЫБОР КОГДА), если логика расчета зависит от конкретного элемента группировки.
Для расчетов типа «сумма деленная на сумму» всегда прописывайте формулу в выражении ресурса, а не в запросе, чтобы агрегация происходила до деления.
В самых сложных иерархических расчетах используйте функцию ВычислитьВыражениеСГруппировкойТаблицыЗначений.

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