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