При построении аналитических отчетов в схеме компоновки данных часто возникает задача формирования таблицы, где строки содержат номенклатурные позиции, а колонки представляют собой шкалу времени (например, месяцы). Типичная проблема в таких отчетах — расчет прогнозируемого или планового остатка на конец каждого периода. В отличие от стандартного фактического остатка, плановый показатель рассчитывается динамически: остаток на начало первого месяца плюс накопленный план производства за вычетом накопленного плана продаж.
Рассмотрим подробно причины возникновения ошибок при расчете подобных показателей в СКД и разберем практические способы решения задачи как в выражениях ресурсов компоновки данных, так и на уровне языка запросов 1С.
Для корректного вычисления планового переходящего остатка на конец каждого периода применяется математическая формула нарастающего итога:
Плановый остаток = Фактический остаток на начало отчета + Нарастающий итог (План производства) - Нарастающий итог (План продаж)
Попытка реализовать данную формулу в СКД через функцию ВычислитьВыражение для ресурса выглядит следующим образом:
ЕстьNull(ВычислитьВыражение("Сумма(КоличествоНачальныйОстаток)", "ПериодМесяц", , "Первая", "Первая"), 0)
+ ЕстьNull(ВычислитьВыражение("Сумма(ЕстьNull(КоличествоПланПроизводство, 0))", "ПериодМесяц", , "Первая", "Текущая"), 0)
- ЕстьNull(ВычислитьВыражение("Сумма(КоличествоПланПродаж)", "ПериодМесяц", , "Первая", "Текущая"), 0)
Однако на практике разработчики сталкиваются с тем, что расчет ломается по отдельным позициям номенклатуры. Проанализируем, почему это происходит:
"Первая" и "Текущая" обрабатывает только существующие записи группировки. Если для товара первое движение произошло, например, в марте, то позицией "Первая" для него станет март, а не январь, что приведет к искажению начальной базы расчета.ВычислитьВыражение отвечает за сортировку записей при обходе. Если этот параметр не заполнен, СКД не гарантирует строгий хронологический порядок обхода записей, из-за чего нарастающий итог суммируется хаотично.КоличествоНачальныйОстаток, полученное из виртуальной таблицы регистра накопления с периодичностью «Месяц», отражает остаток на начало каждого конкретного месяца. При использовании нарастающего итога оборотов начальный остаток должен быть зафиксирован строго на момент старта всего отчета.Разберем по шагам, как исправить расчет ресурса непосредственно в схеме компоновки данных без изменения текста запроса.
ПериодМесяц и настроим дополнение периодов: установим тип дополнения «Месяц», а в качестве начальной и конечной даты укажем параметры отчета &НачалоПериода и &КонецПериода. Это гарантирует построение непрерывной сетки дат для всех строк.ВычислитьВыражение(Выражение, ПолеГруппировки, ТипРасчета, Начало, Конец, Сортировка). Добавим шестым параметром сортировку по полю периода:
ЕстьNull(ВычислитьВыражение("Сумма(КоличествоНачальныйОстаток)", "ПериодМесяц", , "Первая", "Первая", "ПериодМесяц Возр"), 0)
+ ЕстьNull(ВычислитьВыражение("Сумма(ЕстьNull(КоличествоПланПроизводство, 0))", "ПериодМесяц", , "Первая", "Текущая", "ПериодМесяц Возр"), 0)
- ЕстьNull(ВычислитьВыражение("Сумма(ЕстьNull(КоличествоПланПродаж, 0))", "ПериодМесяц", , "Первая", "Текущая", "ПериодМесяц Возр"), 0)
Наиболее надежный и производительный способ — вычисление нарастающего итога до передачи данных в компоновку. Начиная с версии платформы 8.3.20, язык запросов 1С поддерживает аналитические (оконные) функции. Это исключает сложные и ресурсоемкие формулы в СКД.
Рассмотрим пример запроса, где нарастающий итог оборотов формируется непосредственно в базе данных:
ВЫБРАТЬ
ТаблицаДанных.Номенклатура КАК Номенклатура,
ТаблицаДанных.ПериодМесяц КАК ПериодМесяц,
ТаблицаДанных.НачальныйОстаток КАК НачальныйОстаток,
ТаблицаДанных.КоличествоПланПроизводство КАК КоличествоПланПроизводство,
ТаблицаДанных.КоличествоПланПродаж КАК КоличествоПланПродаж,
СУММА(ТаблицаДанных.КоличествоПланПроизводство) OVER (
PARTITION BY ТаблицаДанных.Номенклатура
ORDER BY ТаблицаДанных.ПериодМесяц
ROWS МЕЖДУ НЕОГРАНИЧЕННО ПРЕДШЕСТВУЮЩИЕ И ТЕКУЩАЯ СТРОКА
) КАК ПланПроизводстваНарастающий,
СУММА(ТаблицаДанных.КоличествоПланПродаж) OVER (
PARTITION BY ТаблицаДанных.Номенклатура
ORDER BY ТаблицаДанных.ПериодМесяц
ROWS МЕЖДУ НЕОГРАНИЧЕННО ПРЕДШЕСТВУЮЩИЕ И ТЕКУЩАЯ СТРОКА
) КАК ПланПродажНарастающий
ИЗ
ВТ_СводныеДанные КАК ТаблицаДанных
В схеме компоновки данных для ресурса ПлановыйОстаток останется указать простое и понятное арифметическое выражение: Сумма(НачальныйОстаток + ПланПроизводстваНарастающий - ПланПродажНарастающий).
Если отчет разрабатывается для более ранних версий платформы, где оконные функции недоступны, сформируем нарастающий итог классическим способом через соединение временной таблицы самой с собой.
Разберем логику построения такого запроса:
ВЫБРАТЬ
Т1.Номенклатура КАК Номенклатура,
Т1.ПериодМесяц КАК ПериодМесяц,
МАКСИМУМ(Т1.НачальныйОстаток) КАК НачальныйОстаток,
МАКСИМУМ(Т1.КоличествоПланПроизводство) КАК КоличествоПланПроизводство,
МАКСИМУМ(Т1.КоличествоПланПродаж) КАК КоличествоПланПродаж,
СУММА(ЕСТЬNULL(Т2.КоличествоПланПроизводство, 0)) КАК ПланПроизводстваНарастающий,
СУММА(ЕСТЬNULL(Т2.КоличествоПланПродаж, 0)) КАК ПланПродажНарастающий
ИЗ
ВТ_ДанныеПоМесяцам КАК Т1
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ДанныеПоМесяцам КАК Т2
ПО Т1.Номенклатура = Т2.Номенклатура
И Т1.ПериодМесяц >= Т2.ПериодМесяц
СГРУППИРОВАТЬ ПО
Т1.Номенклатура,
Т1.ПериодМесяц
Посмотрим на условие соединения: строка текущего месяца соединяется со всеми записями того же товара за предшествующие и текущий периоды (Т1.ПериодМесяц >= Т2.ПериодМесяц). Последующая группировка по полям таблицы Т1 со сверткой сумм из таблицы Т2 формирует корректный нарастающий итог.
Вынесение вычислений на уровень запроса защищает отчет от ошибок пустых ячеек компоновки и существенно увеличивает скорость формирования отчета при больших объемах номенклатуры.