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