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