Почему в консоли запросов и отчете СКД разный результат, и как исправить пустой отчет?

Каждый разработчик 1С рано или поздно сталкивается с удивительной ситуацией: один и тот же запрос, выполненный в обычной консоли запросов, возвращает корректные и ожидаемые данные, но стоит запустить его в отчете на базе Системы компоновки данных (СКД) — и на выходе мы получаем пустую таблицу или неверные суммы. Особенно ярко эта проблема проявляется, когда мы начинаем отключать или удалять определенные группировки в настройках отчета (например, убираем измерение Сотрудник из вывода).

В этой статье мы подробно разберем, почему так происходит, залезем "под капот" оптимизатора СКД, выясним истинную причину расхождения результатов и рассмотрим сразу несколько профессиональных способов решения этой проблемы.

В чем суть проблемы: анализируем исходный запрос

Рассмотрим практическую задачу. Нам необходимо получить отрицательные начисления по сотрудникам в разрезе статей расходов и статей финансирования. Для этого в схеме компоновки данных (или в консоли) пишется пакетный запрос. В первой временной таблице ВТМинус мы собираем обороты, группируем их по сотрудникам и статьям, а затем накладываем условие отбора через предложение ИМЕЮЩИЕ, чтобы оставить только отрицательные суммы:

ВЫБРАТЬ
    БухучетНачисленияУдержанияПоСотрудникамОбороты.Сотрудник КАК Сотрудник,
    БухучетНачисленияУдержанияПоСотрудникамОбороты.СтатьяРасходов КАК СтатьяРасходов,
    БухучетНачисленияУдержанияПоСотрудникамОбороты.СтатьяФинансирования КАК СтатьяФинансирования,
    СУММА(БухучетНачисленияУдержанияПоСотрудникамОбороты.СуммаОборот) КАК СуммаОборот
ПОМЕСТИТЬ ВТМинус
ИЗ
    РегистрНакопления.БухучетНачисленияУдержанияПоСотрудникам.Обороты(&НачПер, &КонПер, Период, ) КАК БухучетНачисленияУдержанияПоСотрудникамОбороты
ГДЕ
    (БухучетНачисленияУдержанияПоСотрудникамОбороты.НачислениеУдержание ССЫЛКА ПланВидовРасчета.Начисления
    ИЛИ БухучетНачисленияУдержанияПоСотрудникамОбороты.НачислениеУдержание = ЗНАЧЕНИЕ(Перечисление.ВидыОсобыхНачисленийИУдержаний.ДоговорРаботыУслуги))
СГРУППИРОВАТЬ ПО
    БухучетНачисленияУдержанияПоСотрудникамОбороты.Сотрудник,
    БухучетНачисленияУдержанияПоСотрудникамОбороты.СтатьяРасходов,
    БухучетНачисленияУдержанияПоСотрудникамОбороты.СтатьяФинансирования
ИМЕЮЩИЕ
    СУММА(БухучетНачисленияУдержанияПоСотрудникамОбороты.СуммаОборот) < 0;

Во втором (результирующем) запросе пакета мы хотим вывести общие суммы по статьям, уже без детализации до конкретного человека:

ВЫБРАТЬ
    СУММА(ВТМинус.СуммаОборот) КАК СуммаОборот,
    ВТМинус.СтатьяФинансирования КАК СтатьяФинансирования,
    ВТМинус.СтатьяРасходов КАК СтатьяРасходов
ИЗ
    ВТМинус КАК ВТМинус
СГРУППИРОВАТЬ ПО
    ВТМинус.СтатьяФинансирования,
    ВТМинус.СтатьяРасходов

В консоли запросов этот пакет работает идеально. Он находит сотрудников с отрицательными суммами, формирует по ним временную таблицу, а затем сворачивает эти суммы до статей. Но в отчете СКД, как только пользователь убирает поле Сотрудник из группировок или выбранных полей компоновщика, отчет становится абсолютно пустым.

Давайте разберемся, почему СКД ведет себя столь непредсказуемо на первый взгляд.

Как работает оптимизатор СКД (Механизм "Автозаполнения")

Главное, что нужно усвоить: СКД никогда не выполняет ваш запрос в исходном виде. Перед отправкой запроса к СУБД компоновщик макета СКД анализирует текущие настройки отчета (какие поля выбраны пользователем, какие используются группировки, отборы, сортировки и условное оформление) и динамически перестраивает текст запроса.

Если на вкладке "Наборы данных" у вас включено свойство "Автозаполнение", СКД берет на себя управление полями. Алгоритм оптимизатора выглядит следующим образом:

