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