Как исправить задвоение итогов при соединении таблиц с разной детализацией в отчетах 1С

При разработке отчетов в системе «1С:Предприятие» программисты часто сталкиваются с классической проблемой: задвоением (или многократным завышением) суммовых показателей в итогах. Эта ситуация возникает тогда, когда в одном запросе соединяются таблицы с разным уровнем детализации (гранулярности) данных. Например, когда детальные строки с номенклатурой (из регистра накопления остатков товаров или резервов) соединяются с данными по взаиморасчетам или оплатам, которые ведутся только в разрезе документов-заказов (без указания конкретных товаров).

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

Выясняем причину: почему задваиваются суммы оплаты и отгрузки?

Рассмотрим детально, как формируется выборка данных на стороне СУБД при левом соединении таблиц с разной детализацией. Представим, что у нас есть два источника данных:

1. Таблица резервов номенклатуры (детализация до товара):
   • Заказ покупателя №1 — Товар «Смартфон» — Количество 1 шт.
   • Заказ покупателя №1 — Товар «Чехол для смартфона» — Количество 1 шт.
2. Таблица взаиморасчетов (детализация только до заказа покупателя):
   • Заказ покупателя №1 — Сумма оплаты 15 000 руб.

Когда мы выполняем левое соединение (LEFT JOIN) таблицы резервов с таблицей оплат по полю ДокЗаказ, СУБД сопоставляет каждую строку первой таблицы со строками второй. Поскольку в первом источнике по Заказу №1 есть две строки (смартфон и чехол), сумма оплаты в 15 000 рублей продублируется для каждой строки товара:

• Строка 1: Заказ №1 — Смартфон — Оплата 15 000 руб.
• Строка 2: Заказ №1 — Чехол — Оплата 15 000 руб.

Если в настройках итогов отчета для ресурса «Сумма оплаты» мы укажем функцию СУММА(СуммаОплаты), система сложит значения всех строк и вернет результат 30 000 рублей вместо фактических 15 000 рублей. Использование функции МАКСИМУМ(СуммаОплаты) частично решает проблему на уровне самого заказа (максимум из 15 000 и 15 000 равен 15 000), но в общих итогах по всему отчету (например, по нескольким заказам или контрагентам) мы получим не общую сумму всех оплат, а просто максимальную оплату из всех документов, что также является грубой ошибкой.

Рассмотрим, как решить эту задачу правильно.

---

Решение №1. Использование нескольких наборов данных в СКД (Связи наборов данных)

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

Разберем по шагам, как это реализовать:

1. Перейдем на вкладку «Наборы данных» в схеме компоновки данных.
2. Создадим первый набор данных — запрос (назовем его НаборДанныхРезервы). В него поместим запрос по регистру накопления резервов и цен номенклатуры:
   Скопировать код

   
   ВЫБРАТЬ РАЗРЕШЕННЫЕ
       ТаблицаРегистра.ДокЗаказ КАК ДокЗаказ,
       ТаблицаРегистра.Номенклатура КАК Номенклатура,
       ТаблицаРегистра.КолНачальныйОстаток КАК КоличествоНачальныйОстаток,
       ТаблицаРегистра.КолПриход КАК КоличествоПриход,
       ТаблицаРегистра.КолРасход КАК КоличествоРасход,
       ТаблицаРегистра.КолКонечныйОстаток КАК КоличествоКонечныйОстаток
   ИЗ
       РегистрНакопления.Резервы.ОстаткиИОбороты(&ДатаНач, &ДатаКон, , , ) КАК ТаблицаРегистра
   

3. Создадим второй набор данных — запрос (назовем его НаборДанныхСуммы). В него поместим запрос по оплатам и отгрузкам без детализации по номенклатуре:
   Скопировать код

   
   ВЫБРАТЬ РАЗРЕШЕННЫЕ
       ДолгиОбороты.Сделка КАК ДокЗаказ,
       ДолгиОбороты.СуммаВалРасход КАК СуммаОплаты,
       ПродажиОбороты.СуммаОборот КАК СуммаОтгрузки
   ИЗ
       РегистрНакопления.Долги.Обороты КАК ДолгиОбороты
       ПОЛНОЕ СОЕДИНЕНИЕ РегистрНакопления.ПродажиОбороты.Обороты КАК ПродажиОбороты
       ПО ДолгиОбороты.Сделка = ПродажиОбороты.ДокЗаказПокупателя
   

4. Перейдем на вкладку «Связи наборов данных». Добавим новую строку связи:
   • Источник связи: НаборДанныхРезервы
   • Приемник связи: НаборДанныхСуммы
   • Условие связи: ДокЗаказ = ДокЗаказ
5. На вкладке «Ресурсы» определим правила расчета итогов:
   • Для ресурсов по количеству зададим выражение: Сумма(КоличествоНачальныйОстаток), Сумма(КоличествоКонечныйОстаток) и т.д.
   • Для ресурсов оплат и отгрузок зададим стандартное выражение: Сумма(СуммаОплаты), Сумма(СуммаОтгрузки).

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

---

Решение №2. Использование расширенных выражений ресурсов СКД

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

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

