Как исправить некорректное суммирование итогов в отчете 1С на СКД при использовании левого соединения?

← На главную

При разработке отчетов в системе 1С на базе Системы Компоновки Данных (СКД) мы часто сталкиваемся с необходимостью объединения информации из нескольких источников. Одним из распространенных подходов является использование соединений (например, ЛЕВОЕ СОЕДИНЕНИЕ) непосредственно в запросе к базе данных. Однако такой подход может привести к неожиданным и некорректным результатам, особенно при расчете итоговых значений по некоторым колонкам.

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

Выявление проблемы: некорректные итоги при соединениях в запросе

Предположим, у нас есть данные, которые выглядят следующим образом после выполнения запроса:

СтатьяКалькуляции    КоличествоВыпуск    КоличествоВыручка    СуммаВыручки    ЦенаВыручки    СебестоимостьРеал    СтоимостьЗатратОбщая    СтоимостьЗатрат    СтоимостьНаЕдиницу    КоличествоВыручкаОбщая    СуммаВыручкиОбщая
Амортизация оборудования    42    385    4 294 865,32    11 155,49433766    2 964 020,23    145 314,33    1 332 048,025    3 459,865    385    4 294 865,32
Амортизация оборудования    142    385    4 294 865,32    11 155,49433766    2 964 020,23    145 314,33    393 986,03556235    1 023,34035211    385    4 294 865,32
Амортизация оборудования    108    385    4 294 865,32    11 155,49433766    2 964 020,23    145 314,33    518 018,6763906    1 345,50305556    385    4 294 865,32
...

Мы видим, что для каждой строки, связанной с разным КоличествоВыпуск, такие поля как КоличествоВыручка, СуммаВыручки, ЦенаВыручки, СебестоимостьРеал, КоличествоВыручкаОбщая, СуммаВыручкиОбщая имеют одинаковые значения. Это происходит потому, что эти поля берутся из одной "общей" таблицы, которая присоединяется к "детализированной" таблице с помощью ЛЕВОГО СОЕДИНЕНИЯ. Если "детализированная" таблица имеет несколько записей для одного и того же элемента из "общей" таблицы, то поля из "общей" таблицы будут дублироваться для каждой такой "детализированной" записи.

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

СтатьяКалькуляции    КоличествоВыпуск    КоличествоВыручка    СуммаВыручки    ЦенаВыручки    СебестоимостьРеал    СтоимостьЗатратОбщая    СтоимостьЗатрат    СтоимостьНаЕдиницу    КоличествоВыручкаОбщая    СуммаВыручкиОбщая
Амортизация оборудования    42    385    4 294 865,32    11 155,49433766    2 964 020,23    145 314,33    1 332 048,025    3 459,865    385    4 294 865,32
Амортизация оборудования    142                                    393 986,03556235    1 023,34035211
Амортизация оборудования    108                                    518 018,6763906    1 345,50305556
...

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


|ВЫБРАТЬ
|   ВременаяВыручка.Организация КАК Организация,
|   ВременаяВыручка.Номенклатура КАК Номенклатура,
|   ВременаяВыручка.Серия КАК Серия,
|   ВЫБОР
|       КОГДА ВременнаяИтог.СтатьяКалькуляции ЕСТЬ NULL
|           ТОГДА &СтатьяКалькуляции
|       ИНАЧЕ ВременнаяИтог.СтатьяКалькуляции
|   КОНЕЦ КАК СтатьяКалькуляции,
|   Выпуск.Количество КАК КоличествоВыпуск,
|   ВременаяВыручка.КоличествоВыручка КАК КоличествоВыручка,
|   ВременаяВыручка.СуммаВыручки КАК СуммаВыручки,
|   ВременаяВыручка.ЦенаВыручки КАК ЦенаВыручки,
|   ВременаяВыручка.СебестоимостьРеал КАК СебестоимостьРеал,
|   ВЫБОР
|       КОГДА ВременнаяИтог.СтатьяКалькуляции ЕСТЬ NULL
|           ТОГДА ВременаяВыручка.СебестоимостьРеал
|       ИНАЧЕ ВременнаяИтог.СтоимостьЗатрат
|   КОНЕЦ КАК СтоимостьЗатратОбщая,
|   ВЫБОР
|       КОГДА Выпуск.Количество = 0
|           ТОГДА 0
|       ИНАЧЕ ВЫБОР
|           КОГДА ВременнаяИтог.СтатьяКалькуляции ЕСТЬ NULL
|               ТОГДА ВременаяВыручка.СебестоимостьРеал
|           ИНАЧЕ ВЫБОР
|               КОГДА Выпуск.Количество = 0
|                   ТОГДА 0
|               ИНАЧЕ ВременнаяИтог.СтоимостьЗатрат / Выпуск.Количество * ВременаяВыручка.КоличествоВыручка
|           КОНЕЦ
|       КОНЕЦ
|   КОНЕЦ КАК СтоимостьЗатрат,
|   ВЫБОР
|       КОГДА Выпуск.Количество = 0
|           ТОГДА 0
|       ИНАЧЕ ВЫБОР
|           КОГДА ВременнаяИтог.СтатьяКалькуляции ЕСТЬ NULL
|               ТОГДА ВЫБОР
|                   КОГДА ВременаяВыручка.КоличествоВыручка = 0
|                       ТОГДА 0
|                   ИНАЧЕ ВременаяВыручка.СебестоимостьРеал / ВременаяВыручка.КоличествоВыручка
|               КОНЕЦ
|           ИНАЧЕ ВЫБОР
|               КОГДА Выпуск.Количество = 0
|                   ТОГДА 0
|               ИНАЧЕ ВременнаяИтог.СтоимостьЗатрат / Выпуск.Количество
|           КОНЕЦ
|       КОНЕЦ
|   КОНЕЦ КАК СтоимостьНаЕдиницу,
|   ВременаяВыручкаОбщая.КоличествоВыручкаОбщая КАК КоличествоВыручкаОбщая,
|   ВременаяВыручкаОбщая.СуммаВыручкиОбщая КАК СуммаВыручкиОбщая
|ИЗ
|   ВременаяВыручка КАК ВременаяВыручка
|       ЛЕВОЕ СОЕДИНЕНИЕ ВременнаяИтог КАК ВременнаяИтог
|       ПО ВременаяВыручка.Номенклатура = ВременнаяИтог.Продукция
|           И ВременаяВыручка.Серия = ВременнаяИтог.СерияПродукции
|       ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ВыпускПродукции КАК Выпуск
|       ПО ВременаяВыручка.Организация = Выпуск.Организация
|           И ВременаяВыручка.Номенклатура = Выпуск.АналитикаУчетаНоменклатуры.Номенклатура
|           И ВременаяВыручка.Серия = Выпуск.АналитикаУчетаНоменклатуры.Серия
|       ЛЕВОЕ СОЕДИНЕНИЕ ВременаяВыручкаОбщая КАК ВременаяВыручкаОбщая
|       ПО ВременаяВыручка.Организация = ВременаяВыручкаОбщая.Организация
|           И ВременаяВыручка.Номенклатура = ВременаяВыручкаОбщая.Номенклатура;

