При разработке отчетов в системе компоновки данных (СКД) программисты часто сталкиваются с неприятной проблемой: при выводе отчета итоговые показатели (например, суммы взаиморасчетов или оплат) необоснованно увеличиваются, умножаются или дублируются. Особенно ярко эта проблема проявляется при попытке объединить в одном запросе детальные товарные позиции и общие суммовые показатели документов.
В этой статье мы подробно разберем, почему возникает эффект «перемножения» строк, проанализируем классические ошибки разработчиков на примере конфигурации «1С:Управление производственным предприятием 1.3» (УПП) и рассмотрим три надежных способа решения этой проблемы, включая лучшие практики разработки в СКД.
Рассмотрим типичную ситуацию. Нам необходимо построить отчет, который показывает закупки номенклатуры за определенный период и одновременно выводит сумму взаиморасчетов по документу-регистратору. Разработчик пишет запрос, в котором выполняет ЛЕВОЕ СОЕДИНЕНИЕ таблицы закупок с регистром расчетов по полю Регистратор.
В результате работы такого запроса суммы взаиморасчетов начинают дублироваться. Почему это происходит? Дело в разной гранулярности (детализации) объединяемых данных:
Закупки детализирована до уровня строк товарной части документа. В одной закупке (одном документе-регистраторе) может быть 10 или 50 строк с разной номенклатурой.РасчетыПоПриобретениюОрганизации) хранит информацию в разрезе договоров и документов расчетов. Для одного документа-регистратора там чаще всего присутствует всего одна или несколько сводных строк (например, общая сумма по документу).При левом соединении СУБД сопоставляет каждую строку из левой таблицы (закупки) со строками из правой таблицы (взаиморасчеты). Если в левой таблице у нас 10 строк с разной номенклатурой по одному регистратору, а в правой — одна строка с общей суммой взаиморасчетов по этому же регистратору, то эта общая сумма продублируется ровно 10 раз — для каждой строки номенклатуры. При расчете итогов (функция СУММА) СКД сложит эти дубли, и итоговая сумма взаиморасчетов по документу окажется завышенной ровно в 10 раз.
Давайте разберем, как исправить эту ошибку различными методами.
Самое очевидное решение, которое часто приходит на ум разработчикам, — это связать таблицы не только по регистратору, но и по аналитике номенклатуры. Тогда к каждой строке номенклатуры подтянется только ее часть суммы взаиморасчетов.
Посмотрим на пример измененного запроса с дополнительным условием связи:
ВЫБРАТЬ РАЗЛИЧНЫЕ
Закупки.Номенклатура КАК Номенклатура,
Закупки.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
Закупки.Контрагент КАК Контрагент,
СУММА(Закупки.Количество) КАК Количество,
СУММА(Закупки.Стоимость) КАК Стоимость,
СУММА(Закупки.НДС) КАК НДС,
Закупки.Период КАК Период,
Закупки.Регистратор КАК Регистратор,
СУММА(Расчеты.СуммаВзаиморасчетов) КАК СуммаВзаиморасчетов,
Расчеты.КурсДокумента КАК КурсДокумента,
Расчеты.КратностьДокумента КАК КратностьДокумента,
Расчеты.ВалютаДокумента КАК ВалютаДокумента,
СУММА(Расчеты.СуммаНДС) КАК СуммаНДС
ИЗ
РегистрНакопления.Закупки КАК Закупки
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.РасчетыПоПриобретениюОрганизации КАК Расчеты
ПО Закупки.Регистратор = Расчеты.Регистратор
И Закупки.Номенклатура = Расчеты.Номенклатура
ГДЕ
Закупки.Период МЕЖДУ &ДатаНачало И &ДатаКонец
СГРУППИРОВАТЬ ПО
Закупки.Номенклатура,
Закупки.ХарактеристикаНоменклатуры,
Закупки.Контрагент,
Закупки.Период,
Закупки.Регистратор,
Расчеты.КурсДокумента,
Расчеты.КратностьДокумента,
Расчеты.ВалютаДокумента
Важное предупреждение для пользователей УПП 1.3: Данный способ сработает только в том случае, если ваша конфигурация была серьезно доработана и в регистр сведений РасчетыПоПриобретениюОрганизации программисты вручную добавили измерение или реквизит Номенклатура. В типовой конфигурации УПП 1.3 этот регистр не содержит номенклатурного разреза. Попытка выполнить такой запрос в типовой базе приведет к ошибке СУБД: "Поле не найдено Расчеты.Номенклатура".
Чтобы избежать перемножения строк на уровне СУБД и не перегружать запрос сложными условиями группировки, в Системе компоновки данных реализован великолепный механизм — Связи наборов данных (Dataset Links). Это самый правильный, надежный и архитектурно верный подход.
Вместо одного тяжелого запроса мы создадим два независимых набора данных и свяжем их на уровне СКД по полю Регистратор. Разберем этот процесс по шагам.
Шаг 1. Создаем НаборДанных1 (Закупки)
Этот набор данных выбирает информацию о номенклатуре, количестве и стоимости. Обратите внимание, что мы используем производительную виртуальную таблицу оборотов Обороты вместо физической таблицы регистра:
ВЫБРАТЬ
ЗакупкиОбороты.Номенклатура КАК Номенклатура,
ЗакупкиОбороты.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
ЗакупкиОбороты.Контрагент КАК Контрагент,
ЗакупкиОбороты.Регистратор КАК Регистратор,
ЗакупкиОбороты.КоличествоОборот КАК Количество,
ЗакупкиОбороты.СтоимостьОборот КАК Стоимость,
ЗакупкиОбороты.НДСОборот КАК НДС,
ЗакупкиОбороты.Период КАК Период
ИЗ
РегистрНакопления.Закупки.Обороты(&ДатаНачало, &ДатаКонец, Регистратор, ) КАК ЗакупкиОбороты
Шаг 2. Создаем НаборДанных2 (Взаиморасчеты)
Второй набор данных будет выбирать информацию о взаиморасчетах из регистра сведений по тем же регистраторам:
ВЫБРАТЬ
Расчеты.Регистратор КАК Регистратор,
Расчеты.СуммаВзаиморасчетов КАК СуммаВзаиморасчетов,
Расчеты.СуммаНДС КАК СуммаНДСВзаиморасчетов,
Расчеты.КурсДокумента КАК КурсДокумента,
Расчеты.ВалютаДокумента КАК ВалютаДокумента
ИЗ
РегистрСведений.РасчетыПоПриобретениюОрганизации КАК Расчеты
ГДЕ
Расчеты.Регистратор В (
ВЫБРАТЬ РАЗЛИЧНЫЕ
ЗакупкиОбороты.Регистратор
ИЗ
РегистрНакопления.Закупки.Обороты(&ДатаНачало, &ДатаКонец, Регистратор, ) КАК ЗакупкиОбороты
)
Шаг 3. Настраиваем связь наборов данных
Переходим на вкладку «Связи наборов данных» в конструкторе схемы компоновки данных и добавляем новую строку:
НаборДанных1НаборДанных2Регистратор = РегистраторПри таком подходе СКД сначала получит плоскую таблицу закупок с номенклатурой, а затем для каждой строки (или группы строк по регистратору) аккуратно подтянет данные о взаиморасчетах. Суммирования и умножения строк не произойдет, так как расчет итогов по каждому набору данных компоновщик выполняет независимо.
Если вам по какой-то причине необходимо оставить структуру с левым соединением в одном запросе, вы можете решить проблему некорректного суммирования с помощью гибкой настройки выражений ресурсов на вкладке «Ресурсы».
Вместо стандартного выражения Сумма(СуммаВзаиморасчетов), которое складывает все строки подряд, мы можем указать СКД, как именно рассчитывать итоги для разных уровней группировки отчета:
Номенклатура) сумма взаиморасчетов не должна дублироваться. Мы можем выводить максимальное или среднее значение: Максимум(СуммаВзаиморасчетов) или вообще оставить поле пустым.Контрагент или Организация) мы можем использовать агрегатные функции СКД, которые вычисляют сумму уникальных документов.Пример формулы для поля ресурса СуммаВзаиморасчетов:
ВычислитьВыражениеСГруппировкойМассив("Максимум(СуммаВзаиморасчетов)", "Регистратор")
Это выражение собирает максимальные значения сумм взаиморасчетов в разрезе каждого регистратора в массив, а затем суммирует элементы этого массива. Такой подход гарантирует, что даже при наличии множества строк номенклатуры в одном документе, его сумма взаиморасчетов будет учтена в общем итоге только один раз.
Помимо устранения математических ошибок при сложении сумм, при разработке подобных отчетов важно учитывать требования производительности и особенности метаданных 1С:
РегистрНакопления.Закупки напрямую. Это приводит к полному сканированию таблицы базой данных и сильному падению скорости работы системы. Всегда используйте виртуальную таблицу оборотов: РегистрНакопления.Закупки.Обороты(...).
Закупки.Регистратор ССЫЛКА Документ.ПоступлениеТоваровУслуг
РасчетыПоПриобретениюОрганизации в УПП 1.3 является вспомогательным техническим регистром, данные в котором формируются при проведении расчетов и восстановлении последовательностей. Если в вашей базе регламентные операции выполняются нерегулярно, данные в нем могут быть неактуальными. Для получения точной картины по взаиморасчетам рекомендуется использовать предназначенные для этого регистры накопления:
РегистрНакопления.ВзаиморасчетыСКонтрагентами — для оперативного управленческого учета.РегистрНакопления.РасчетыСКонтрагентами — для регламентированного (бухгалтерского) учета.Применение механизмов связи наборов данных в СКД и правильный выбор регистров позволят вам создавать быстродействующие, отказоустойчивые отчеты, которые всегда будут показывать корректные и точные данные пользователям.