Как правильно составить отчет по остаткам и движениям запасов в розничных ценах в СКД?

← На главную

Разработка отчетов по складским остаткам с оценкой их стоимости в розничных ценах — частая задача при автоматизации торговых точек и складов. Нередко разработчики сталкиваются с ситуацией, когда данные отображаются некорректно: товары с нулевыми входящими и исходящими остатками (но с оборотами внутри периода) вовсе не попадают в выборку, при соединении таблиц появляются пустые строки без номенклатуры, либо не рассчитывается итоговая сумма из-за отсутствия цены на нужную дату. Разберем по шагам, почему возникают подобные проблемы в языке запросов 1С и СКД и как построить отчет правильно.

Анализ типовых ошибок в запросе отчета

Рассмотрим структуру запроса, с которой часто начинают разработку. В исходном варианте автор пытался получить начальные остатки, конечные остатки, обороты и цены через раздельные виртуальные таблицы и соединить их с помощью конструкции ПОЛНОЕ СОЕДИНЕНИЕ. Проанализируем, к каким критическим ошибкам это привело:

Превращение полного соединения во внутреннее из-за секции ГДЕ. Если в запросе используется ПОЛНОЕ СОЕДИНЕНИЕ (или ЛЕВОЕ СОЕДИНЕНИЕ), но в предложении ГДЕ указываются условия на поля присоединяемой таблицы (например, ГДЕ ЦеныНоменклатурыСрезПоследнихНачало.ВидЦен = &ВидЦен), СУБД отсекает все записи, где правая таблица вернула NULL. В результате отсекаются все товары, у которых нет остатка или не установлена цена, а соединение фактически работает как ВНУТРЕННЕЕ СОЕДИНЕНИЕ.
Потеря номенклатуры при полном соединении таблиц. При соединении таблицы остатков с таблицей оборотов автор выбрал поле ВТ_Всё.Номенклатура. Если товар пришел и ушел внутри месяца, его не было ни на начало, ни на конец периода. В таблице ВТ_Всё этой позиции нет, поэтому поле ВТ_Всё.Номенклатура возвращает NULL, хотя в таблице ЗапасыОбороты обороты присутствуют.
Неоптимальная архитектура раздельных запросов. Создание отдельных временных таблиц под остатки на начало, остатки на конец и обороты чрезмерно усложняет текст запроса и снижает производительность системы.
Периодичность установки цен. Если розничная цена была введена документом позже периода формирования отчета, срез последних на дату отчета вернет пустое значение (NULL). Умножение количества на NULL приводит к пустой сумме.

Способ 1. Использование виртуальной таблицы ОстаткиИОбороты

Выясним причину усложнения: вместо трех разных виртуальных таблиц одного регистра (Остатки на начало, Остатки на конец, Обороты) регистры накопления с поддержкой остатков предоставляют универсальную таблицу ОстаткиИОбороты. Она рассчитывает начальный остаток, конечный остаток, приход и расход за один проход, причем позиции с оборотами внутри периода (даже при нулевых остатках) гарантированно попадут в результат.

Перенесем условия по структурной единице и виду цен внутрь параметров виртуальных таблиц. Посмотрим на оптимизированный текст запроса:


ВЫБРАТЬ
	ЗапасыОстаткиИОбороты.Номенклатура КАК Номенклатура,
	ЕСТЬNULL(ЦеныНачало.Цена, 0) КАК ЦенаНач,
	ЕСТЬNULL(ЦеныКонец.Цена, 0) КАК ЦенаКон,
	ЗапасыОстаткиИОбороты.КоличествоНачальныйОстаток КАК КоличествоОстатокНач,
	ЗапасыОстаткиИОбороты.КоличествоПриход КАК КоличествоПриход,
	ЗапасыОстаткиИОбороты.КоличествоРасход КАК КоличествоРасход,
	ЗапасыОстаткиИОбороты.КоличествоКонечныйОстаток КАК КоличествоОстатокКон,
	// Суммовые оценки на детальном уровне
	ЗапасыОстаткиИОбороты.КоличествоНачальныйОстаток * ЕСТЬNULL(ЦеныНачало.Цена, 0) КАК СуммаОстатокНач,
	ЗапасыОстаткиИОбороты.КоличествоПриход * ЕСТЬNULL(ЦеныКонец.Цена, 0) КАК СуммаПриход,
	ЗапасыОстаткиИОбороты.КоличествоРасход * ЕСТЬNULL(ЦеныКонец.Цена, 0) КАК СуммаРасход,
	ЗапасыОстаткиИОбороты.КоличествоКонечныйОстаток * ЕСТЬNULL(ЦеныКонец.Цена, 0) КАК СуммаОстатокКон
