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

← На главную

При разработке отчетов на системе компоновки данных (СКД) программисты часто сталкиваются с ситуацией, когда итоговые суммы в колонках «раздуваются». Это происходит из-за специфики работы SQL-соединений: если в одной из соединяемых таблиц по ключу связи находится несколько записей, строки из другой таблицы начинают дублироваться. Особенно остро эта проблема проявляется в отчетах по НДФЛ, где данные из регистра НДФЛРасчетыСБюджетом соединяются с данными регистра НДФЛСведенияОДоходах.

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

Причина возникновения проблемы

Рассмотрим типовой запрос, который часто становится источником ошибок:


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

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

Способ 1. Использование агрегатной функции МАКСИМУМ

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

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

Способ 2. Связи наборов данных в СКД

Наиболее правильный архитектурный подход в СКД — отказ от ЛЕВОЕ СОЕДИНЕНИЕ внутри одного запроса. Вместо этого мы разделим данные на два отдельных набора данных-запроса:

НаборДанных1: выбирает данные по налогам из регистра НДФЛРасчетыСБюджетом.
НаборДанных2: выбирает данные по доходам из регистра НДФЛСведенияОДоходах.

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

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

Если изменить структуру запроса невозможно, воспользуемся мощным механизмом функций СКД. В языке выражений СКД нет конструкции СУММА(РАЗЛИЧНЫЕ), но её можно имитировать с помощью функции ВычислитьВыражениеСГруппировкойМассив.

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


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

Как это работает: Во-первых, система группирует данные по уникальной паре «Регистратор + Физлицо». Во-вторых, для каждой такой группы вычисляется максимальное (единственное) значение налога. В-третьих, полученные уникальные значения собираются в массив, который затем передается функции Сумма.

Этот метод позволяет получить верные итоги как в группировках, так и в «подвале» отчета.

Способ 4. Группировка в исходном запросе

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

Выясним, как это выглядит на практике:


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

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

После такой предварительной подготовки в каждой таблице на одну комбинацию ключей ФизЛицо + Регистратор будет приходиться ровно одна строка. Последующее соединение будет безопасным и не приведет к росту сумм.

Подведем итоги

Для решения проблемы «размножения» сумм в СКД мы рекомендуем:

Использовать связи наборов данных вместо сложных соединений в одном запросе — это самый надежный и «родной» для СКД способ.
Если нужно быстро исправить расчет в ресурсах, применяйте вычисляемые выражения с группировкой в массив.
Избегайте простого использования МАКСИМУМ в ресурсах, если вам важны корректные общие итоги по отчету.