Как устранить задвоение и затроение сумм в отчете СКД при соединении таблиц

← На главную

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

Анализ проблемы: природа задвоения данных

Выясним причину искажения сумм. Ошибка возникает при использовании левого соединения таблиц типа один-ко-многим (1:N). Например, когда к одной строке документа поступления из регистра Закупки.Обороты присоединяются строки регистра ПартииПрочихРасходов.Обороты, где этот документ выступает аналитикой для нескольких статей дополнительных расходов.

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


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

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

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

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

Создаем НаборДанных1 (Закупки): формируем запрос исключительно к регистру закупок без соединения с расходами.


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

Создаем НаборДанных2 (Расходы): формируем второй запрос к дополнительным расходам.


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

Настраиваем связь: переходим на вкладку «Связи наборов данных» и добавляем строку:

Источник связи: НаборДанных1

Приемник связи: НаборДанных2

Условие связи: Регистратор = АналитикаРасходов
Настраиваем ресурсы: на вкладке «Ресурсы» указываем стандартные формулы Сумма(СтоимостьЗакупки) и Сумма(СтоимостьДополнительныхРасходов).

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

Способ 2. Объединение запросов (ОБЪЕДИНИТЬ ВСЕ)

Если отчет требуется построить строго в рамках одного набора данных-запроса, исключить перемножение строк позволяет замена соединения на объединение. В каждой ветке запроса выбирается своя часть показателей, а недостающие поля заполняются нулями или пустыми значениями.


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

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

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

При таком подходе строки не дублируются, а стандартная агрегация Сумма() в ресурсах СКД корректно считает итоги на всех уровнях группировок (по документу, контрагенту, периоду и в целом по отчету).

Способ 3. Вычисление выражений ресурсов по массиву

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

Для ресурса СтоимостьЗакупки добавляем расчет для детальных записей и уровня документа: Максимум(СтоимостьЗакупки).
Для вышестоящих группировок (Контрагент, Период, ОбщийИтог) прописываем агрегацию уникальных значений по документам:
```
Сумма(ВычислитьВыражениеСГруппировкойМассив("Максимум(СтоимостьЗакупки)", "Регистратор"))
```
Для вычисления расчетных показателей (например, процента дополнительных расходов) задаем формулу непосредственно в ресурсах, а не в тексте запроса:
```
Выбор 
    Когда ЕстьNULL(Сумма(СтоимостьЗакупки), 0) = 0 Тогда 0 
    Иначе Сумма(СтоимостьДополнительныхРасходов) / Сумма(СтоимостьЗакупки) * 100 
Конец
```

Важные рекомендации по оптимизации запросов СКД

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

Параметры виртуальных таблиц: условия по периоду, складу и хозяйственной операции необходимо передавать внутрь параметров виртуальной таблицы РегистрНакопления.Закупки.Обороты(&НачалоПериода, &КонецПериода, , Склад = &Склад), а не в секцию ГДЕ. Секция ГДЕ фильтрует данные уже после того, как таблица оборотов прочитана из базы данных, что существенно замедляет формирование отчета.
Типизация полей соединения: в прикладных решениях 1С:ERP поле АналитикаРасходов может иметь составной тип данных. При соединении с полем Регистратор рекомендуется явно приводить тип через функцию ВЫРАЗИТЬ(ПартииПрочихРасходовОбороты.АналитикаРасходов КАК Документ.ПриобретениеТоваровУслуг), чтобы оптимизатор СУБД корректно строил план выполнения запроса.