При разработке отчетов на базе Системы компоновки данных (СКД) в «1С:Предприятии» программисты часто сталкиваются с классической проблемой: при выводе детальных записей или вложенных группировок итоговые суммы по вышестоящим группировкам начинают умножаться или задваиваться. Это происходит из-за связи типа «один ко многим» в SQL-запросе.
Давайте подробно разберем, почему возникает эта проблема, проанализируем реальный пример из практики и рассмотрим четыре эффективных способа ее решения.
Представим стандартную ситуацию. У нас есть документ СчетНаОплату, по которому зарегистрирован доход в размере 1000 рублей. В то же время по этому счету были отражены расходы от нескольких разных поставщиков (например, от двух контрагентов: Поставщик А и Поставщик Б). Если мы попытаемся соединить таблицу доходов с таблицей расходов/поставщиков по ключевому полю СчетНаОплату, в результирующей выборке СУБД мы получим две строки:
Когда СКД начинает рассчитывать итоги по группировке СчетНаОплату с помощью стандартного выражения ресурса Сумма(СуммаДоход), система складывает значения из обеих строк. В результате мы получаем неверный итог по доходу: 2000 рублей вместо исходных 1000 рублей. Сумма дохода умножилась на количество поставщиков.
Рассмотрим детально, какими способами можно решить эту проблему и получить корректные цифры в отчете.
Это самый надежный, методологически верный и архитектурно красивый способ решения проблемы средствами самой платформы «1С:Предприятие».
Вместо того чтобы делать сложное левое или внутреннее соединение таблиц непосредственно в тексте одного SQL-запроса, мы разделяем получение данных на два независимых запроса в рамках схемы компоновки данных.
Шаг 1. Создаем НаборДанных1 (запрос)
В этом наборе мы выбираем только основные данные по доходам и расходам в разрезе счетов, без детализации по поставщикам. Строки здесь дублироваться не будут:
ВЫБРАТЬ
ДоходыОбороты.СчетНаОплату КАК СчетНаОплату,
ДоходыОбороты.Контрагент КАК Клиент,
ДоходыОбороты.СуммаОборот КАК СуммаДоход,
РасходыОбороты.СуммаОборот КАК СуммаРасход
ИЗ
РегистрНакопления.ДоходыПоНаправлениямДеятельности.Обороты(&НачалоПериода, &КонецПериода) КАК ДоходыОбороты
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.РасходыПоНаправлениямДеятельности.Обороты(&НачалоПериода, &КонецПериода) КАК РасходыОбороты
ПО ДоходыОбороты.СчетНаОплату = РасходыОбороты.СчетНаОплату
Шаг 2. Создаем НаборДанных2 (запрос)
В этом наборе мы получаем только информацию о соответствии счетов на оплату и их поставщиков:
ВЫБРАТЬ РАЗЛИЧНЫЕ
Расходы.СчетНаОплату КАК СчетНаОплату,
Расходы.Контрагент КАК Поставщик
ИЗ
РегистрНакопления.РасходыПоНаправлениямДеятельности КАК Расходы
Шаг 3. Настройка связи наборов данных
Переходим на вкладку «Связи наборов данных» в конструкторе СКД и добавляем новую связь:
СчетНаОплату = СчетНаОплатуСчетНаОплатуПри таком подходе СКД сначала получит уникальные данные по доходам и расходам из первого набора, рассчитает по ним абсолютно корректные итоги, а затем для каждого счета сопоставит и выведет список поставщиков из второго набора. Задвоение сумм полностью исключается.
Если по каким-то причинам вам крайне важно оставить всю логику внутри одного запроса, можно применить алгоритм искусственного обнуления показателей для «лишних» строк. Мы оставляем сумму только для одного (например, первого или максимального по алфавиту) поставщика, а для остальных выводим 0.
Рассмотрим, как реализовать этот алгоритм на примере пакета запросов:
// Шаг 1. Получаем базовую таблицу доходов и расходов
ВЫБРАТЬ
ДоходыОбороты.СчетНаОплату КАК СчетНаОплату,
ДоходыОбороты.СуммаОборот КАК СуммаДоход,
РасходыОбороты.СуммаОборот КАК СуммаРасход,
РасходыОбороты.Контрагент КАК Поставщик
ПОМЕСТИТЬ ВТ_ДоходыРасходыПоставщики
ИЗ
РегистрНакопления.ДоходыПоНаправлениямДеятельности.Обороты КАК ДоходыОбороты
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрНакопления.РасходыПоНаправлениямДеятельности КАК РасходыОбороты
ПО ДоходыОбороты.СчетНаОплату = РасходыОбороты.СчетНаОплату
;
// Шаг 2. Определяем одного "главного" поставщика для каждого счета (например, по Максимуму)
ВЫБРАТЬ
ВТ.СчетНаОплату КАК СчетНаОплату,
МАКСИМУМ(ВТ.Поставщик) КАК ПоставщикМаксимум
ПОМЕСТИТЬ ВТ_МаксимальныеПоставщики
ИЗ
ВТ_ДоходыРасходыПоставщики КАК ВТ
ГРУППИРОВАТЬ ПО
ВТ.СчетНаОплату
;
// Шаг 3. Выводим суммы только для строки с главным поставщиком, для остальных - выводим 0
ВЫБРАТЬ
ВТ.СчетНаОплату КАК СчетНаОплату,
ВТ.Поставщик КАК Поставщик,
ВЫБОР
КОГДА ВТ.Поставщик = ВТ_Макс.ПоставщикМаксимум ТЕГДА ВТ.СуммаДоход
ИНАЧЕ 0
КОНЕЦ КАК СуммаДоход,
ВТ.СуммаРасход КАК СуммаРасход
ИЗ
ВТ_ДоходыРасходыПоставщики КАК ВТ
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_МаксимальныеПоставщики КАК ВТ_Макс
ПО ВТ.СчетНаОплату = ВТ_Макс.СчетНаОплату
В данном случае, когда СКД будет группировать и складывать значения ресурса СуммаДоход по счету, она сложит исходную сумму и нули, получив в результате правильный итог. Однако помните, что этот способ является "обходным" и может вести себя непредсказуемо, если пользователь начнет применять сложные отборы или специфические группировки в настройках отчета.
Если структура запроса уже жестко задана и вы не хотите ее менять, исправить расчет итогов можно непосредственно в настройках ресурсов СКД с помощью встроенного языка выражений компоновки данных. Для этого мы задействуем мощную функцию ВычислитьВыражениеСГруппировкойМассив.
По умолчанию выражение ресурса для поля СуммаДоход выглядит как Сумма(СуммаДоход). Чтобы СКД на уровне группировки по счету не суммировала дубли, мы укажем для этой группировки и общих итогов специальную формулу расчета.
Перейдем на вкладку «Ресурсы» в СКД и добавим настройки:
Выражение: Сумма(СуммаДоход)
Выражение: Сумма(ВычислитьВыражениеСГруппировкойМассив("Максимум(СуммаДоход)", "СчетНаОплату"))
Как это работает: Функция ВычислитьВыражениеСГруппировкойМассив берет максимальное значение суммы дохода для каждого уникального счета (избавляясь тем самым от дублирования строк), помещает эти значения в массив, а внешняя функция Сумма() складывает элементы полученного массива. Итоги рассчитываются абсолютно корректно.
Зачастую бизнес-требования не настаивают на том, чтобы каждый поставщик выводился в новой строке группировки. Если пользователям важно просто видеть список поставщиков напротив счета на оплату, мы можем свернуть строки и вывести контрагентов в одну ячейку таблицы через запятую.
В этом случае запрос остается плоским, без дублирования строк по суммам, а склеивание строк выполняется средствами СКД:
Поставщик.Поставщик в ресурсы.СоединитьСтроки(Массив(Различные Поставщик), ", ")
СчетНаОплату.При такой настройке суммы доходов и расходов будут суммироваться по стандартной формуле Сумма(...) и не задвоятся, так как дублирования строк в базе нет. При этом в колонке «Поставщик» напротив каждого счета красиво выведутся все связанные с ним контрагенты через запятую.
Выбирайте наиболее подходящий для вашей конфигурации и бизнес-логики метод, проектируйте схемы компоновки данных правильно, и ваши отчеты всегда будут радовать пользователей корректными результатами!