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