1. Компоновщик видит, что в результирующей таблице настроек отчета поле Сотрудник отсутствует (оно не выводится на экран, по нему нет группировок и по нему не заданы отборы).
2. В целях повышения производительности и экономии памяти сервера СУБД, оптимизатор решает полностью исключить это "лишнее" поле из выборки.
3. Оптимизатор вырезает поле Сотрудник не только из финального запроса, но и "протягивает" это исключение назад во все временные таблицы пакета, включая нашу ВТМинус.

В результате работы этого механизма исходный запрос первой временной таблицы превращается для СУБД в следующий вид:

ВЫБРАТЬ
    // Поле "Сотрудник" полностью исключено из выборки!
    БухучетНачисленияУдержанияПоСотрудникамОбороты.СтатьяРасходов КАК СтатьяРасходов,
    БухучетНачисленияУдержанияПоСотрудникамОбороты.СтатьяФинансирования КАК СтатьяФинансирования,
    СУММА(БухучетНачисленияУдержанияПоСотрудникамОбороты.СуммаОборот) КАК СуммаОборот
ПОМЕСТИТЬ ВТМинус
ИЗ
    РегистрНакопления.БухучетНачисленияУдержанияПоСотрудникам.Обороты(&НачПер, &КонПер, Период, ) КАК БухучетНачисленияУдержанияПоСотрудникамОбороты
// ... условия ГДЕ ...
СГРУППИРОВАТЬ ПО
    // Группировка по Сотруднику также исчезла!
    БухучетНачисленияУдержанияПоСотрудникамОбороты.СтатьяРасходов,
    БухучетНачисленияУдержанияПоСотрудникамОбороты.СтатьяФинансирования
ИМЕЮЩИЕ
    СУММА(БухучетНачисленияУдержанияПоСотрудникамОбороты.СуммаОборот) < 0;

Проанализируем последствия этой оптимизации. Из-за удаления сотрудника группировка теперь происходит только по статьям. Соответственно, условие ИМЕЮЩИЕ СУММА(СуммаОборот) < 0 теперь рассчитывается не для каждого сотрудника в отдельности, а суммарно по всей статье.

Поскольку общие обороты по статьям финансирования и расходов в организации почти всегда положительные (даже если у пары сотрудников там "минусы"), условие ИМЕЮЩИЕ отсекает абсолютно все записи. Временная таблица ВТМинус оказывается пустой, и финальный отчет не выводит ничего.

Для диагностики таких проблем рекомендуется использовать специализированные инструменты разработчика — например, "Консоль компоновки данных". С ее помощью можно открыть макет компоновки и увидеть реальный XML-запрос, отправляемый платформой на сервер СУБД.

---

Варианты решения проблемы

Рассмотрим несколько способов исправить это поведение — от простых настроек в конструкторе СКД до управления языком запросов.

Способ 1. Установка роли "Обязательное" для поля

Это самый простой, элегантный и рекомендуемый способ решения проблемы. Мы должны явно указать СКД, что поле Сотрудник является ключевым для логики расчетов и его ни в коем случае нельзя исключать из временных таблиц, даже если пользователь не выводит его в отчет.

Пошаговая инструкция:

1. Откройте схему компоновки данных вашего отчета.
2. Перейдите на вкладку "Наборы данных".
3. В таблице полей найдите поле Сотрудник.
4. Найдите колонку "Роль" и нажмите кнопку выбора (три точки) в строке этого поля.
5. В открывшемся окне редактирования роли поля установите флажок "Обязательное".
6. Нажмите "ОК" и сохраните отчет.

Что произошло: Теперь оптимизатор СКД видит жесткое ограничение. Поле помечено как обязательное, поэтому система обязана сохранить его во всех временных таблицах пакета запросов и выполнить по нему группировку. Логика расчета условия ИМЕЮЩИЕ останется неизменной, и данные будут рассчитаны верно.

Обратите внимание: Если это поле участвует в объединении нескольких запросов (ОБЪЕДИНИТЬ ВСЕ), вам может потребоваться сопоставить его во всех частях объединения. Если в других таблицах сотрудника нет, придется передавать туда значение НЕОПРЕДЕЛЕНО или NULL, что не всегда удобно.

Способ 2. Использование разностных синонимов (Программная хитрость)

Если вы не хотите делать поле сотрудника доступным для пользователя в финальном списке полей или накладывать на него жесткую роль обязательного поля во всем отчете, можно прибегнуть к следующей хитрости с синонимами полей запроса.

Переименуйте поле сотрудника во временной таблице так, чтобы СКД не могла напрямую связать его с полями финальной выборки. Например, назовем его СотрудникДляГруппировки:

ВЫБРАТЬ
    БухучетНачисленияУдержанияПоСотрудникамОбороты.Сотрудник КАК СотрудникДляГруппировки,
    // ... остальные поля ...
ПОМЕСТИТЬ ВТМинус

