При разработке отчетов и аналитических панелей в системе 1С:Предприятие мы часто сталкиваемся с задачей анализа динамики остатков. Типичный пример: необходимо построить комплексный анализ движений и остатков номенклатуры или график изменения складских запасов и денежных средств на расчетных счетах за месяц. Казалось бы, задача тривиальная, но при попытке решить её "в лоб" начинающие разработчики сталкиваются с проблемой: данные выводятся только за те даты, когда были какие-либо движения (приходы или расходы). Если в определенный день склад «спал», этот день просто выпадает из выборки.
В этой статье мы подробно разберем, почему так происходит, и рассмотрим несколько профессиональных способов решения этой задачи. Мы проанализируем варианты как с использованием чистого языка запросов, так и с применением возможностей Системы Компоновки Данных (СКД).
Давайте разберемся в механике работы виртуальных таблиц регистра накопления (или бухгалтерии). Когда мы обращаемся к виртуальной таблице ОстаткиИОбороты с периодичностью День, платформа 1С формирует выборку, основываясь на записях в таблицах итогов и движений. Если за конкретную дату не было никаких записей в регистре, то и в итоговой выборке этой даты физически не существует. Виртуальная таблица не "знает" о существовании календарных дней, в которые ничего не происходило.
Посмотрим на типичную ошибку. Разработчик пишет запрос:
ВЫБРАТЬ
ТоварыНаСкладахОстаткиИОбороты.Период КАК Период,
ТоварыНаСкладахОстаткиИОбороты.Номенклатура,
ТоварыНаСкладахОстаткиИОбороты.КоличествоКонечныйОстаток
ИЗ
РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, День, , ) КАК ТоварыНаСкладахОстаткиИОбороты
В результате, если движения были 1-го и 5-го числа, отчет покажет только две строки. Дни со 2-го по 4-е выпадут, что сделает невозможным построение корректного линейного графика, где ось времени должна быть непрерывной. Рассмотрим, как это исправить.
Если ваша задача — вывести отчет с помощью Системы Компоновки Данных (СКД), то решение проблемы уже встроено в платформу. Это самый простой и рекомендуемый метод, не требующий усложнения текста запроса. Этот механизм позволяет эффективно реализовывать отчеты, где важны движения товаров с резервами и расчет нарастающим итогом.
Разберем этот метод по шагам:
Период.Период.Период и обратите внимание на колонку "Тип дополнения" (в свойствах поля группировки или в параметрах группировки, в зависимости от режима интерфейса).НачалоПериода и КонецПериода нужно установить галочку "Включать в доступные поля" или жестко связать их с настройками дополнения.Что сделает платформа? Она автоматически создаст непрерывную последовательность дат между началом и концом периода отчета. Для тех дат, где данных в выборке нет, СКД подставит NULL, который при выводе ресурсов преобразуется в 0 (если настроено оформление) или скопирует предыдущее значение остатка (если это остаточный ресурс).
Важно: Для корректного расчета конечного остатка на "пустые" дни в СКД необходимо правильно настроить роли полей. Поле КоличествоКонечныйОстаток должно иметь роль "Остаток" в настройках набора данных.
Иногда нам нужно получить данные не в отчет СКД, а в таблицу значений для программной обработки или выгрузки в стороннюю систему. Здесь механизмы СКД нам недоступны. Такой подход часто требуется, когда нужно выполнить сравнение товаров на складах и товаров организаций для поиска ошибок в учете, где критически важно видеть расхождения по конкретным датам.
Основная идея: нам нужна "опорная" таблица, содержащая ВСЕ даты интересующего нас периода. К этой таблице мы будем присоединять наши данные об остатках.
Разберем алгоритм действий:
ЛЕВОЕ СОЕДИНЕНИЕ.Откуда взять список дат?
Самый надежный способ в типовых конфигурациях (ERP, КА, УТ, БП) — использовать регистр сведений РегламентированныйПроизводственныйКалендарь (или аналогичный справочник). Рассмотрим пример кода:
ВЫБРАТЬ
Календарь.ДатаКалендаря КАК Период
ПОМЕСТИТЬ ВТ_Календарь
ИЗ
РегистрСведений.РегламентированныйПроизводственныйКалендарь КАК Календарь
ГДЕ
Календарь.ДатаКалендаря МЕЖДУ &НачалоПериода И &КонецПериода
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Остатки.Период КАК Период,
Остатки.Номенклатура,
Остатки.КоличествоКонечныйОстаток
ПОМЕСТИТЬ ВТ_Данные
ИЗ
РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, День, , ) КАК Остатки
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_Календарь.Период,
ЕСТЬNULL(ВТ_Данные.Номенклатура, &ПустаяНоменклатура) КАК Номенклатура,
ЕСТЬNULL(ВТ_Данные.КоличествоКонечныйОстаток, 0) КАК Остаток
ИЗ
ВТ_Календарь КАК ВТ_Календарь
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Данные КАК ВТ_Данные
ПО ВТ_Календарь.Период = ВТ_Данные.Период
Однако, данный пример кода решит проблему лишь частично. Мы получим строки с нулями вместо дат, где не было движений, но мы не получим значение остатка с предыдущего дня. Мы получим просто NULL (или 0). Это подходит для оборотов, но не для остатков.
Самая сложная часть задачи — заполнить "пустые" дни значением остатка из предыдущего дня. С такой сложностью часто сталкиваются при разработке отчетов, показывающих остатки товаров организаций с себестоимостью и датами последних закупок, где важно видеть актуальное состояние склада на любую дату. В SQL-подобных языках (каким является язык запросов 1С) нет прямых функций для обращения к "предыдущей строке" (как LAG в T-SQL). Поэтому нам придется применить хитрость.
Проанализируем правильный подход к получению остатков на каждый день:
1. Мы не используем виртуальную таблицу ОстаткиИОбороты с периодичностью. Вместо этого мы должны рассчитать остаток на каждый момент времени из нашего календаря.
2. Самый простой способ (но ресурсоемкий) — использовать соединение календаря с виртуальной таблицей Остатки. Но нельзя соединить таблицу остатков с календарем напрямую. В параметрах виртуальной таблицы Остатки дата передается как параметр, а не как поле соединения.
Поэтому рассмотрим оптимальный алгоритм для больших выборок:
К сожалению, в чистом запросе 1С "нарастающий итог" делать сложно (через треугольное соединение таблиц само с собой), что крайне медленно работает на больших объемах данных. Поэтому, если вы не используете СКД, лучше всего выполнить финальный расчет в коде встроенного языка.
Это самый производительный метод для сложных расчетов. Он позволяет построить детальный материальный отчет по остаткам и движениям материалов для Бухгалтерии 3.0, корректно обрабатывая каждый день периода. Мы разберем его по шагам:
Шаг 1. Получаем в запросе остатки на начало периода и обороты по дням.
ВЫБРАТЬ
ТоварыОстатки.Номенклатура,
ТоварыОстатки.КоличествоОстаток КАК НачальныйОстаток
ИЗ
РегистрНакопления.ТоварыНаСкладах.Остатки(&НачалоПериода, ) КАК ТоварыОстатки
;
ВЫБРАТЬ
ТоварыОбороты.Период,
ТоварыОбороты.Номенклатура,
ТоварыОбороты.КоличествоОборот
ИЗ
РегистрНакопления.ТоварыНаСкладах.Обороты(&НачалоПериода, &КонецПериода, День, ) КАК ТоварыОбороты
ИТОГИ ПО
Номенклатура,
Период
Шаг 2. В модуле (обработки или отчета) обходим период по дням в цикле и рассчитываем остаток.
Посмотрим на примерный алгоритм на встроенном языке:
// Создаем таблицу результатов с колонками Дата, Номенклатура, Остаток
ТаблицаРезультатов = Новый ТаблицаЗначений;
// ... (добавляем колонки)
// Получаем выборку по номенклатуре
ВыборкаНоменклатура = РезультатЗапроса.Выбрать(ОбходРезультатаЗапроса.ПоГруппировкам);
Пока ВыборкаНоменклатура.Следующий() Цикл
ТекущийОстаток = ВыборкаНоменклатура.НачальныйОстаток;
// Цикл по дням месяца
ТекущаяДата = НачалоПериода;
Пока ТекущаяДата <= КонецПериода Цикл
// Поиск оборотов за эту дату в выборке
// (Для оптимизации используем структуру обхода выборки,
// сбрасывая выборку по периодам внутри номенклатуры)
ОборотЗаДень = 0;
// Здесь логика получения оборота из выборки запроса
// Если движение есть - корректируем ТекущийОстаток
ТекущийОстаток = ТекущийОстаток + ОборотЗаДень;
// Записываем строку в результирующую таблицу
НоваяСтрока = ТаблицаРезультатов.Добавить();
НоваяСтрока.Период = ТекущаяДата;
НоваяСтрока.Номенклатура = ВыборкаНоменклатура.Номенклатура;
НоваяСтрока.Остаток = ТекущийОстаток;
ТекущаяДата = ТекущаяДата + 86400; // +1 день
КонецЦикла;
КонецЦикла;
Что делать, если у нас нет производственного календаря (например, пустая или нетиповая конфигурация)? Мы можем сгенерировать последовательность чисел и превратить их в даты прямо в запросе. Это полезный трюк.
Рассмотрим, как создать 1000 дат (около 3 лет) на лету:
ВЫБРАТЬ 0 КАК Цифра ПОМЕСТИТЬ Цифры ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 1 ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 2 ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ 3 ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 4 ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 5 ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ 6 ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 7 ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 8 ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 9;
ВЫБРАТЬ
ДОБАВИТЬКДАТЕ(&НачалоПериода, ДЕНЬ, Единицы.Цифра + Десятки.Цифра * 10 + Сотни.Цифра * 100) КАК Период
ПОМЕСТИТЬ ВТ_СгенерированныйКалендарь
ИЗ
Цифры КАК Единицы,
Цифры КАК Десятки,
Цифры КАК Сотни
ГДЕ
ДОБАВИТЬКДАТЕ(&НачалоПериода, ДЕНЬ, Единицы.Цифра + Десятки.Цифра * 10 + Сотни.Цифра * 100) <= &КонецПериода
Этот код создает таблицу ВТ_СгенерированныйКалендарь, которую затем можно использовать так же, как мы рассматривали в примере с производственным календарем.
Подводя итог, выделим основные сценарии использования:
Используйте эти методы в зависимости от вашей конкретной задачи, и ваши отчеты всегда будут информативными и полными.