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

← На главную

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

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

Выясняем причину: почему «размножаются» суммы?

Рассмотрим ситуацию на примере запроса из исходного обращения. Разработчик соединяет таблицу доходов физических лиц с оборотами начислений и удержаний по регистратору и сотруднику:


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

Допустим, по одному документу увольнения у сотрудника есть три разные строки с доходами (например, оклад, компенсация отпуска и премия), но всего одно удержание за неотработанный отпуск. В результате левого соединения единственная строка удержания сопоставится с каждой из трех строк доходов. База данных вернет три строки, в каждой из которых будет фигурировать одна и та же сумма удержания (например, 3 734,75 рублей). При выводе детальных записей или расчете общих итогов СКД сложит эти значения, и вместо реальных 3 734,75 рублей мы получим утроенную сумму — 11 204,25 рублей.

Давайте разберем по шагам, как можно исправить это поведение системы.

Способ 1. Изменение функции агрегирования ресурса в СКД (Быстрое решение)

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

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

Перейдем на вкладку Ресурсы в конструкторе схемы компоновки данных.
Найдем поле СуммаОборот.
В колонке Выражение заменим стандартное Сумма(СуммаОборот) на Максимум(СуммаОборот) или Минимум(СуммаОборот).
В колонке Рассчитывать по... укажем группировки, для которых это правило актуально (например, по регистратору или сотруднику).

Важное предупреждение: этот способ является «костылем». Он работает корректно только в том случае, если в рамках группировки (например, одного регистратора) значение удержания действительно всегда одно и то же. Если же в документе окажется два разных начисления по коду "УОТ", функция Максимум выберет только наибольшее из них, потеряв часть реальных данных, и итоговая сумма отчета окажется заниженной.

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

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

Рассмотрим этот алгоритм по шагам:

Создадим первый набор данных - запрос (например, НаборДанныхДоходы), который выбирает сведения о доходах:


ВЫБРАТЬ
    СведенияОДоходахНДФЛ.ФизическоеЛицо КАК ФизическоеЛицо,
    СведенияОДоходахНДФЛ.Регистратор КАК Регистратор,
    СведенияОДоходахНДФЛ.СуммаДохода КАК СуммаДохода
ИЗ
    РегистрНакопления.СведенияОДоходахНДФЛ КАК СведенияОДоходахНДФЛ
ГДЕ
    СведенияОДоходахНДФЛ.ФизическоеЛицо = &ФизическоеЛицо
    И СведенияОДоходахНДФЛ.Период МЕЖДУ &Период1 И &Период2

Создадим второй набор данных - запрос (например, НаборДанныхУдержания), выбирающий удержания:


ВЫБРАТЬ
    НачисленияУдержанияПоСотрудникамОбороты.ФизическоеЛицо КАК ФизическоеЛицо,
    НачисленияУдержанияПоСотрудникамОбороты.Регистратор КАК Регистратор,
    НачисленияУдержанияПоСотрудникамОбороты.НачислениеУдержание КАК НачислениеУдержание,
    НачисленияУдержанияПоСотрудникамОбороты.СуммаОборот КАК СуммаОборот
ИЗ
    РегистрНакопления.НачисленияУдержанияПоСотрудникам.Обороты(, , Регистратор, НачислениеУдержание.Код ПОДОБНО "УОТ") КАК НачисленияУдержанияПоСотрудникамОбороты

Перейдем на вкладку Связи наборов данных.
Добавим новую строку связи:
- Источник связи: НаборДанныхДоходы
- Приемник связи: НаборДанныхУдержания
- Условие связи: ФизическоеЛицо = ФизическоеЛицо И Регистратор = Регистратор
- Параметры: при необходимости можно передать параметры для оптимизации ограничений второго запроса.

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

Способ 3. Предварительная группировка во временных таблицах

Если по каким-то причинам нам обязательно нужно использовать только один запрос, мы должны предварительно обработать таблицы перед соединением. Основная задача — сделать так, чтобы связь происходила по уникальным ключам «один-к-одному».

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

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


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

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВТ_СвернутыеДоходы.ФизическоеЛицо КАК ФизическоеЛицо,
    ВТ_СвернутыеДоходы.Регистратор КАК Регистратор,
    ВТ_СвернутыеДоходы.СуммаДохода КАК СуммаДохода,
    НачисленияОбороты.НачислениеУдержание КАК НачислениеУдержание,
    ЕСТЬNULL(НачисленияОбороты.СуммаОборот, 0) КАК СуммаОборот
ИЗ
    ВТ_СвернутыеДоходы КАК ВТ_СвернутыеДоходы
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.НачисленияУдержанияПоСотрудникам.Обороты(, , Регистратор, НачислениеУдержание.Код ПОДОБНО "УОТ") КАК НачисленияОбороты
    ПО ВТ_СвернутыеДоходы.ФизическоеЛицо = НачисленияОбороты.ФизическоеЛицо
        И ВТ_СвернутыеДоходы.Регистратор = НачисленияОбороты.Регистратор

Поскольку во временной таблице ВТ_СвернутыеДоходы запись по паре «ФизическоеЛицо + Регистратор» теперь строго уникальна, при соединении с оборотами начислений строки размножаться не будут.

Способ 4. Использование функции СКД «ВычислитьВыражениеСГруппировкойМассив»

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

Для поля СуммаОборот на вкладке Ресурсы укажем следующее выражение:


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

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

Способ 5. Тонкая настройка ролей полей в СКД

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

Для поля связи (в нашем случае это Регистратор) можно задать роль:

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

Правильная разметка ролей полей компоновки помогает встроенному генератору макетов СКД корректно оптимизировать результирующий SQL-запрос и минимизировать наложение лишних записей в иерархии.

Резюме

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