При разработке отчетов планирования или анализа запасов в системе 1С:Предприятие часто возникает классическая задача: необходимо вывести плановый остаток товаров на каждую дату определенного периода. Основная сложность заключается в том, что стандартные виртуальные таблицы остатков и оборотов СУБД возвращают данные только за те дни, в которых были совершены реальные движения (продажи, поступления, корректировки). Если в какой-то из дней отгрузок не планировалось, эта дата просто «выпадает» из результата запроса, а плановый остаток на нее не рассчитывается.
Формула расчета планового остатка обычно выглядит следующим образом:
Плановый остаток = Остаток на начало периода - Накопительная сумма продаж (нарастающий итог) в каждом последующем периоде
В этой статье мы подробно разберем, как решить эту проблему. Мы проанализируем три различных подхода: от самого быстрого и технологичного штатного механизма СКД до оптимизированного построения низкоуровневых запросов.
Вопреки распространенному мнению, в Системе компоновки данных (СКД) есть мощный встроенный механизм, специально предназначенный для решения этой задачи. Нам не нужно вручную генерировать таблицы дат в коде или перегружать СУБД сложными соединениями. СКД умеет автоматически генерировать недостающие даты («пустые» периоды) и правильно распределять остатки по ним.
Разберем по шагам, как настроить этот механизм:
Откроем схему компоновки данных и перейдем на вкладку «Наборы данных». Для поля нашей даты (например, ПериодДень) необходимо обязательно настроить роль. В колонке «Роль» укажем, что это поле является периодом, и присвоим ему порядковый номер (например, 1).
Для поля начального остатка (например, НачальныйОстаток) настроим роль Остаток. Укажем тип «Начальный», имя — Количество, а в поле связи укажем соответствующее поле конечного остатка.
Для поля конечного остатка (например, КонечныйОстаток) настроим роль аналогично: тип «Конечный», имя — Количество, связь с начальным остатком.
Перейдем на вкладку «Настройки» и выберем группировку, которая отвечает за вывод периодов (например, колонки в кросс-таблице по полю ПериодДень).
В свойствах этой группировки (на вкладке «Системные поля» или через свойства группировки в нижней части экрана) найдем параметр Тип дополнения и установим его в значение День (или другой необходимый период: неделя, месяц).
Также здесь можно указать начальную и конечную даты дополнения. Их целесообразно связать с параметрами отчета &НачалоПериода и &КонецПериода, чтобы СКД дополняла даты строго в рамках выбранного пользователем интервала.
Как это работает: При формировании отчета СКД сама проанализирует выборку. Если между двумя датами с движениями будет пропуск (например, движения были только 15.01 и 18.01), система автоматически сгенерирует строки (или колонки) для 16.01 и 17.01, продублировав на эти даты актуальный остаток предыдущего дня.
Если нам необходимо рассчитать плановый остаток по формуле вычитания накопительного итога продаж из начального остатка, мы можем использовать мощную функцию СКД ВычислитьВыражение(). Это позволит перенести расчеты из тяжелого SQL-запроса в оперативную память на этапе компоновки результата, что работает значительно быстрее.
Выясним, как настроить расчет нарастающего итога продаж:
ПродажиНарастающимИтогом.
ВычислитьВыражение("Сумма(ОборотыПродаж)", "ПериодДень", , "Первая", "Текущая")
Здесь функция рассчитывает сумму продаж от самой первой строки группировки ПериодДень до текущей строки включительно.
ПлановыйОстаток и пропишем выражение:
ЕстьNull(НачальныйОстаток, 0) - ВычислитьВыражение("Сумма(ОборотыПродаж)", "ПериодДень", , "Первая", "Текущая")
Использование этого метода в сочетании с автодополнением периодов (Способ 1) дает максимально производительный и элегантный результат без написания какого-либо программного кода в модулях.
Бывают ситуации, когда логика отчета слишком сложна или данные выводятся без использования стандартных возможностей СКД (например, при постобработке в коде через ТаблицаЗначений). В этом случае нам придется сгенерировать таблицу всех дат периода непосредственно в запросе и объединить ее с остатками.
Многие разработчики совершают ошибку, делая полное соединение (cross join) всей таблицы товаров со всеми датами периода, что на больших базах данных приводит к критическому падению производительности. Рассмотрим наиболее оптимизированный алгоритм запроса:
Для получения списка всех дней периода проще всего использовать производственный календарь, который уже заполнен в системе.
ВЫБРАТЬ
Календарь.Дата КАК Период
ПОМЕСТИТЬ ВТ_Календарь
ИЗ
РегистрСведений.РегламентированныйПроизводственныйКалендарь КАК Календарь
ГДЕ
Календарь.Дата МЕЖДУ &НачалоПериода И &КонецПериода
ИНДЕКСИРОВАТЬ ПО
Период
;
Нам не нужно генерировать даты для абсолютно всей номенклатуры базы данных. Ограничим выборку только интересующими нас позициями.
ВЫБРАТЬ РАЗЛИЧНЫЕ
Заказы.Номенклатура КАК Номенклатура
ПОМЕСТИТЬ ВТ_АктивнаяНоменклатура
ИЗ
РегистрНакопления.ЗаказыПокупателей КАК Заказы
ГДЕ
Заказы.Период МЕЖДУ &НачалоПериода И &КонецПериода
;
Теперь мы соединяем только отфильтрованные товары с нашей таблицей дат. Получаем плоскую сетку, в которой для каждого нужного товара гарантированно присутствует каждая дата периода.
ВЫБРАТЬ
Номенклатура.Номенклатура КАК Номенклатура,
Календарь.Период КАК Период
ПОМЕСТИТЬ ВТ_СеткаДатыТовары
ИЗ
ВТ_АктивнаяНоменклатура КАК Номенклатура
СОЕДИНЕНИЕ ВТ_Календарь КАК Календарь ПО ИСТИНА
ИНДЕКСИРОВАТЬ ПО
Номенклатура,
Период
;
На последнем шаге мы берем нашу подготовленную сетку ВТ_СеткаДатыТовары и левым соединением подключаем к ней таблицы реальных продаж и остатков. При этом расчет нарастающего итога планового остатка можно реализовать прямо в запросе через связь по условию Движения.Период <= Сетка.Период.
Мы проанализировали различные методы решения проблемы «пустых» дат в отчетах. Подводя итоги, дадим несколько важных рекомендаций:
ВычислитьВыражение() для расчета нарастающих итогов (Способ 2) в ресурсах СКД. Это избавит ваш запрос от тяжелых соединений «самого на себя» для расчета накопительных сумм.Номенклатура, Период). Отсутствие индексов на больших объемах данных может замедлить формирование отчета в десятки раз.