Почему в отчетах СКД неверно считаются итоги и пропадают строки с одинаковыми суммами?

← На главную

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

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

Суть проблемы и странное поведение СКД

Рассмотрим практическую ситуацию. Разработчик создает отчет, который собирает данные из табличных частей "Товары" и "Услуги" документа "Поступление товаров и услуг" (ПТУ). Отчет должен выводить номенклатуру, суммы и рассчитывать итоги по документу.

Если в табличной части одного документа присутствуют несколько строк с одинаковой номенклатурой и одинаковой суммой (например, две строки с кирпичами: в одной 5 штук по 10 рублей, в другой — 10 штук по 5 рублей; в обоих случаях сумма строки равна 50 рублей), то в отчете они группируются в одну строку, но их сумма не складывается! Итог по документу считается так, как будто одной из строк физически не существовало.

При этом, если изменить цену или количество в одной из строк так, чтобы суммы отличались (например, 51 рубль и 50 рублей), расчет итогов моментально становится верным. Это наталкивает на мысль о неявной работе механизма ВЫБРАТЬ РАЗЛИЧНЫЕ.

Анализ исходного запроса разработчика

Проанализируем текст запроса, который привел к такому поведению схемы компоновки данных:


ВЫБРАТЬ
    ПоступлениеТоваровУслугТовары.Ссылка КАК Ссылка,
    ВЫБОР 
        КОГДА ПоступлениеТоваровУслугТовары.Ссылка.СуммаВключаетНДС ТОГДА ПоступлениеТоваровУслугТовары.Сумма 
        ИНАЧЕ ПоступлениеТоваровУслугТовары.Сумма + ПоступлениеТоваровУслугТовары.СуммаНДС 
    КОНЕЦ КАК Сумма,
    ПоступлениеТоваровУслугТовары.Ссылка.СуммаДокумента КАК СуммаДокумента,
    ПоступлениеТоваровУслугТовары.Номенклатура КАК Номенклатура,
    ПоступлениеТоваровУслугТовары.СтавкаНДС,
    ПоступлениеТоваровУслугТовары.СуммаНДС КАК СуммаНДС
ИЗ
    Документ.ПоступлениеТоваровУслуг.Товары КАК ПоступлениеТоваровУслугТовары 
ГДЕ 
    ПоступлениеТоваровУслугТовары.Ссылка.Дата МЕЖДУ &НачалоПериода И &КонецПериода

ОБЪЕДИНИТЬ

ВЫБРАТЬ
    ПоступлениеТоваровУслугУслуги.Ссылка,
    ВЫБОР 
        КОГДА ПоступлениеТоваровУслугУслуги.Ссылка.СуммаВключаетНДС ТОГДА ПоступлениеТоваровУслугУслуги.Сумма 
        ИНАЧЕ ПоступлениеТоваровУслугУслуги.Сумма + ПоступлениеТоваровУслугУслуги.СуммаНДС 
    КОНЕЦ,
    ПоступлениеТоваровУслугУслуги.Ссылка.СуммаДокумента КАК СуммаДокумента,
    ПоступлениеТоваровУслугУслуги.Номенклатура,
    ПоступлениеТоваровУслугУслуги.СтавкаНДС,
    ПоступлениеТоваровУслугУслуги.СуммаНДС
ИЗ
    Документ.ПоступлениеТоваровУслуг.Услуги КАК ПоступлениеТоваровУслугУслуги 
ГДЕ 
    ПоступлениеТоваровУслугУслуги.Ссылка.Дата МЕЖДУ &НачалоПериода И &КонецПериода

Обратим внимание на оператор связи между таблицей товаров и таблицей услуг. В запросе используется ключевое слово ОБЪЕДИНИТЬ (без ключевого слова ВСЕ). Именно в этом кроется первопричина проблемы.

Теория: В чем разница между ОБЪЕДИНИТЬ и ОБЪЕДИНИТЬ ВСЕ?

В языке запросов платформы 1С:Предприятие (как и в классическом языке SQL) существуют два ключевых слова для объединения результатов нескольких запросов:

ОБЪЕДИНИТЬ (UNION). Этот оператор производит объединение выборок и автоматически удаляет все дублирующиеся строки из результирующего набора данных. Чтобы определить дубликат, СУБД сравнивает значения абсолютно всех полей в строках. Если все поля совпадают, в итоговом результате остается только одна строка. Процесс поиска и удаления дубликатов требует дополнительных вычислительных ресурсов сервера базы данных.
ОБЪЕДИНИТЬ ВСЕ (UNION ALL). Данный оператор просто склеивает результаты запросов "один под другим". Он не производит никакого анализа на уникальность строк и сохраняет все дубликаты. Этот оператор работает значительно быстрее, так как СУБД не тратит ресурсы на сортировку и сверку данных.

В нашем случае, когда у двух строк из табличной части ПТУ совпали номенклатура, ставка НДС, сумма НДС и итоговая сумма, СУБД при выполнении операции ОБЪЕДИНИТЬ посчитала эти строки идентичными и просто вырезала одну из них из результата запроса. В СКД пришла только одна строка, поэтому и итоговая сумма уменьшилась ровно на величину "пропавшей" строки.

Почему помогло добавление Номера Строки с ролью "Обязательное"?

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

