Как рассчитать отклонение от эталонного проекта в кросс-таблице СКД без создания дополнительных наборов данных?

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

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

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

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

Для реализации этого способа перейдем на закладку Ресурсы в схеме компоновки данных и создадим новое выражение для ресурса Отклонение (или создадим вспомогательный ресурс СуммаЭталона):

Сумма(Сумма) - ВычислитьВыражение("Сумма(Сумма)", , "ОбщийИтог", "Проект = &ПроектДляСравнения")

Разберем подробнее, как работают параметры этой функции в нашем контексте:

• Первый параметр ("Сумма(Сумма)") — это агрегатная функция, которую мы хотим вычислить для эталона.
• Третий параметр ("ОбщийИтог") — указывает СКД, что поиск данных нужно вести не в текущей колонке (где проект может не совпадать с эталоном), а во всем наборе данных отчета.
• Четвертый параметр ("Проект = &ПроектДляСравнения") — это ключевой момент. Он накладывает фильтр на выборку данных внутри функции. СКД найдет во всем наборе только те записи, где проект соответствует нашему параметру.

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

Способ 2. Распространение значения через «Максимум» и массив группировок

Если структура отчета сложная (например, используется многоуровневая иерархия), можно применить технику «проброса» значения через массив. Проанализируем этот алгоритм:

Во-первых, в самом запросе создадим поле, которое будет содержать сумму только для эталонного проекта, а для остальных — ноль:

ВЫБОР 
    КОГДА Проект = &ПроектДляСравнения ТОГДА Сумма 
    ИНАЧЕ 0 
КОНЕЦ КАК СуммаЭталона

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

Максимум(ВычислитьВыражениеСГруппировкойМассив("Сумма(СуммаЭталона)", "Проект"))

Выясним причину, почему это работает. Функция ВычислитьВыражениеСГруппировкойМассив соберет суммы эталона по всем проектам в массив. Так как для всех проектов, кроме одного, там будет 0, массив будет выглядеть примерно так: [0, 0, 5000, 0]. Функция Максимум выберет значение 5000 и подставит его в каждую ячейку текущей строки (Классификатора). Теперь мы можем легко вычислить отклонение простым вычитанием ресурсов.

Способ 3. Оптимизация на уровне запроса (Self-Join)

Рассмотрим ситуацию, когда расчеты крайне сложны и их предпочтительнее выполнить на стороне сервера СУБД. Если основной запрос «тяжелый», мы не будем его дублировать, а воспользуемся временными таблицами.

1. Поместим результат основного сложного запроса во временную таблицу ВТ_Данные.
2. В финальном запросе набора данных выполним левое соединение этой таблицы самой с собой.

Посмотрим на пример структуры такого запроса:

ВЫБРАТЬ
    Основная.Классификатор,
    Основная.Проект,
    Основная.Сумма КАК ТекущаяСумма,
    ЕСТЬNULL(Эталон.Сумма, 0) КАК СуммаЭталона
ИЗ
    ВТ_Данные КАК Основная
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Данные КАК Эталон
    ПО Основная.Классификатор = Эталон.Классификатор
    И Эталон.Проект = &ПроектДляСравнения

В данном случае мы получаем обе суммы в одной строке результата. Это делает расчет отклонения тривиальным — ТекущаяСумма - СуммаЭталона. Этот метод наиболее надежен при работе с большими объемами данных и сложными фильтрами, так как СКД получает уже готовые цифры для вывода.

Особенности работы с кросс-таблицами и иерархией

При настройке ресурсов важно обратить внимание на поле «Рассчитывать по...». Для ресурса отклонения убедитесь, что расчет ведется по всем необходимым группировкам (и по строкам, и по колонкам). Если вы используете иерархические справочники, во втором параметре функции ВычислитьВыражение рекомендуется явно указывать значение "Иерархия".

Полезный совет: Чтобы отчет не «развалился», если пользователь не выбрал проект для сравнения или по этому проекту нет данных, всегда оборачивайте вычисления в функцию ЕСТЬNULL(..., 0). Например:

Сумма(Сумма) - ЕСТЬNULL(ВычислитьВыражение("Сумма(Сумма)", , "ОбщийИтог", "Проект = &ПроектДляСравнения"), 0)

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