Выполним следующую настройку на вкладке «Ресурсы»:

1. Найдем поле ресурса СуммаОплаты.
2. В колонке «Выражение» вместо стандартного Сумма(СуммаОплаты) напишем следующее выражение:
   Скопировать код

   
   Сумма(ВычислитьВыражениеСГруппировкойМассив("Максимум(СуммаОплаты)", "ДокЗаказ"))
   

3. Аналогично настроим ресурс для поля СуммаОтгрузки:
   Скопировать код

   
   Сумма(ВычислитьВыражениеСГруппировкойМассив("Максимум(СуммаОтгрузки)", "ДокЗаказ"))
   

Разберем по шагам, как работает это выражение:

• Функция ВычислитьВыражениеСГруппировкойМассив группирует детальные записи отчета по указанному измерению (в нашем случае по документу заказа — ДокЗаказ).
• Для каждого уникального заказа вычисляется выражение, указанное в первом параметре (Максимум(СуммаОплаты)). Так как в рамках одного заказа сумма оплаты везде одинакова, функция максимум вернет корректную сумму оплаты по этому заказу, проигнорировав дублирование строк с разной номенклатурой.
• Результаты расчетов по каждому заказу помещаются в массив.
• Внешняя функция Сумма() складывает все элементы этого массива. На выходе в группировках верхнего уровня (например, по Контрагенту или в Общих итогах) мы получаем математически верную сумму оплат без дублей.

---

Решение №3. Переработка запроса (для Универсального отчета и консоли запросов)

Если отчет строится не на базе СКД, а разрабатывается через Универсальный отчет или обычный построитель, где нет возможности использовать связи наборов данных или расширенные выражения ресурсов, исправление необходимо внести непосредственно в текст SQL-запроса.

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

Проанализируем структуру оптимального запроса:

ВЫБРАТЬ
    ОбъединенныеДанные.ДокЗаказ КАК ДокЗаказ,
    ОбъединенныеДанные.Номенклатура КАК Номенклатура,
    СУММА(ОбъединенныеДанные.КоличествоНачальныйОстаток) КАК КоличествоНачальныйОстаток,
    СУММА(ОбъединенныеДанные.КоличествоКонечныйОстаток) КАК КоличествоКонечныйОстаток,
    СУММА(ОбъединенныеДанные.СуммаОплаты) КАК СуммаОплаты,
    СУММА(ОбъединенныеДанные.СуммаОтгрузки) КАК СуммаОтгрузки
ИЗ
    (
        // Часть 1: Получаем детальные данные по товарам (поля сумм оплат заполняем нулями)
        ВЫБРАТЬ
            ТаблицаРезервов.ДокЗаказ КАК ДокЗаказ,
            ТаблицаРезервов.Номенклатура КАК Номенклатура,
            ТаблицаРезервов.КолНачальныйОстаток КАК КоличествоНачальныйОстаток,
            ТаблицаРезервов.КолКонечныйОстаток КАК КоличествоКонечныйОстаток,
            0 КАК СуммаОплаты,
            0 КАК СуммаОтгрузки
        ИЗ
            РегистрНакопления.Резервы.ОстаткиИОбороты(&ДатаНач, &ДатаКон, , , ) КАК ТаблицаРезервов
        
        ОБЪЕДИНИТЬ ВСЕ
        
        // Часть 2: Получаем агрегированные суммы по заказам (поля номенклатуры заполняем NULL)
        ВЫБРАТЬ
            ДолгиОбороты.Сделка,
            ЗНАЧЕНИЕ(Справочник.Номенклатура.ПустаяСсылка),
            0,
            0,
            ДолгиОбороты.СуммаВалРасход,
            ПродажиОбороты.СуммаОборот
        ИЗ
            РегистрНакопления.Долги.Обороты(&ДатаНач, &ДатаКон, , ) КАК ДолгиОбороты
            ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ПродажиОбороты.Обороты(&ДатаНач, &ДатаКон, , ) КАК ПродажиОбороты
                ПО ДолгиОбороты.Sделка = ПродажиОбороты.ДокЗаказПокупателя
    ) КАК ОбъединенныеДанные

СГРУППИРОВАТЬ ПО
    ОбъединенныеДанные.ДокЗаказ,
    ОбъединенныеДанные.Номенклатура

Преимущества этого подхода:

• Отсутствие декартова произведения: Детальные строки номенклатуры и суммовые показатели по заказу находятся в разных физических строках временного результата. Они объединяются по вертикали, а не по горизонтали.
• Абсолютная точность итогов: При группировке функция СУММА по полям оплат и отгрузок сработает идеально точно, так как строки с номенклатурой содержат нули в этих полях и никак не влияют на итоговый результат.
• Универсальность: Такой запрос корректно работает в любых движках отчетов, включая классический построитель отчетов 1С, консоль запросов и любые внешние обработчики данных.

Выбор конкретного решения зависит от архитектуры вашего отчета. Если отчет строится в СКД — отдайте предпочтение Решению №1 или Решению №2. Если отчет строится на классических механизмах или требует максимальной переносимости — используйте Решение №3.