Номер строки в табличной части документа всегда уникален (например, 1 и 2). Когда мы добавляем уникальное поле в выборку, строки для СУБД перестают быть идентичными, даже если все остальные реквизиты (номенклатура, сумма) полностью совпадают. Оператор ОБЪЕДИНИТЬ больше не может их схлопнуть.

Однако простого добавления поля в текст запроса недостаточно. Здесь вступает в силу механизм оптимизации запросов СКД.

Как работает механизм оптимизации запросов в СКД?

Система компоновки данных — это интеллектуальный механизм. Перед тем как отправить финальный запрос к СУБД, СКД анализирует текущие настройки отчета, сделанные пользователем. Она проверяет:

Какие поля выбраны для вывода на экран?
По каким полям выполняются группировки?
Какие поля участвуют в отборах или условном оформлении?

Если какое-то поле объявлено в запросе, но не используется в настройках схемы (например, мы добавили НомерСтроки, но не вывели его в колонки отчета и не группируем по нему), СКД в целях оптимизации удаляет это поле из итогового SQL-запроса.

Если бы разработчик не установил роль "Обязательное", СКД просто "вырезала" бы НомерСтроки из текста запроса при формировании отчета. В СУБД ушел бы запрос без номеров строк, и оператор ОБЪЕДИНИТЬ снова исключил бы дублирующие строки по суммам.

Установка флага "Обязательное" (или свойства "Использовать всегда") принудительно отключает оптимизацию СКД для конкретного поля. Мы даем системе жесткую команду: "Это поле должно быть в SQL-запросе всегда, вне зависимости от настроек пользователя". За счет этого уникальный номер строки доходил до СУБД, предотвращая схлопывание данных оператором ОБЪЕДИНИТЬ.

Правильное решение проблемы

Хотя добавление поля НомерСтроки с ролью "Обязательное" решает проблему, этот метод является неоптимальным "костылем". Он заставляет базу данных выполнять лишнюю работу: сначала СУБД пытается сравнить и отсортировать строки (из-за оператора ОБЪЕДИНИТЬ), а сервер приложений передает избыточные технические данные, которые не выводятся пользователю.

Правильным и лаконичным решением является замена оператора объединения.

Шаг 1. Исправление текста запроса

Заменим ключевое слово ОБЪЕДИНИТЬ на ОБЪЕДИНИТЬ ВСЕ в тексте нашего запроса. Это позволит СУБД моментально склеить таблицы без выполнения дорогостоящих проверок на дубликаты.


ВЫБРАТЬ
    ПоступлениеТоваровУслугТовары.Ссылка КАК Ссылка,
    ВЫБОР 
        КОГДА ПоступлениеТоваровУслугТовары.Ссылка.СуммаВключаетНДС ТОГДА ПоступлениеТоваровУслугТовары.Сумма 
        ИНАЧЕ ПоступлениеТоваровУслугТовары.Сумма + ПоступлениеТоваровУслугТовары.СуммаНДС 
    КОНЕЦ КАК Сумма,
    ПоступлениеТоваровУслугТовары.Ссылка.СуммаДокумента КАК СуммаДокумента,
    ПоступлениеТоваровУслугТовары.Номенклатура КАК Номенклатура,
    ПоступлениеТоваровУслугТовары.СтавкаНДС,
    ПоступлениеТоваровУслугТовары.СуммаНДС КАК СуммаНДС
ИЗ
    Документ.ПоступлениеТоваровУслуг.Товары КАК ПоступлениеТоваровУслугТовары 
ГДЕ 
    ПоступлениеТоваровУслугТовары.Ссылка.Дата МЕЖДУ &НачалоПериода И &КонецПериода

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
    ПоступлениеТоваровУслугУслуги.Ссылка,
    ВЫБОР 
        КОГДА ПоступлениеТоваровУслугУслуги.Ссылка.СуммаВключаетНДС ТОГДА ПоступлениеТоваровУслугУслуги.Сумма 
        ИНАЧЕ ПоступлениеТоваровУслугУслуги.Сумма + ПоступлениеТоваровУслугУслуги.СуммаНДС 
    КОНЕЦ,
    ПоступлениеТоваровУслугУслуги.Ссылка.СуммаДокумента,
    ПоступлениеТоваровУслугУслуги.Номенклатура,
    ПоступлениеТоваровУслугУслуги.СтавкаНДС,
    ПоступлениеТоваровУслугУслуги.СуммаНДС
ИЗ
    Документ.ПоступлениеТоваровУслуг.Услуги КАК ПоступлениеТоваровУслугУслуги 
ГДЕ 
    ПоступлениеТоваровУслугУслуги.Ссылка.Дата МЕЖДУ &НачалоПериода И &КонецПериода

Шаг 2. Очистка неиспользуемых полей

Теперь мы можем полностью удалить техническое поле НомерСтроки из запроса и настроек СКД (если оно действительно не нужно пользователю в отчете). Данные больше не будут искажаться, а сам отчет станет работать быстрее за счет оптимизации планов запросов СУБД.

Таким образом, всегда используйте ОБЪЕДИНИТЬ ВСЕ по умолчанию, если перед вами не стоит осознанная и конкретная задача удалить абсолютно идентичные строки из разных таблиц.