Как в СКД рассчитать среднее значение за месяц и вывести его только в итогах

При разработке отчетов в системе компоновки данных (СКД) часто возникает задача: создать таблицу, где в колонках отображаются месяцы с суммами, а в конце — колонка с общим итогом, которая показывает не общую сумму, а среднее значение за месяц. На первый взгляд задача кажется простой, но новички часто сталкиваются с тем, что расчетный показатель начинает отображаться в каждой колонке периода, «раздувая» отчет, или возникают сложности с правильным расчетом делителя (количества месяцев).

В этой статье мы подробно разберем несколько профессиональных способов решения этой задачи, начиная от простых формул в ресурсах и заканчивая продвинутым использованием функций СКД.

Способ 1. Настройка ресурса через колонку «Рассчитывать по...»

Это самый «чистый» и штатный метод СКД, который позволяет использовать разные формулы для одного и того же поля на разных уровнях группировок. Рассмотрим этот метод пошагово:

1. Перейдем на закладку Ресурсы в схеме компоновки данных.
2. Добавим наше поле (например, Количество) в список ресурсов дважды.
3. Для первой строки в колонке «Выражение» напишем стандартное: Сумма(Количество).
4. В этой же строке, в колонке «Рассчитывать по...», выберем все группировки, кроме Общего итога. Обязательно укажите здесь вашу группировку по месяцам.
5. Для второй строки (того же поля Количество) в колонке «Выражение» напишем формулу среднего. Например: Сумма(Количество) / Количество(Различные НачалоПериода(Период, "Месяц"))
6. В колонке «Рассчитывать по...» для этой строки выберем только Общий итог.

Проанализируем ситуацию: теперь СКД будет суммировать данные внутри каждого месяца, но как только дело дойдет до отрисовки общего итога, применится вторая формула. Это исключает необходимость создавать дублирующие поля в запросе.

Способ 2. Использование функции ВычислитьВыражение

Иногда нам нужно рассчитать среднее значение более гибко, основываясь на уже агрегированных данных. Для этого в СКД существует мощная функция ВычислитьВыражение. Она позволяет обратиться к значениям других группировок без изменения структуры запроса.

Рассмотрим пример выражения для ресурса в колонке общего итога:

Сумма(Продажи) / ВычислитьВыражение("Количество(Различные НачалоПериода(Период, 'Месяц'))", , "ОбщийИтог")

Разберем, как это работает. Функция считает количество уникальных месяцев во всем отчете (благодаря параметру "ОбщийИтог") и использует это число как делитель для общей суммы. Это особенно полезно, если количество месяцев заранее неизвестно или зависит от установленных пользователем фильтров.

Способ 3. Создание вычисляемого поля для «дублирования» данных

Автор темы на форуме упоминал ошибку «повторяющееся поле итогов». Чтобы избежать конфликтов имен при настройке сложных итогов, мы рекомендуем использовать вычисляемые поля. Выясним, как это сделать:

1. На закладке Вычисляемые поля добавим новое поле с именем КоличествоДляСреднего.
2. В поле «Выражение» просто укажем имя нашего основного поля: Количество.
3. Теперь на закладке Ресурсы у нас есть два разных идентификатора. Для Количество мы настраиваем Сумма(), а для КоличествоДляСреднего — формулу расчета среднего.

Этот подход позволяет избежать путаницы в макетах и настройках оформления, так как для системы это два разных ресурса, хоть они и черпают данные из одного источника.

Как скрыть расчетное поле из колонок периодов

Частая проблема: вы добавили ресурс «Среднее», и он появился рядом с суммой в каждом месяце. Чтобы среднее отображалось только в общем итоге, выполним следующие действия в настройках отчета:

1. Перейдем на вкладку Настройки, выберем нашу группировку «Колонки» (где выбран период/месяц).
2. В нижней части окна выберем вкладку Выбранные поля.
3. Снимем флаг «Использовать родительские поля».
4. Добавим в список только ресурс Сумма(Количество).
5. Затем выберем самый верхний уровень настроек (Отчет) и на его вкладке Выбранные поля убедимся, что добавлены оба ресурса: и сумма, и среднее.

Таким образом, в группировке по месяцам будет действовать локальный набор полей, а в общих итогах — глобальный.

Решение проблемы сортировки месяцев

В процессе обсуждения упоминалось, что при получении месяца числом (1, 2, 3...) отчет выглядит некрасиво, а при получении строкой — сортируется по алфавиту (Август, Апрель...). Посмотрим, как решить это правильно средствами СКД:

В запросе всегда оставляйте поле периода как Дата (Начало периода). В схеме компоновки на закладке Наборы данных для этого поля настройте:

• Выражение представления: Формат(Период, "ДФ='ММММ гггг'") — это обеспечит вывод красивого названия (например, «Январь 2023»).
• Выражение упорядочивания: Период — это сохранит хронологическую сортировку, так как система будет сравнивать даты, а не строки.

Обработка деления на ноль

Важно помнить о безопасности расчетов. Если отчет строится за период, в котором нет данных, делитель может оказаться равным нулю. Разберем пример безопасного выражения:

Выбор 
    Когда Количество(Различные НачалоПериода(Период, "Месяц")) = 0 
    Тогда 0 
    Иначе Окр(Сумма(Количество) / Количество(Различные НачалоПериода(Период, "Месяц")), 2) 
Конец

Использование функции Окр() (округление) также является хорошим тоном, чтобы итоговая колонка не содержала бесконечных десятичных дробей, что часто мешает восприятию отчета пользователем.

Подводя итог, можно сказать, что СКД предоставляет достаточно инструментов для реализации подобных задач без «костылей» в виде ручных макетов или дублирования данных в SQL-запросе. Главное — четко разделять области расчета ресурсов и использовать встроенные агрегатные функции.