При разработке отчетов в системе компоновки данных (СКД) программисты часто сталкиваются с задачей объединения данных из нескольких независимых регистров накопления. Типичный пример — необходимость вывести в одном отчете остатки и обороты товаров на складах совместно со сведениями о браке, зарегистрированном в производстве. Дополнительно заказчики часто требуют выводить информацию с детализацией по дням, причем остатки должны отображаться даже на те даты, в которые не было никаких движений.
В рамках этой статьи мы подробно разберем, почему попытки решить эту задачу «в лоб» через сложные SQL-запросы приводят к зависанию системы и вылету платформы 1С, а также рассмотрим правильную архитектуру решения с использованием штатных механизмов СКД: объединения наборов данных, ролей полей и дополнения периодов.
Посмотрим на типичные ошибки, которые допускают начинающие разработчики при решении этой задачи:
ТоварыНаСкладах.ОстаткиИОбороты и БракВПроизводстве через ЛЕВОЕ СОЕДИНЕНИЕ по полям Номенклатура, Склад и ПериодДень данные теряются. Если в конкретный день было движение по браку, но не было движений по складу, левое соединение отбросит запись о браке. Если сделать соединение ПОЛНЫМ, возникнет проблема дублирования строк и некорректного расчета остатков.СУММА(ВЫБОР КОГДА...). На реальных базах данных такой запрос гарантированно приводит к перегрузке процессора, нехватке оперативной памяти и аварийному завершению работы процесса 1С.Для создания быстрого и отказоустойчивого отчета мы должны отказаться от расчетов «нарастающего итога» внутри запроса СУБД и переложить эту задачу на встроенный математический процессор СКД.
---
Если мы хотим использовать один набор данных (запрос), наиболее эффективным способом совместить данные двух регистров является использование конструкции ОБЪЕДИНИТЬ ВСЕ вместо ресурсоемких соединений.
Рассмотрим структуру такого запроса:
ВЫБРАТЬ
ТоварыОстатки.Номенклатура КАК Номенклатура,
ТоварыОстатки.Склад КАК Склад,
ТоварыОстатки.ПериодДень КАК ПериодДень,
ТоварыОстатки.КоличествоНачальныйОстаток КАК НачальныйОстаток,
ТоварыОстатки.КоличествоПриход КАК Приход,
ТоварыОстатки.КоличествоРасход КАК Расход,
0 КАК Брак,
ТоварыОстатки.КоличествоКонечныйОстаток КАК КонечныйОстаток
ПОМЕСТИТЬ ВТ_ОбщиеДанные
ИЗ
РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, День, , ) КАК ТоварыОстатки
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
БракОбороты.Материал,
БракОбороты.Склад,
БракОбороты.ПериодДень,
0,
0,
0,
БракОбороты.КоличествоРасход, // Или Приход в зависимости от логики вашего регистра брака
0
ИЗ
РегистрНакопления.БракВПроизводстве.Обороты(&НачалоПериода, &КонецПериода, День, ) КАК БракОбороты
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_ОбщиеДанные.Номенклатура КАК Номенклатура,
ВТ_ОбщиеДанные.Склад КАК Склад,
ВТ_ОбщиеДанные.ПериодДень КАК ПериодДень,
СУММА(ВТ_ОбщиеДанные.НачальныйОстаток) КАК НачальныйОстаток,
СУММА(ВТ_ОбщиеДанные.Приход) КАК Приход,
СУММА(ВТ_ОбщиеДанные.Расход) КАК Расход,
СУММА(ВТ_ОбщиеДанные.Брак) КАК Брак,
СУММА(ВТ_ОбщиеДанные.КонечныйОстаток) КАК КонечныйОстаток
ИЗ
ВТ_ОбщиеДанные КАК ВТ_ОбщиеДанные
СГРУППИРОВАТЬ ПО
ВТ_ОбщиеДанные.Номенклатура,
ВТ_ОбщиеДанные.Склад,
ВТ_ОбщиеДанные.ПериодДень
Преимущества этого подхода: Мы получаем плоскую выборку без использования тяжелых связей JOIN. СУБД выполняет этот запрос практически мгновенно.
---
Наиболее профессиональный подход — разделить получение данных на два независимых набора данных (два запроса) прямо в схеме компоновки данных, а затем связать их средствами самой платформы.
Выясним, как это сделать пошагово:
ВЫБРАТЬ
Товары.Номенклатура КАК Номенклатура,
Товары.Склад КАК Склад,
Товары.ПериодДень КАК ПериодДень,
Товары.КоличествоНачальныйОстаток КАК КоличествоНачальныйОстаток,
Товары.КоличествоПриход КАК КоличествоПриход,
Товары.КоличествоРасход КАК КоличествоРасход,
Товары.КоличествоКонечныйОстаток КАК КоличествоКонечныйОстаток
ИЗ
РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, День, , ) КАК Товары
ВЫБРАТЬ
Брак.Материал КАК Номенклатура,
Брак.Склад КАК Склад,
Брак.ПериодДень КАК ПериодДень,
Брак.КоличествоОборот КАК КоличествоБрак
ИЗ
РегистрНакопления.БракВПроизводстве.Обороты(&НачалоПериода, &КонецПериода, День, ) КАК Брак
Номенклатура = Номенклатура И Склад = Склад И ПериодДень = ПериодДень---
Чтобы СКД корректно рассчитывала начальные и конечные остатки на уровне группировок (особенно по дням), нам необходимо явно указать роли для полей остатков. Без этого СКД просто просуммирует остатки за все дни, получив некорректные и завышенные в разы цифры.
Разберем, как настроить вкладку «Наборы данных» в конструкторе СКД:
ПериодДень. В колонке «Роль» нажмем кнопку изменения и настроим параметры:
1).День.КоличествоНачальныйОстаток. В роли поля настроим параметры:
Количество.ПериодДень.КоличествоКонечныйОстаток и настроим роль аналогично:
Количество (такое же, как у начального).ПериодДень.Номенклатура и Склад обязательно укажем роль «Измерение».Теперь СКД будет использовать алгоритмы расчета остатков нарастающим итогом. Начальный остаток будет браться на начало первой секунды выбранного дня, а конечный — на конец последней секунды дня.
---
Чтобы решить проблему автора из Сообщения 16 (когда при отсутствии движений за день данные по остаткам просто пропадают из отчета), мы воспользуемся штатным механизмом дополнения периодов в настройках СКД. Нам больше не нужны искусственные таблицы дат!
Выполним следующие настройки:
ПериодДень (или добавим его внутрь группировки по Номенклатуре).ПериодДень и внизу в окне свойств найдем группу параметров «Системные поля / Периоды».День.&НачалоПериода.&КонецПериода.После выполнения этих настроек СКД автоматически сгенерирует строки для каждого календарного дня в выбранном интервале дат. Если в какой-то из дней движений по складу и браку не было, СКД автоматически перенесет конечный остаток предыдущего дня в качестве начального и конечного остатка текущего дня, а показатели Приход, Расход и Брак заполнит нулями. Наш отчет станет точным, красивым и будет работать молниеносно!