При разработке отчетов на Системе Компоновки Данных (СКД) разработчики часто сталкиваются с загадочным поведением: отчет работает корректно, цифры верны, но стоит пользователю добавить новую группировку или, наоборот, убрать детальные записи (иногда полезно знать, как скрыть пустые столбцы), оставив только общие итоги, как эти самые итоги кардинально меняются. Цифры "плывут", и доверие к отчету падает.
В этой статье мы подробно разберем, почему так происходит, как работает механизм оптимизации запросов в 1С и рассмотрим конкретный пример решения проблемы при расчете продаж комплектов номенклатуры.
СКД — это умный механизм. Когда вы пишете запрос в наборе данных, это не всегда тот запрос, который фактически уйдет в СУБД (SQL), поэтому для контроля стоит использовать консоль запросов для управляемых форм. Компоновщик макета анализирует настройки пользователя (выбранные поля, отборы по шаблонам, группировки) и старается "облегчить" запрос.
Как это работает:
ВЫБРАТЬ), но оно не используется в настройках отчета (не выводится, не участвует в отборе, сортировке или условном оформлении), СКД просто вырезает его из итогового запроса.СГРУППИРОВАТЬ ПО, оно также удаляется оттуда.Это сделано для ускорения работы. Однако, если ваша логика вычисления ресурсов (сумм, количества) завязана на конкретную группировку внутри самого текста запроса (например, во временных таблицах), удаление поля ломает эту логику.
Рассмотрим ситуацию, с которой столкнулся автор темы. Есть задача посчитать продажи "Комплектов". В регистре накопления хранятся продажи составляющих. Например, комплект "Костюм" состоит из 1 пиджака и 1 брюк.
Если продано по 1 штуке каждого компонента, значит продан 1 костюм. Если мы просто сложим количество (1 + 1), получим 2, что неверно. Поэтому часто применяют логику: сгруппировать по комплекту и взять МАКСИМУМ(Количество), полагая, что компоненты продаются равными долями.
Изначальный (проблемный) подход выглядел примерно так:
ВЫБРАТЬ
Продажи.НоменклатураНабора,
МАКСИМУМ(Продажи.Количество) КАК Количество
ПОМЕСТИТЬ ВТ_Продажи
ИЗ
РегистрНакопления.Продажи КАК Продажи
СГРУППИРОВАТЬ ПО
Продажи.НоменклатураНабора
В чем здесь ловушка?
Пока пользователь выводит отчет с группировкой по НоменклатураНабора, все работает. СКД видит, что поле нужно, и оставляет его в группировке. Функция МАКСИМУМ отрабатывает в разрезе каждого набора.
Но если пользователь убирает группировку (хочет посмотреть итог по складу или подразделению), СКД "видит", что НоменклатураНабора не нужна для вывода. Она удаляет это поле из SELECT и GROUP BY. Запрос превращается в:
ВЫБРАТЬ
МАКСИМУМ(Продажи.Количество) КАК Количество
...
Теперь МАКСИМУМ берется не по конкретному набору, а по всей таблице (или в разрезе оставшихся полей, например, Подразделения). Естественно, итог получается совершенно другим и непредсказуемым.
Давайте проанализируем варианты, как заставить СКД считать правильно независимо от настроек пользователя.
Самый простой способ — запретить СКД выкидывать поле из запроса. Для этого в схеме компоновки данных на закладке "Наборы данных" для поля НоменклатураНабора нужно поставить галочку в колонке "Обязательное".
Это часто помогает, но не всегда. В сложных запросах с несколькими вложенными временными таблицами контекст "обязательности" может потеряться, или оптимизатор все равно решит перестроить запрос так, что логика нарушится. Именно это произошло в разбираемом случае — галочка не помогла.
Можно обмануть оптимизатор, заставив его думать, что поле участвует в вычислениях. Например, использовать конструкцию в ГДЕ:
ГДЕ
ВЫБОР КОГДА &ПараметрИстина ТОГДА ИСТИНА ИНАЧЕ Продажи.НоменклатураНабора = &ПустаяСсылка КОНЕЦ
Или использовать поле в вычисляемом выражении. Но это "костыльные" методы, которые усложняют поддержку кода.
Можно явно указать состав выбираемых полей с помощью фигурных скобок:
ВЫБРАТЬ
Продажи.НоменклатураНабора,
...
{ВЫБРАТЬ
НоменклатураНабора.*}
Это дает контроль над тем, какие поля доступны пользователю, но не всегда спасает от удаления полей из группировок внутри подзапросов.
Наиболее надежный способ — изменить логику запроса так, чтобы агрегация (суммирование) происходила на уровне Ресурсов СКД, а не внутри текста запроса через СГРУППИРОВАТЬ ПО. Текст запроса должен возвращать детальные данные, устойчивые к изменениям состава полей.
В случае с комплектами, автор темы пришел к верному решению: вместо поиска максимума, нужно вычислить "вес" одной детали и через него получить количество целых комплектов.
Алгоритм решения:
КоличествоДеталей / КоличествоВНаборе.При таком подходе мы получаем корректное число в каждой строке выборки до группировки. Даже если СКД уберет группировку по номенклатуре, математика останется верной, а последующее суммирование (стандартный ресурс СУММА) даст правильный общий итог.
Рассмотрим итоговый код решения, который оказался устойчив к настройкам отчета:
// Шаг 1. Получаем количество составляющих в каждом наборе
ВЫБРАТЬ РАЗЛИЧНЫЕ
СоставНаборов.НоменклатураНабора КАК НоменклатураНабора,
СоставНаборов.ХарактеристикаНабора КАК ХарактеристикаНабора,
СУММА(СоставНаборов.Количество) КАК Количество
ПОМЕСТИТЬ ВТСоставНаборов
ИЗ
РегистрСведений.СоставНаборов КАК СоставНаборов
ГДЕ
// Отбор по нужным наборам (оптимизация)
(СоставНаборов.НоменклатураНабора, СоставНаборов.ХарактеристикаНабора) В
(ВЫБРАТЬ РАЗЛИЧНЫЕ
Продажи.НоменклатураНабора,
Продажи.ХарактеристикаНабора
ИЗ
РегистрНакопления.Продажи КАК Продажи
ГДЕ
Продажи.Период МЕЖДУ &НачалоПериода И &КонецПериода
И Продажи.НоменклатураНабора <> ЗНАЧЕНИЕ(Справочник.Номенклатура.ПустаяСсылка))
СГРУППИРОВАТЬ ПО
СоставНаборов.НоменклатураНабора,
СоставНаборов.ХарактеристикаНабора
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 2. Основной запрос. Делим факт продаж на норму в наборе.
ВЫБРАТЬ
Продажи.НоменклатураНабора КАК Номенклатура,
Продажи.НоменклатураНабора.Артикул КАК Артикул,
Продажи.НоменклатураНабора.КатегорияНоменклатуры КАК КатегорияНоменклатуры,
Продажи.Регистратор КАК ДокументПродажи,
// Вот главная формула:
Продажи.Количество / ЕСТЬNULL(ВТСоставНаборов.Количество, 1) КАК Количество,
Продажи.Подразделение КАК Подразделение
ИЗ
РегистрНакопления.Продажи КАК Продажи
ЛЕВОЕ СОЕДИНЕНИЕ ВТСоставНаборов КАК ВТСоставНаборов
ПО Продажи.НоменклатураНабора = ВТСоставНаборов.НоменклатураНабора
И Продажи.ХарактеристикаНабора = ВТСоставНаборов.ХарактеристикаНабора
ГДЕ
Продажи.Период МЕЖДУ &НачалоПериода И &КонецПериода
И Продажи.НоменклатураНабора <> ЗНАЧЕНИЕ(Справочник.Номенклатура.ПустаяСсылка)
В этом запросе мы не используем жесткую группировку СГРУППИРОВАТЬ ПО в основном тексте запроса к таблице продаж. Мы выполняем строчную операцию деления.
ВТСоставНаборов останется (так как поле ВТСоставНаборов.Количество участвует в вычислении поля Количество). Следовательно, деление произойдет корректно для каждой записи регистра, и общий итог (Сумма) будет верным.Важно: При таком подходе, на вкладке "Ресурсы" в схеме компоновки данных, для поля Количество нужно обязательно указать функцию Сумма(Количество).