Как рассчитать начальный остаток нарастающим итогом в СКД с помощью функции ВычислитьВыражение?

← На главную

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

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

Почему возникает проблема "пустой первой строки"?

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


ВычислитьВыражение("Сумма(СуммаНачальныйОстаток)", , , "Первая", "Первая") 
+ ВычислитьВыражение("Сумма(СуммаПриход-СуммаРасход)", , , "Первая", "Предыдущая")

Логика этого выражения проста и понятна:

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

Такой подход отлично работает для второй, третьей и последующих строк отчета. Но на самой первой строке система сталкивается с проблемой: предыдущей строки еще не существует. Граница диапазона вычисления ("Предыдущая") указывает в пустоту. В результате вся функция ВычислитьВыражение для расчета оборотов возвращает значение NULL (или пустое значение). При попытке сложить числовое значение начального остатка с неопределенным результатом сложения СКД возвращает пустую ячейку (или 0), ломая расчет для первой строки.

Давайте разберем три рабочих способа решения этой проблемы — от варианта автора до максимально элегантных математических оптимизаций.

Решение 1: Обработка первой строки через ВЫБОР КОГДА (Вариант автора)

Автор темы самостоятельно нашел решение, добавив в выражение СКД явную проверку заполненности результата вычисления для предыдущей строки с помощью конструкции ВЫБОР КОГДА и функции ЗначениеЗаполнено.

Формула в поле ресурса СКД в этом случае выглядит так:


ВычислитьВыражение("Сумма(СуммаНачальныйОстаток)", , , "Первая", "Первая") 
+ Выбор 
    Когда ЗначениеЗаполнено(ВычислитьВыражение("Сумма(СуммаПриход-СуммаРасход)", , , "Предыдущая", "Предыдущая")) 
        Тогда ВычислитьВыражение("Сумма(СуммаПриход-СуммаРасход)", , , "Первая", "Предыдущая") 
    Иначе 0 
Конец

Как это работает: Мы проверяем, существует ли вообще предыдущая строка (вычисляя значение оборота конкретно для нее). Если она существует и заполнена, СКД выполняет стандартный расчет оборотов с первой по предыдущую строку. Если же предыдущей строки нет (это первая запись), выражение возвращает 0, и к начальному остатку первой строки просто прибавляется ноль. Проблема решена, но само выражение выглядит громоздким и сложным для понимания.

Решение 2: Оптимизация формулы через функцию ЕСТЬNULL

Мы можем значительно упростить выражение автора, избавившись от громоздкой конструкции ВЫБОР КОГДА. В СКД внутри выражений ресурсов можно использовать стандартную функцию ЕСТЬNULL. Она отлично перехватывает пустое значение, возникающее при обращении к несуществующей предыдущей строке.

Давайте посмотрим, как будет выглядеть оптимизированная формула:


ВычислитьВыражение("Сумма(СуммаНачальныйОстаток)", , , "Первая", "Первая") 
+ ЕСТЬNULL(ВычислитьВыражение("Сумма(СуммаПриход - СуммаРасход)", , , "Первая", "Предыдущая"), 0)

Преимущества этого способа: Формула стала в два раза короче и легче для чтения. Если диапазон вычислений от "Первой" до "Предыдущей" возвращает NULL (что происходит исключительно на первой строке), функция ЕСТЬNULL мгновенно подставляет вместо него 0, обеспечивая корректное сложение.

Решение 3: Математический обход без использования значения "Предыдущая"

Существует еще более элегантный математический лайфхак, который полностью исключает появление значения NULL на первой строке и не требует никаких проверок заполненности.

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

Запишем это математическое правило на языке СКД:


ВычислитьВыражение("Сумма(СуммаНачальныйОстаток)", , , "Первая", "Первая") 
+ ВычислитьВыражение("Сумма(СуммаПриход - СуммаРасход)", , , "Первая", "Текущая") 
- (СуммаПриход - СуммаРасход)

Давайте пошагово проанализируем, как этот алгоритм отрабатывает на разных строках группировки:

Для 1-й строки: Начальный остаток первой строки + Оборот 1-й строки (с первой по текущую) - Оборот 1-й строки. Математически обороты взаимно уничтожаются, и мы получаем чистый Начальный остаток. Никаких пустых значений и ошибок.
Для 2-й строки: Начальный остаток + Обороты (Строка 1 + Строка 2) - Оборот Строки 2. В результате получаем: Начальный остаток + Оборот Строки 1. Это в точности соответствует начальному остатку для второй строки.
Для 3-й строки: Начальный остаток + Обороты (Строка 1 + Строка 2 + Строка 3) - Оборот Строки 3. На выходе: Начальный остаток + Обороты за первую и вторую строки.

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

Важные правила работы с ВычислитьВыражение на практике

Часто разработчики пытаются применить функцию ВычислитьВыражение для решения других похожих задач (например, для вывода курсов валют на каждый день периода, как в сообщении №27) и получают нулевые или неверные результаты. Чтобы избежать этих ошибок, рекомендуем соблюдать следующие правила:

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

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