Разработчики отчетов на системе компоновки данных (СКД) часто сталкиваются со стандартной, но весьма коварной проблемой: на детальном уровне или на уровне нижних группировок расчет показателей выполняется абсолютно верно, но как только дело доходит до общих итогов или родительских группировок, СКД выводит некорректные цифры. Вместо того чтобы просто сложить получившиеся значения нижних уровней, система пытается рассчитать общую формулу для итоговой строки целиком. Это классическая проблема расчета «суммы произведений» вместо «произведения сумм».
В этой статье мы подробно разберем, почему возникает такая ситуация, и как решить ее с помощью мощной встроенной функции СКД ВычислитьВыражениеСГруппировкойМассив. Мы детально изучим ее синтаксис, настройки на вкладке ресурсов и рассмотрим альтернативные подходы к оптимизации расчетов.
Представим типовую бизнес-задачу. Нам необходимо рассчитать показатель упущенной выгоды. На уровне детальных записей (или минимальной группировки, например, по конкретному сотруднику и складу) формула выглядит следующим образом:
Минимум(ДнейОтсутствия) * Максимум(Коэффициент)
Если мы просто добавим это вычисляемое поле в список ресурсов СКД и укажем для него стандартное выражение Сумма(ИмяПоля), то получим крайне неприятный результат. Система компоновки данных на уровне общих итогов не станет складывать построчно ранее вычисленные значения. Вместо этого СКД возьмет минимальное значение дней отсутствия по всем сотрудникам сразу, умножит его на максимальный коэффициент по всем записям базы данных и выведет это абстрактное число в качестве общего итога. Разумеется, итоговая сумма окажется в корне неверной.
Для наглядности проанализируем поведение системы на простом примере данных:
| Группировка (Сотрудник) | Минимум(ДнейОтсутствия) | Максимум(Коэф) | Ожидаемый результат (Построчно) | Как посчитает стандартная СКД в итогах |
|---|---|---|---|---|
| Иванов И.И. | 2 | 1.5 | 3.0 | — |
| Петров П.П. | 5 | 2.0 | 10.0 | — |
| ОБЩИЙ ИТОГ | 2 (Минимум из 2 и 5) | 2.0 (Максимум из 1.5 и 2) | 13.0 (Сумма: 3 + 10) | 4.0 (Расчет итога: 2 * 2) |
Как мы видим, стандартный механизм СКД выдает результат 4.0 вместо ожидаемых 13.0. Нам же требуется заставить систему сначала рассчитать выражение для каждой строки группировки отдельно, сложить получившиеся результаты в виртуальный массив и затем просуммировать элементы этого массива на уровне общего итога. Именно для таких целей и предназначена функция ВычислитьВыражениеСГруппировкойМассив.
Давайте подробно изучим синтаксис этой функции, поскольку информации по ней во встроенной справке зачастую бывает недостаточно для быстрого освоения.
Полный технический синтаксис функции выглядит следующим образом:
ВычислитьВыражениеСГруппировкойМассив(Выражение, ВыраженияПолейГруппировки, ОтборЗаписей, ОтборГруппировок)
Разберем назначение каждого параметра по шагам:
"Минимум(ДнейОтсутствия) * Максимум(Коэф)". Важно помнить, что выражение передается обязательно в двойных кавычках."Сотрудник, Склад"). Именно эти поля определяют «гранулярность» или уровень детализации расчета. По сути, СКД выполняет виртуальную группировку данных по этим полям, рассчитывает для каждой строки выражение из первого параметра и помещает результат в результирующий массив."Активно = Истина". Важное ограничение: здесь нельзя использовать агрегатные функции."Сумма(Продажи) > &МинимальныйПорог".Перейдем к практической реализации решения в настройках СКД. Для того чтобы отчет формировал корректные итоги, выполним следующие шаги.
Шаг 1. Перейдем на вкладку Вычисляемые поля и добавим наше поле (например, назовем его УпущеннаяВыгода). В колонке «Выражение» мы можем указать простую формулу для детального уровня:
Коэф * ДнейОтсутствия
Шаг 2. Перейдем на вкладку Ресурсы. Здесь нам потребуется тонкая настройка формул расчета для разных уровней группировок отчета.
Вместо того чтобы писать одну универсальную формулу для всех полей, мы разделим области расчета. В таблице ресурсов добавим несколько строк для нашего поля УпущеннаяВыгода:
Строка №1 (для детальных записей и нижних группировок):
УпущеннаяВыгодаМинимум(ДнейОтсутствия) * Максимум(Коэф)Сотрудник)Строка №2 (для вышестоящих группировок и Общего итога):
УпущеннаяВыгода
Сумма(ВычислитьВыражениеСГруппировкойМассив("Минимум(ДнейОтсутствия) * Максимум(Коэф)", "Сотрудник"))
Подразделение, Организация) и обязательно отмечаем флажком Общий итог.Давайте проанализируем внутреннюю механику работы СКД при использовании такой конструкции. Когда компоновщик доходит до формирования строки общего итога:
ВычислитьВыражениеСГруппировкойМассив.Сотрудник рассчитывается выражение Минимум(ДнейОтсутствия) * Максимум(Коэф).[3.0, 10.0].Сумма(...) принимает этот массив в качестве аргумента и складывает его элементы: 3.0 + 10.0 = 13.0.Обратите внимание на очень важное правило, о котором часто забывают начинающие разработчики: функции семейства ВычислитьВыражение и Вычислить работают с текущими, соседними данными или данными вышестоящих группировок. А функция ВычислитьВыражениеСГруппировкойМассив — это единственный штатный инструмент СКД для обращения к нижестоящим группировкам сверху вниз.
Понимание работы этой функции открывает широкие возможности для решения других сложных аналитических задач в отчетах 1С. Рассмотрим еще два популярных примера.
Довольно часто в торговых отчетах требуется рассчитать сумму превышения лимитов. Если по какому-то товару остаток превысил норму, мы должны зафиксировать это превышение. Если остаток меньше нормы — превышение равно нулю. Однако при простом суммировании отрицательные отклонения одних товаров будут компенсировать положительные отклонения других, что исказит общую картину.
Решить эту задачу можно с помощью следующего выражения в ресурсах:
Сумма(ВычислитьВыражениеСГруппировкойМассив("Выбор Когда Сумма(Остаток) - Максимум(Лимит) < 0 Тогда 0 Иначе Сумма(Остаток) - Максимум(Лимит) Конец", "Номенклатура"))
Этот код гарантирует, что на уровне общего итога сложатся только положительные величины превышений по каждой номенклатурной позиции.
Когда требуется рассчитать «среднее от средних» значений по подразделениям, обычная функция Среднее() на уровне итогов отработает неверно, так как она просто усреднит все детальные строки без учета веса подразделений. С помощью массива группировки мы можем сначала получить средние показатели каждого подразделения, а затем вычислить среднее по получившемуся массиву:
Среднее(ВычислитьВыражениеСГруппировкойМассив("Среднее(ПроцентВыполнения)", "Подразделение"))
Несмотря на свою колоссальную полезность, функция ВычислитьВыражениеСГруппировкойМассив имеет свои теневые стороны, которые необходимо учитывать при проектировании архитектуры отчета:
Таким образом, мы детально разобрали механизм применения функции ВычислитьВыражениеСГруппировкойМассив, научились обходить ограничения стандартного суммирования в СКД и настраивать ресурсы отчета для получения абсолютно корректных итоговых данных на любых уровнях группировки.