При разработке сложных отчетов в Системе компоновки данных (СКД) программисты часто сталкиваются с ситуацией, когда запрос, прекрасно работающий в консоли запросов, выдает пустой результат или некорректные данные внутри схемы компоновки. Особенно ярко это проявляется при работе с временными таблицами и виртуальными таблицами остатков. Рассмотрим ситуацию, когда мы пытаемся выбрать отрицательные остатки (так называемые «минуса»), поместить их во временную таблицу, а затем использовать эти данные для дальнейшего анализа.
Проанализируем программный код, который вызывает затруднение. Разработчик использует два варианта запроса. В первом варианте он выбирает все измерения (Организация, Вид запасов, Номер ГТД) и получает ожидаемый результат. Во втором варианте он сокращает список полей в финальной выборке, и отчет становится пустым. Посмотрим на структуру этого запроса:
// ПЕРВЫЙ ЗАПРОС - РАБОТАЕТ
ВЫБРАТЬ
ТоварыОрганизацийОстаткиИОбороты.АналитикаУчетаНоменклатуры КАК АналитикаУчетаНоменклатуры,
ТоварыОрганизацийОстаткиИОбороты.Организация КАК Организация,
ТоварыОрганизацийОстаткиИОбороты.ВидЗапасов КАК ВидЗапасов,
ТоварыОрганизацийОстаткиИОбороты.НомерГТД КАК НомерГТД
ПОМЕСТИТЬ Минуса
ИЗ
РегистрНакопления.ТоварыОрганизаций.ОстаткиИОбороты КАК ТоварыОрганизацийОстаткиИОбороты
ГДЕ
ТоварыОрганизацийОстаткиИОбороты.КоличествоКонечныйОстаток < 0
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Минуса.АналитикаУчетаНоменклатуры КАК АналитикаУчетаНоменклатуры,
Минуса.Организация КАК Организация,
Минуса.ВидЗапасов КАК ВидЗапасов,
Минуса.НомерГТД КАК НомерГТД
ИЗ
Минуса КАК Минуса
Во втором случае разработчик оставляет в итоговой выборке только АналитикаУчетаНоменклатуры, полагая, что фильтрация внутри временной таблицы по-прежнему будет работать корректно. Однако результат оказывается пустым. Выясним причину этого поведения.
Главная особенность СКД заключается в том, что она не исполняет запрос «как есть». Перед отправкой на сервер СУБД система выполняет компоновку запроса. Процессор компоновки анализирует настройки отчета (выбранные поля, группировки, отборы) и переписывает текст запроса, удаляя из него всё «лишнее» с точки зрения итогового результата.
Если в настройках отчета (на закладке «Настройки» или в итоговом списке полей выборки) не указаны поля Организация, ВидЗапасов или НомерГТД, СКД решит, что их не нужно выбирать и во временной таблице. В итоге виртуальная таблица ОстаткиИОбороты будет сгруппирована только по полю АналитикаУчетаНоменклатуры.
Проанализируем ситуацию на примере данных:
Если мы выбираем данные с учетом склада, условие КоличествоКонечныйОстаток < 0 найдет строку со Складом А. Если же СКД «оптимизирует» запрос и уберет склад из группировки, система увидит общий остаток по номенклатуре: -5 + 10 = +5. Поскольку 5 больше 0, условие фильтрации во временной таблице не сработает, и строка вообще не попадет в результат. Именно поэтому при удалении «лишних» полей из выборки отчет становится пустым.
Чтобы предотвратить исключение поля из запроса механизмом оптимизации, мы должны явно указать системе, что данное поле необходимо всегда, независимо от того, выбрал его пользователь в настройках или нет. Разберем, как это сделать:
Организация, ВидЗапасов) в списке полей набора данных.После установки этого флага поле будет присутствовать в секции СГРУППИРОВАТЬ ПО итогового SQL-запроса, что обеспечит корректный расчет остатков в разрезе нужных измерений внутри временной таблицы.
Если запрос содержит сложные зависимости во временных таблицах, флаг «Автозаполнение» может работать непредсказуемо. Посмотрим, как решить проблему через ручное управление полями:
Проанализируем ситуацию: когда флаг «Автозаполнение» включен, СКД сама пытается расставить роли и условия. Если его снять, нам придется вручную описать все доступные поля, параметры и отборы. Это гарантирует, что структура запроса останется жесткой, и СКД не выбросит измерения, необходимые для корректного расчета условия ГДЕ КоличествоКонечныйОстаток < 0.
Рассмотрим еще один важный аспект, упомянутый в обсуждении: использование периодов. В исходном запросе автора отсутствуют параметры виртуальной таблицы ОстаткиИОбороты.
// Рекомендуемый подход с параметрами
ИЗ
РегистрНакопления.ТоварыОрганизаций.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, , , ) КАК ТоварыОрганизацийОстаткиИОбороты
Если не указать параметры НачалоПериода и КонецПериода, СКД может подставить значения по умолчанию, что приведет к расчету итогов на некорректную дату. Кроме того, фильтрация через ГДЕ по полю КоличествоКонечныйОстаток выполняется уже после получения данных из виртуальной таблицы. В некоторых случаях более эффективно использовать параметры самой таблицы для предварительной фильтрации, хотя для агрегатных величин (остатков) условие в ГДЕ остается единственным способом отсечения «минусов».
Чтобы окончательно убедиться в причине проблемы, рекомендуется воспользоваться инструментом «Консоль системы компоновки данных». Это внешняя обработка, которая позволяет:
Там вы увидите реальный текст на языке SQL, который 1С отправляет в базу данных. Сравнив текст «неработающего» и «работающего» вариантов, вы сразу заметите отсутствие полей группировки в секции GROUP BY, что и приводит к исчезновению отрицательных остатков.
Для корректной работы с временными таблицами в СКД при поиске отрицательных остатков необходимо: