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