Разработка отчетов в Системе компоновки данных (СКД) часто ставит перед разработчиком непростые задачи, особенно когда дело касается вывода результатов в кросс-таблицах. Типичный сценарий: по строкам (например, по сотрудникам) необходимо рассчитать максимум, по колонкам (например, по месяцам) — сумму, а в угловой ячейке на пересечении общих итогов должна получиться корректная сумма максимумов. По умолчанию СКД пытается рассчитать общий итог напрямую по детальным записям, что приводит к неверным цифрам и задвоению данных.
В этой статье мы подробно разберем, почему возникает эта проблема, как работает механизм компоновки данных в таких ситуациях и как настроить отчет так, чтобы все итоги считались абсолютно верно.
Представим отчет по учету сверхурочных часов, которые в исходном запросе рассчитываются накопительным итогом по месяцам. Поскольку данные уже накоплены, для каждого отдельного сотрудника итоговым значением за год будет его максимальное значение (которое совпадает с показателем последнего месяца). Следовательно, по строкам логично применить функцию Максимум.
В то же время по колонкам (в разрезе месяцев) нам требуется видеть сумму часов по всем сотрудникам за этот конкретный месяц. Здесь отлично работает функция Сумма.
Однако на пересечении строк и колонок — в правом нижнем углу таблицы (общий итог) — СКД сталкивается с противоречием. Если применить стандартную функцию Сумма, платформа просто сложит все детальные записи из базы данных, что приведет к колоссальному завышению результата (так как накопительные итоги сложатся между собой). Если же оставить Максимум, то в углу отобразится максимальный показатель среди всех сотрудников за весь период, а не сумма их личных итоговых результатов.
Давайте разберем по шагам, как решить эту проблему несколькими способами.
ВычислитьВыражениеСГруппировкойМассивСамый надежный и быстрый способ решить проблему без изменения структуры запроса — это тонкая настройка вкладки Ресурсы в схеме компоновки данных. Мы укажем СКД, как именно рассчитывать показатель для каждой отдельной группировки и для общего итога.
Для реализации этого подхода выполним следующие действия:
СверхурочныеЧасы) в список ресурсов три раза, чтобы настроить индивидуальный расчет для каждого контекста.Максимум(СверхурочныеЧасы) и в колонке Рассчитывать по... укажем группировку строк — Сотрудник.Сумма(СверхурочныеЧасы) и в колонке Рассчитывать по... укажем группировку колонок — Период (или Месяц).ОбщийИтог). В поле Выражение запишем специальную формулу:
Сумма(ВычислитьВыражениеСГруппировкойМассив("Максимум(СверхурочныеЧасы)", "Сотрудник"))
Проанализируем, как работает это выражение. Функция ВычислитьВыражениеСГруппировкойМассив заставляет СКД произвести локальную виртуальную группировку данных по измерению Сотрудник, вычислить для каждого сотрудника его Максимум(СверхурочныеЧасы), поместить полученные значения в массив, а внешняя функция Сумма сложит элементы этого массива. В результате в угловой ячейке мы получим точную сумму итоговых показателей сотрудников.
Использование накопительного итога непосредственно в запросе является архитектурным антипаттерном для СКД. Это сильно связывает руки при расчете других агрегатных функций. Гораздо правильнее получать из базы данных «чистые» транзакционные данные за каждый месяц, а нарастающий итог рассчитывать силами самой СКД по оси времени.
Давайте разберем этот элегантный способ:
Часы содержит только чистые сверхурочные часы за конкретный месяц.ЧасыНарастающимИтогом. Выражение для самого поля оставляем пустым.
ВычислитьВыражение("Сумма(Часы)", "Период", , "Первая", "Текущая")
Давайте разберем параметры функции ВычислитьВыражение по порядку:
"Сумма(Часы)" — базовое выражение, которое мы хотим рассчитать."Период" — имя группировки (наша колонка времени), в разрезе которой происходит вычисление."Первая" — указывает, что расчет нужно начинать с самой первой колонки (начала периода)."Текущая" — указывает, что суммирование нужно вести до текущей колонки включительно.При таком подходе СКД сама сформирует накопительный итог по горизонтали. Поскольку базовые данные в запросе больше не содержат искусственно надутых сумм, расчет общих вертикальных и угловых итогов стандартными функциями Сумма будет происходить автоматически и без ошибок.
Бывают ситуации, когда структура отчета еще сложнее — например, присутствуют группировки подразделений, проектов или видов начислений. В этом случае для корректного отображения пересечений общих итогов разработчику приходится прибегать к вложенным вызовам функций СКД.
Если нам необходимо для общего итога рассчитать сумму средних значений, которые, в свою очередь, берутся от максимумов по сотрудникам, выражение в ресурсе общего итога примет следующий вид:
Сумма(ВычислитьВыражениеСГруппировкойМассив("Среднее(ВычислитьВыражениеСГруппировкойМассив(""Максимум(СверхурочныеЧасы)"", ""Период, Сотрудник""))", "Сотрудник"))
Важное примечание: обратите внимание на использование двойных кавычек во вложенных текстовых параметрах внутри выражений СКД. Это обязательное синтаксическое требование платформы 1С для экранирования строк.
В редких случаях, когда формулы ресурсов становятся слишком громоздкими или СКД не позволяет добиться нужного визуального представления стандартным механизмом общих итогов, можно применить обходной путь с добавлением искусственной колонки итогов:
Нет.ГруппировкаИтог и выражением-строкой "Итого".ГруппировкаИтог.Мы рассмотрели все основные способы решения проблемы расчета угловых итогов в СКД. Выбор конкретного метода зависит от архитектуры вашего запроса и сложности структуры отчета, но использование функции ВычислитьВыражениеСГруппировкойМассив по праву считается самым гибким и мощным инструментом в арсенале разработчика 1С для работы с ресурсами кросс-таблиц.