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