При разработке сложных отчетов в системе компоновки данных (СКД) разработчики часто сталкиваются с задачей вывода показателей «вертикально» — то есть строками, а не колонками. Типичный пример — отчеты финансового характера (БДР, БДДС) или аналитические отборы, где друг за другом идут строки: «Продажи», «Себестоимость» и расчетная строка «Наценка %».
Основная сложность возникает при выводе иерархии номенклатуры или контрагентов. Если детальные записи наценки рассчитываются в запросе и выводятся как готовые числа, то СКД на уровне иерархических папок просто складывает эти проценты между собой. В результате вместо правильного средневзвешенного процента наценки (например, 40%) мы получаем бессмысленную сумму процентов по детальным строкам (например, 70%).
В этой статье мы подробно разберем, почему возникает эта проблема и рассмотрим несколько эффективных способов ее решения: от классического условного ресурса до продвинутого архитектурного метода связи наборов данных.
Давайте проанализируем ситуацию на математическом уровне. Допустим, у нас есть группа номенклатуры «Вкусняшки», в которую входят два товара:
Если считать наценку по иерархической группе «Вкусняшки» в целом, мы должны взять общую себестоимость группы (60), разделить на общие продажи (150) и умножить на 100. Математически верный результат: 40%.
Однако, если показатель наценки рассчитывается непосредственно в запросе как отдельная строка и передается в СКД в готовом виде, то для папки «Вкусняшки» СКД применит стандартное агрегатное выражение ресурса — чаще всего Сумма(ПолеЗначение). В итоге система просто сложит проценты дочерних элементов: 50% + 20% = 70%. Это происходит потому, что на уровне иерархического родителя СКД «не знает» исходных данных (продаж и себестоимости) конкретно для этой строки, так как они находятся в других строках отчета.
---
Самый простой способ решить проблему без кардинального изменения структуры отчета — перенести расчет формулы из запроса на уровень ресурсов СКД и использовать условное выражение ВЫБОР КОГДА.
Для реализации этого метода выполним следующие шаги:
Шаг 1. Модернизируем исходный запрос.
Нам необходимо сделать так, чтобы в строке с расчетным показателем (Наценка) в базу данных также попадали исходные показатели (Продажи и Себестоимость). Для этого во всех объединяемых таблицах запроса должны присутствовать соответствующие поля, пусть даже со значениями 0 в нецелевых строках.
Примерная схема запроса будет выглядеть так:
// Таблица 1. Продажи
ВЫБРАТЬ
1 КАК НомерРаздела,
"Продажи" КАК НазваниеРаздела,
Номенклатура,
СуммаПродаж КАК Продажи,
0 КАК Себестоимость,
СуммаПродаж КАК ЗначениеПоказателя
ПОМЕСТИТЬ ВТ_Продажи...
;
// Таблица 2. Себестоимость
ВЫБРАТЬ
2 КАК НомерРаздела,
"Себестоимость" КАК НазваниеРаздела,
Номенклатура,
0 КАК Продажи,
СуммаСебестоимости КАК Себестоимость,
СуммаСебестоимости КАК ЗначениеПоказателя
ПОМЕСТИТЬ ВТ_Себестоимость...
;
// Таблица 3. Наценка (базовые показатели берем из первых двух таблиц)
ВЫБРАТЬ
3 КАК НомерРаздела,
"Наценка, %" КАК НазваниеРаздела,
Номенклатура,
Продажи,
Себестоимость,
0 КАК ЗначениеПоказателя // Само значение здесь считать НЕ нужно!
ПОМЕСТИТЬ ВТ_Наценка...
В итоговом пакете мы объединяем все три таблицы и выводим их в СКД.
Шаг 2. Настраиваем ресурсы СКД.
Переходим на вкладку «Ресурсы» схемы компоновки данных. Для поля ЗначениеПоказателя пишем специальное выражение, которое будет определять логику расчета в зависимости от выводимого раздела:
Выбор
Когда НомерРаздела = 3 Тогда
Выбор
Когда Сумма(Продажи) = 0 Тогда 0
Иначе Сумма(Себестоимость) / Сумма(Продажи) * 100
Конец
Иначе
Сумма(ЗначениеПоказателя)
Конец
Как это работает: При расчете детальных записей и иерархических итогов для Разделов 1 и 2 СКД применит обычное суммирование. Когда же система начнет рассчитывать итоги по иерархии номенклатуры для Раздела 3 (Наценка), она обратится к агрегированным величинам Сумма(Себестоимость) и Сумма(Продажи) по этой ветке иерархии и разделит их друг на друга. Мы получим математически верные 40%.
---
Если в вашем отчете не 3 раздела, а 20 или более, то использование первого метода приведет к сильному усложнению запроса. Вам придется тащить десятки служебных колонок через все объединения (UNION ALL). Чтобы избежать этого, спроектируем отчет более профессионально — с помощью связи двух наборов данных в СКД.
Рассмотрим этот подход детально:
Шаг 1. Создаем Набор данных №1 (Объектный/Запрос).
В этом наборе мы получаем чистые физические данные из базы данных в разрезе номенклатуры без каких-либо объединений по показателям. Каждому базовому значению соответствует своя колонка:
Выбрать
Номенклатура,
СуммаПродаж КАК Продажи,
СуммаСебестоимости КАК Себестоимость,
ДополнительныйПоказатель...
Из
РегистрНакопления.Продажи
Шаг 2. Создаем Набор данных №2 (Справочник разделов).
Этот набор будет содержать исключительно структуру строк нашего отчета. Это может быть как обычный запрос, так и статическая таблица значений:
Выбрать 1 как КодРаздела, "Продажи" как НазваниеРаздела
Объединить все
Выбрать 2, "Себестоимость"
Объединить все
Выбрать 3, "Наценка"
Шаг 3. Настраиваем связи наборов данных.
Переходим на вкладку «Связи наборов данных». Связываем Набор данных №2 (источник) с Набором данных №1 (приемник). В качестве условия связи указываем выражение Истина (или просто оставляем поля связи пустыми, если платформа позволяет сделать декартово произведение). Это свяжет каждую строчку разделов со всеми записями номенклатуры.
Шаг 4. Пишем универсальное выражение ресурса.
В поле ресурса Значение на вкладке «Ресурсы» прописываем формулы для каждого раздела:
Выбор
Когда КодРаздела = 1 Тогда Сумма(Продажи)
Когда КодРаздела = 2 Тогда Сумма(Себестоимость)
Когда КодРаздела = 3 Тогда
Выбор Когда Сумма(Продажи) = 0 Тогда 0
Иначе Сумма(Себестоимость) / Сумма(Продажи) * 100 Конец
Конец
Преимущества архитектурного метода:
---
Если бизнес-требования позволяют изменить структуру вывода отчета таким образом, чтобы группировка «Номенклатура» была родительской, а показатели (Разделы) — подчиненными, мы можем использовать мощный аналитический инструмент СКД — функцию ВычислитьВыражение.
В структуре настроек отчета это выглядит так:
Для того чтобы в строке раздела «Наценка» получить данные из соседних строк («Себестоимость» и «Продажи»), мы пишем следующее выражение для ресурса:
Выбор
Когда Раздел = "Наценка" Тогда
ВычислитьВыражение("Сумма(Выбор Когда Раздел = 'Себестоимость' Тогда Значение Иначе 0 Конец)", "Номенклатура")
/
ВычислитьВыражение("Сумма(Выбор Когда Раздел = 'Продажи' Тогда Значение Иначе 0 Конец)", "Номенклатура")
* 100
Иначе
Сумма(Значение)
Конец
Разберем по шагам, как это работает:
"Номенклатура" указывает функции ВычислитьВыражение, что расчет нужно производить в контексте текущего элемента номенклатуры.Для корректного расчета сложных формул и процентов по иерархии в СКД никогда не считайте конечный результат внутри детальных записей запроса. Всегда передавайте в компоновку базовые показатели и настраивайте расчет формул в ресурсах схемы. Выбор конкретного метода зависит от масштаба вашего отчета: