При разработке сложных отчетов в системе компоновки данных (СКД) часто возникает задача расчета так называемого удельного веса. Особенно это актуально для кросс-таблиц, где нам необходимо разделить значение в текущей ячейке (на пересечении строки и колонки) на общий итог по этой строке или по определенной группировке. Проблема заключается в том, что стандартные средства агрегации Сумма() или Количество() работают в контексте текущей выборки, и просто так «дотянуться» до итогов соседних колонок или всей строки бывает непросто.
В данной статье мы подробно разберем, как реализовать расчет удельного веса, используя продвинутые функции СКД, такие как ВычислитьВыражение и ВычислитьВыражениеСГруппировкойМассив. Мы проанализируем ситуацию, когда необходимо посчитать отношение количества вхождений номенклатуры в категорию к общему количеству номенклатуры в группировке.
Представим ситуацию: у нас есть кросс-таблица. В строках — иерархия номенклатуры и склады, в колонках — категории или периоды. Нам нужно в каждой ячейке получить процентное соотношение: сколько позиций текущей категории находится на складе относительно общего количества позиций в этой группе номенклатуры (по всем складам или категориям в строке).
Главная сложность здесь заключается в том, что расчет «знаменателя» (общего количества) должен игнорировать фильтры колонок, но при этом соблюдать фильтры строк. Рассмотрим, как это реализовать по шагам.
Первый и самый распространенный способ решения задачи — использование функции ВычислитьВыражение. Эта функция позволяет вычислить любое выражение в контексте другой группировки или всего отчета. Для расчета удельного веса формула в поле «Ресурс» будет выглядеть следующим образом:
Сумма(КоличествоВхождений) / ВычислитьВыражение("Сумма(КоличествоВхождений)", "Группировка", "ОбщийИтог")
Разберем параметры подробнее:
"Группировка" — указывает на то, что расчет должен производиться в контексте определенной группировки."ОбщийИтог" — ключевой момент. Он указывает СКД, что нужно взять сумму по всей строке (игнорируя разбивку по колонкам), чтобы получить итоговое значение для деления.Если задача стоит сложнее, например, нужно посчитать количество уникальных элементов номенклатуры в группировке (как упоминалось в обсуждении), то простая сумма не поможет. Нам потребуется функция ВычислитьВыражениеСГруппировкойМассив. Она сначала собирает данные на нижнем уровне (детальные записи), а затем позволяет нам выполнить операцию над этим набором данных.
Рассмотрим пример расчета общего количества уникальной номенклатуры в группе, который мы затем используем как знаменатель:
Количество(ВычислитьВыражениеСГруппировкойМассив("Номенклатура", "Номенклатура"))
Это выражение вернет массив всех элементов Номенклатура внутри текущего контекста и подсчитает их количество. Чтобы встроить это в расчет удельного веса внутри ресурса, мы можем комбинировать подходы:
Сумма(КоличествоВхождений) / ВычислитьВыражение("Количество(ВычислитьВыражениеСГруппировкойМассив(""Номенклатура"", ""Номенклатура""))", "", "ОбщийИтог")
Важно помнить, что расчет в ячейках (детальных пересечениях) и расчет в общих итогах может требовать разных формул. В СКД на вкладке «Ресурсы» мы можем добавить одно и то же поле несколько раз, указывая в колонке «Рассчитывать по...» разные группировки.
Проанализируем типичную настройку:
Выясним причину, почему иногда расчет «не взлетает». Чаще всего это происходит из-за неправильного указания имени группировки во втором параметре ВычислитьВыражение. Если вы работаете в кросс-таблице и хотите получить итог по строке, убедитесь, что вы обращаетесь к имени группировки, которая формирует строки, либо используйте пустые кавычки "" для текущего контекста в сочетании с "ОбщийИтог".
Часто программисты сталкиваются с тем, что в итогах нужно вывести значение, которое зависит от логики детальных записей (например, «если в строках была хотя бы одна единица, то в итоге выведи сумму»). Для этого используется «агрегация над агрегатами».
Посмотрим на пример, когда нам нужно в итоговой колонке кросс-таблицы просуммировать результаты, вычисленные для каждой строки отдельно:
Сумма(ВычислитьВыражениеСГруппировкойМассив("Максимум(МойПоказатель)", "Номенклатура"))
Здесь СКД сначала определит Максимум(МойПоказатель) для каждой номенклатуры, поместит эти значения в массив, а затем функция Сумма сложит элементы этого массива для вывода в общие итоги. Именно так можно получить в самой правой колонке отчета данные, которые логически отличаются от того, что суммируется в обычных ячейках.
Проанализируем еще один важный технический нюанс. В СКД ресурсы вычисляются последовательно — сверху вниз в списке ресурсов. Если ваш «Удельный вес» зависит от другого вычисляемого ресурса (например, «Количество вхождений»), убедитесь, что поле «Удельный вес» находится ниже в списке на вкладке ресурсов. В противном случае расчет может вернуть 0 или NULL, так как исходные данные еще не были агрегированы.
Также при работе с платформой версии 8.3.8 и выше, обратите внимание на стабильность имен группировок. Если кросс-таблица строится динамически, иногда безопаснее использовать предопределенные контексты, такие как "НаборДанных", чтобы избежать ошибки «Группировка не найдена» при обращении к знаменателю дроби.
Подводя итог, для реализации сложного расчета в ячейках кросс-таблицы, отличного от обычных итогов, мы рекомендуем придерживаться следующего алгоритма:
ВычислитьВыражение с параметром "ОбщийИтог" для получения знаменателя, который не зависит от фильтров колонок.ВычислитьВыражениеСГруппировкойМассив.Такой подход делает отчет гибким и позволяет реализовывать практически любую математическую модель внутри компоновки данных без изменения основного запроса.