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