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