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