ИЗ
	РегистрНакопления.Запасы.ОстаткиИОбороты(
		&ДатаНач,
		&ДатаКон,
		Авто,
		Движения,
		СтруктурнаяЕдиница = &СтруктурнаяЕдиница {СтруктурнаяЕдиница.*}
	) КАК ЗапасыОстаткиИОбороты
		ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(
			&ДатаНач,
			ВидЦен = &ВидЦен {ВидЦен.*}
		) КАК ЦеныНачало
		ПО ЗапасыОстаткиИОбороты.Номенклатура = ЦеныНачало.Номенклатура
		ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(
			&ДатаКон,
			ВидЦен = &ВидЦен {ВидЦен.*}
		) КАК ЦеныКонец
		ПО ЗапасыОстаткиИОбороты.Номенклатура = ЦеныКонец.Номенклатура

Рассмотрим подробнее важные моменты этого запроса:

Параметры виртуальных таблиц: Условия СтруктурнаяЕдиница = &СтруктурнаяЕдиница и ВидЦен = &ВидЦен обязательно указываются внутри скобок виртуальных таблиц. Это исключает неявное преобразование соединений и ускоряет выполнение запроса на уровне базы данных.
Обработка пустых значений: Оборачивание цен в функцию ЕСТЬNULL(Цена, 0) гарантирует, что даже при отсутствии установленной цены на дату сумма не превратится в NULL, а будет равна нулю.
Границы дат: Чтобы конечный остаток и обороты за последний день периода посчитались корректно, убедимся, что параметр &ДатаКон включает окончание суток. В СКД для этого в настройках параметров задается выражение: КонецПериода(&ДатаКон, "ДЕНЬ").

Способ 2. Получение актуальной цены при отсутствии среза на дату

Разберем ситуацию, с которой столкнулся автор: если отчет формируется за прошлый период (например, октябрь), а документ УстановкаЦенНоменклатуры был проведен позже (например, в ноябре), срез цен на &ДатаКон вернет пустоту. Если по учетной политике требуется оценить старые движения по текущим или самым поздним зафиксированным розничным ценам, добавим дополнительное соединение со срезом последних без ограничения по дате:


ВЫБРАТЬ
	ЗапасыОстаткиИОбороты.Номенклатура КАК Номенклатура,
	// Если цена на конец периода не найдена, подставляем последнюю актуальную цену из базы
	ЕСТЬNULL(ЦеныКонец.Цена, ЕСТЬNULL(ЦеныАктуальные.Цена, 0)) КАК ЦенаДляОценки,
	ЗапасыОстаткиИОбороты.КоличествоКонечныйОстаток КАК КоличествоОстатокКон,
	ЗапасыОстаткиИОбороты.КоличествоКонечныйОстаток * ЕСТЬNULL(ЦеныКонец.Цена, ЕСТЬNULL(ЦеныАктуальные.Цена, 0)) КАК СуммаОстатокКон
ИЗ
	РегистрНакопления.Запасы.ОстаткиИОбороты(&ДатаНач, &ДатаКон, , , СтруктурнаяЕдиница = &СтруктурнаяЕдиница) КАК ЗапасыОстаткиИОбороты
		ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&ДатаКон, ВидЦен = &ВидЦен) КАК ЦеныКонец
		ПО ЗапасыОстаткиИОбороты.Номенклатура = ЦеныКонец.Номенклатура
		ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(, ВидЦен = &ВидЦен) КАК ЦеныАктуальные
		ПО ЗапасыОстаткиИОбороты.Номенклатура = ЦеныАктуальные.Номенклатура

Настройка ресурсов в Системе компоновки данных (СКД)

Проанализируем расчет итогов. Распространенная ошибка начинающих разработчиков — вычисление суммовых полей умножением цены на количество прямо в тексте запроса с последующим суммированием в ресурсах через агрегатную функцию СУММА(СуммаОстатокКон). Если номенклатура выводится в иерархических группах или считаются общие итоги, это может приводить к ошибкам округления или неверным промежуточным суммам.

Для правильного вычисления итогов в СКД настроим расчет на вкладке «Ресурсы»:

Для количественных полей укажем стандартное суммирование: Сумма(КоличествоОстатокКон), Сумма(КоличествоПриход), Сумма(КоличествоРасход).
Для суммовых полей в поле выражения ресурса укажем расчет от детальных записей: Сумма(КоличествоОстатокКон * ЦенаКон). Это позволит компоновщику макета сначала перемножить цену на количество для каждой конкретной позиции номенклатуры, а затем сложить полученные суммы по группам и общему итогу отчета.
Для поля цены укажем пустое значение в ресурсах (или расчет средней, если это требуется бизнес-логикой), так как простое суммирование цен по строкам не имеет экономического смысла.