Почему возникают некорректные итоги?

Когда мы соединяем несколько таблиц с помощью ЛЕВОГО СОЕДИНЕНИЯ напрямую в одном запросе, SQL-сервер формирует плоскую таблицу, в которой каждая строка из "левой" таблицы, имеющая несколько совпадений в "правой" таблице, будет продублирована столько раз, сколько есть совпадений. Например, если у нас есть одна запись о выручке и 10 записей о выпуске продукции, связанных с этой выручкой, то строка выручки будет продублирована 10 раз.

Система Компоновки Данных, при расчете ресурсов (агрегатных функций) по таким полям, как СУММА, будет суммировать все эти продублированные значения. В результате, если СуммаВыручки для одной операции составляет 4 294 865,32, и эта строка была продублирована 10 раз из-за соединения с таблицей выпусков, то итоговая сумма по СуммаВыручки будет не 4 294 865,32, а 42 948 653,2, что является неверным.

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

Существует несколько подходов к решению этой проблемы:

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

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

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

Разберем процесс по шагам:

Создание отдельных наборов данных.
Разделите ваш сложный запрос на несколько более простых запросов, каждый из которых будет формировать свой набор данных. Например, если у вас были таблицы ВременаяВыручка, ВременнаяИтог, РегистрНакопления.ВыпускПродукции, ВременаяВыручкаОбщая, то создайте для каждой из них свой набор данных-запрос.

Пример: Один набор данных будет получать информацию о выручке, другой – о выпуске продукции, третий – об общих показателях выручки и т.д. Каждый запрос должен быть максимально простым и извлекать только необходимые данные, формируя "базовые" таблицы.
```
// Запрос для набора данных "Выручка"
ВЫБРАТЬ
    ВременаяВыручка.Организация КАК Организация,
    ВременаяВыручка.Номенклатура КАК Номенклатура,
    ВременаяВыручка.Серия КАК Серия,
    ВременаяВыручка.КоличествоВыручка КАК КоличествоВыручка,
    ВременаяВыручка.СуммаВыручки КАК СуммаВыручки,
    ВременаяВыручка.ЦенаВыручки КАК ЦенаВыручки,
    ВременаяВыручка.СебестоимостьРеал КАК СебестоимостьРеал
ИЗ
    ВременаяВыручка КАК ВременаяВыручка
```
И аналогичные запросы для других частей вашего исходного запроса.
Настройка связей наборов данных.
После того как вы создали несколько наборов данных, перейдите на вкладку "Связи наборов данных" в схеме компоновки данных. Здесь мы будем указывать, как эти наборы данных должны быть связаны между собой.

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

Преимущества использования связей наборов данных в СКД:

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

Для более глубокого понимания работы с СКД и связями наборов данных мы рекомендуем обратиться к авторитетным источникам, таким как "Разработка сложных отчетов в 1С:Предприятии 8. Система компоновки данных" Е.Ю. Хрусталёвой.

Настройка агрегатных функций (ресурсов) в СКД

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

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

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

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