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