Как избежать задвоения строк и сумм при соединении табличных частей документов в отчетах 1С

← На главную

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

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

Разбор проблемы: почему возникают дубли в запросах

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

Использование ВЫБРАТЬ РАЗЛИЧНЫЕ или СГРУППИРОВАТЬ ПО внутри одного запроса часто не приносит результата, так как строки различаются ссылками на документы реализации или датами отгрузки. Для СУБД это разные записи, и она обязана вывести их все.

Оптимизация связей и производительность запроса

Прежде чем переходить к архитектурным изменениям, обратим внимание на важный аспект написания запросов. Рассмотрим пример некорректной связи:


// Плохая практика
ЛЕВОЕ СОЕДИНЕНИЕ ТаблЗП КАК ТаблЗП 
ПО ТаблЗаказы.ЗаказКлиента.Ссылка = ТаблЗП.ЗаказПоставщику.ДокументОснование.Ссылка

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


// Хорошая практика
ЛЕВОЕ СОЕДИНЕНИЕ ТаблЗП КАК ТаблЗП 
ПО ТаблЗаказы.ЗаказКлиента = ТаблЗП.ДокументОснованиеЗП

Решение: Использование нескольких наборов данных в СКД

Самый надежный способ победить задвоение итогов в сложных иерархических отчетах — это отказ от соединения всех таблиц в одном огромном запросе. Вместо этого мы создадим несколько наборов данных (запросов) внутри схемы компоновки данных и свяжем их на закладке "Связи наборов данных".

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


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

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    КПТовары.Ссылка КАК КоммерческоеПредложение,
    КПТовары.Ссылка.СуммаДокумента КАК СуммаДокументаКП,
    КПТовары.Номенклатура КАК НоменклатураКП,
    СУММА(ВЫБОР
        КОГДА КПТовары.Ссылка.Валюта.Код = "643" ТОГДА КПТовары.СуммаСНДС
        ИНАЧЕ КПТовары.СуммаСНДС * Курсы.Курс
    КОНЕЦ) КАК СуммаРубСНдсКП
ИЗ
    Документ.КоммерческоеПредложениеКлиенту.Товары КАК КПТовары
ЛЕВОЕ СОЕДИНЕНИЕ Курсы КАК Курсы ПО КПТовары.Ссылка.Валюта = Курсы.Валюта 
    И (НАЧАЛОПЕРИОДА(КПТовары.Ссылка.Дата, ДЕНЬ) = Курсы.Период)
ГДЕ
    КПТовары.Ссылка.Проведен
СГРУППИРОВАТЬ ПО
    КПТовары.Ссылка,
    КПТовары.Номенклатура

Далее создадим второй набор данных для Заказов клиентов. Обратите внимание, что мы не соединяем его с КП в самом тексте запроса:


ВЫБРАТЬ
    ЗаказТовары.Ссылка КАК ЗаказКлиента,
    ЗаказТовары.Ссылка.ДокументОснование КАК ДокументОснованиеЗК,
    ЗаказТовары.Номенклатура КАК НоменклатураЗК,
    СУММА(ЗаказТовары.СуммаСНДС) КАК СуммаТовараСндсЗК
ИЗ
    Документ.ЗаказКлиента.Товары КАК ЗаказТовары
ГДЕ
    ЗаказТовары.Ссылка.Проведен
СГРУППИРОВАТЬ ПО
    ЗаказТовары.Ссылка,
    ЗаказТовары.Номенклатура,
    ЗаказТовары.Ссылка.ДокументОснование

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

Настройка связей наборов данных

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

Источник связи: НаборКП, Приемник: НаборЗаказы.
Условие связи: КоммерческоеПредложение = ДокументОснованиеЗК И НоменклатураКП = НоменклатураЗК.

При такой архитектуре СКД сначала получает данные по КП, а затем для каждой строки КП делает отдельную выборку из набора Заказов. Это позволяет избежать классического "раздувания" таблицы на уровне SQL-запроса. Если в детальных записях строки все равно двоятся (что логично, если реализаций несколько), то Итоги по ресурсам основного документа (КП) будут рассчитываться корректно, так как СКД понимает иерархию данных.

Работа с ресурсами: как не допустить суммирования дублей

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

Максимум(СуммаДокументаКП) — если мы группируем по конкретному документу КП, это вернет его верную сумму, даже если внутри группировки 10 строк реализаций.

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

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

Разберем, как это работает: функция собирает массив максимальных сумм для каждого уникального КП, а затем складывает элементы этого массива. Это гарантирует математическую точность отчета.

Рекомендации по архитектуре: документы или регистры?

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

Рассмотрим преимущества перехода на регистры:

Данные в регистрах (например, "Заказы клиентов", "Выручка и продажи") уже агрегированы по измерениям.
Индексация регистров позволяет получать данные значительно быстрее, чем при сканировании табличных частей документов.
Регистры хранят только движения проведенных документов, что избавляет от необходимости постоянно проверять свойство Проведен в запросе.

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

Заключение

Мы выяснили, что для создания корректного отчета по цепочке документов без задвоений необходимо:

Разделять запросы на отдельные наборы данных в СКД.
Связывать их по ссылкам и номенклатуре на закладке связей СКД, а не через LEFT JOIN в тексте запроса.
Использовать агрегатные функции Максимум или ВычислитьВыражениеСГруппировкойМассив в ресурсах для корректного подсчета итогов.
Схлопывать данные по номенклатуре во временных таблицах перед передачей в основной набор, если в одном документе возможны дубли строк.

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