При этом в результирующем запросе пакета мы это поле вообще не выбираем. СКД анализирует структуру финальной выборки и не находит в ней поля СотрудникДляГруппировки. В большинстве версий платформы оптимизатор СКД не умеет "дотягиваться" до неиспользуемых промежуточных полей с уникальными именами, которые отсутствуют в финальном выводе, и оставляет структуру временной таблицы нетронутой. Группировка по сотрудникам внутри ВТМинус сохранится.

Способ 3. Отключение автозаполнения и ручное декларирование полей

Если вы хотите полностью контролировать структуру своих запросов и не полагаться на "умные" алгоритмы платформы, можно полностью отключить автозаполнение.

Реализация по шагам:

1. На вкладке "Наборы данных" снимите галочку "Автозаполнение" возле вашего запроса.
2. После этого СКД перестанет автоматически сканировать текст запроса и формировать список доступных полей.
3. Вам придется вручную заполнить таблицу "Поля", добавив туда только те поля, которые действительно нужны пользователю для отборов или вывода (например, СтатьяРасходов, СтатьяФинансирования, СуммаОборот).
4. Поле Сотрудник вообще не нужно объявлять в списке доступных полей СКД.

Результат: Поскольку автозаполнение отключено, СКД будет выполнять ваш пакет запросов "как есть" — именно в том виде, в котором он написан в текстовом поле. Никакие оптимизации и удаления полей из временных таблиц происходить не будут. Однако помните, что при этом вы теряете возможность динамического наложения отборов СКД на внутренние таблицы (системе сложнее оптимизировать скорость выполнения).

Способ 4. Расширение языка запросов СКД (Фигурные скобки {})

Язык запросов СКД поддерживает специальные конструкции в фигурных скобках, которые позволяют явно управлять поведением компоновщика. Мы можем жестко зафиксировать группировку в тексте запроса, используя возможности расширения.

Например, мы можем указать обязательную группировку непосредственно в секции СГРУППИРОВАТЬ ПО:

СГРУППИРОВАТЬ ПО
    БухучетНачисленияУдержанияПоСотрудникамОбороты.СтатьяРасходов,
    БухучетНачисленияУдержанияПоСотрудникамОбороты.СтатьяФинансирования,
    {БухучетНачисленияУдержанияПоСотрудникамОбороты.Сотрудник КАК Сотрудник}

Этот синтаксис позволяет гибко управлять тем, как СКД будет модифицировать запрос при различных настройках компоновки.

---

Анализ альтернативных решений: почему не стоит использовать внешние наборы данных?

Иногда разработчики, столкнувшись с "избыточным умом" оптимизатора СКД, решают проблему радикально: полностью отказываются от выполнения запроса внутри схемы компоновки данных. Они выносят этот запрос в программный код модуля, выгружают результат в Таблицу Значений (ТЗ) и передают ее в СКД через Внешний набор данных.

Да, этот подход гарантирует стабильность структуры данных. СКД никак не сможет "испортить" или изменить готовую таблицу значений в оперативной памяти. Однако у этого решения есть критические недостатки, о которых необходимо помнить:

• Высокая нагрузка на оперативную память: Все данные по оборотам сотрудников будут сначала полностью считаны из базы данных СУБД на сервер приложений 1С. Если база крупная, создание огромной таблицы значений в оперативной памяти может привести к нехватке ресурсов и замедлению работы всей системы.
• Потеря оптимизации отборов: Если пользователь установит отбор по конкретной статье расходов в настройках отчета СКД, при классическом подходе этот отбор автоматически "провалится" внутрь SQL-запроса на уровень базы данных. СУБД выберет лишь малую часть записей. В случае же с внешним набором данных сервер сначала выгрузит абсолютно ВСЕ записи в ТЗ, передаст их в СКД, и только потом СКД отфильтрует их в памяти. Это крайне неэффективно.

Таким образом, использование внешних наборов данных оправдано только в крайних случаях, когда данные действительно невозможно получить одним запросом к базе (например, требуется сложная обработка алгоритмами на встроенном языке).

Резюме

Причиной разного поведения запроса в консоли и в отчете СКД является динамическая оптимизация компоновщика макета. Чтобы избежать пустых результатов при изменении группировок, следуйте простым правилам:

1. Для полей во временных таблицах, от которых зависит расчет условий (таких как ИМЕЮЩИЕ или соединения таблиц), всегда устанавливайте флаг "Обязательное" в роли поля на вкладке "Наборы данных".
2. Если логика отчета очень сложная, используйте отключение Автозаполнения и настраивайте состав доступных полей вручную.
3. Избегайте необоснованного выноса расчетов во внешние таблицы значений, чтобы не нагружать сервер и не лишать пользователей преимуществ динамической фильтрации данных СКД.