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