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