При разработке управленческих и финансовых отчетов на платформе 1С перед программистом часто возникает сложная задача: построить двумерную таблицу (кросс-таблицу), в которой строки представляют собой различные финансовые показатели (Доходы, Расходы, Прибыль, Рентабельность), а колонки — определенные аналитики (проекты, контрагенты, номенклатурные группы или подразделения). При этом заказчик требует дополнительно сгруппировать эти колонки по динамическому признаку, например, разделить их на две группы: "Рентабелен" и "Не рентабелен".
Сложность заключается в том, что сама рентабельность является одним из рассчитываемых показателей в строках отчета. Начинающие разработчики часто пытаются решить эту задачу "в лоб" — через настройки ресурсов Системы компоновки данных (СКД). Однако они быстро сталкиваются с системным ограничением: группировки колонок и строк в СКД формируются до того, как рассчитываются ресурсы в ячейках пересечений. Соответственно, система не может использовать значение ресурса "Рентабельность" для создания родительской группировки колонок.
В этой статье мы подробно разберем природу этой проблемы и рассмотрим три эффективных, проверенных на практике способа реализации такой группировки.
Для того чтобы выбрать оптимальный путь решения, нам необходимо четко понимать жизненный цикл формирования отчета в СКД. Процесс компоновки данных проходит через несколько ключевых этапов:
СУММА, СРЕДНЕЕ и т.д.).Из этой цепочки очевидно, что на этапе построения группировок колонок (этап 2) система еще "не знает", чему будет равна рентабельность конкретной аналитики (этап 3). Следовательно, мы должны рассчитать признак рентабельности заранее. Выясним, как это сделать.
Это самый надежный, производительный и простой в отладке способ. Мы переносим всю математику расчета рентабельности по аналитикам непосредственно в тело запроса СКД с использованием временных таблиц. В результате СКД получает готовый признак для группировки еще на этапе выборки данных.
Разберем по шагам, как построить такой запрос в конструкторе:
Шаг 1. Создание временной таблицы доходов и расходов по аналитикам
Сначала нам нужно агрегировать финансовые показатели в разрезе аналитик, без детализации по остальным показателям. Создадим временную таблицу ВТ_ИтогиПоАналитикам:
ВЫБРАТЬ
Продажи.Аналитика КАК Аналитика,
СУММА(Продажи.Доход) КАК Доход,
СУММА(Продажи.Расход) КАК Расход
ПОМЕСТИТЬ ВТ_ИтогиПоАналитикам
ИЗ
РегистрНакопления.Продажи КАК Продажи
СГРУППИРОВАТЬ ПО
Продажи.Аналитика
Шаг 2. Расчет коэффициента рентабельности и определение текстового признака
Во второй временной таблице ВТ_ПризнакиРентабельности мы рассчитываем отношение прибыли к расходам и на основе полученного числа формируем строковое значение ("Рентабелен" / "Не рентабелен"):
ВЫБРАТЬ
Т.Аналитика КАК Аналитика,
ВЫБОР
КОГДА Т.Расход = 0 И Т.Доход > 0 ТОГДА "Рентабелен"
КОГДА Т.Расход = 0 И Т.Доход <= 0 ТОГДА "Не рентабелен"
КОГДА (Т.Доход - Т.Расход) / Т.Расход > 0 ТОГДА "Рентабелен"
ИНАЧЕ "Не рентабелен"
КОНЕЦ КАК ПризнакРентабельности
ПОМЕСТИТЬ ВТ_ПризнакиРентабельности
ИЗ
ВТ_ИтогиПоАналитикам КАК Т
Шаг 3. Соединение признака с основным набором данных
Теперь мы объединяем нашу таблицу признаков с основной таблицей, которая содержит детальные записи по всем показателям для вывода в строки отчета:
ВЫБРАТЬ
ОсновнойНабор.Показатель КАК Показатель,
ОсновнойНабор.Аналитика КАК Аналитика,
ОсновнойНабор.ЗначениеПоказателя КАК ЗначениеПоказателя,
ЕСТЬNULL(ТаблицаРентабельности.ПризнакРентабельности, "Не рентабелен") КАК ПризнакРентабельности
ИЗ
РегистрСведений.ПоказателиАналитик КАК ОсновнойНабор
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ПризнакиРентабельности КАК ТаблицаРентабельности
ПО ОсновнойНабор.Аналитика = ТаблицаРентабельности.Аналитика
Шаг 4. Настройка структуры отчета в СКД
Переходим на вкладку "Настройки" в схеме компоновки данных и настраиваем структуру нашего отчета типа "Таблица":
Показатель.ПризнакРентабельности.Аналитика.ЗначениеПоказателя.При такой структуре СКД автоматически разделит все колонки-аналитики на две большие группы ("Рентабелен" и "Не рентабелен"), а внутри них выведет детальные аналитики с их показателями.
Рассмотрим ситуацию, когда мы не можем изменить исходный запрос (например, данные поступают из внешнего источника данных, сложного веб-сервиса или готовой типовой функции, которую запрещено редактировать). В этом случае на помощь приходит встроенный язык выражений СКД.
Мы можем создать вычисляемое поле, расчет которого будет производиться в оперативной памяти процессора компоновки данных.
Алгоритм настройки вычисляемого поля:
ПризнакРентабельностиРентабельность аналитикиВычислитьВыражение. Данная функция позволяет рассчитывать итоги по определенной группировке "на лету":
Выбор
Когда (ВычислитьВыражение("Сумма(Доход)", "Аналитика") - ВычислитьВыражение("Сумма(Расход)", "Аналитика")) / ВычислитьВыражение("Сумма(Расход)", "Аналитика") > 0
Тогда "Рентабелен"
Иначе "Не рентабелен"
Конец
Важная техническая деталь: Второй параметр функции ВычислитьВыражение указывает контекст расчета. Передавая туда строку "Аналитика", мы заставляем систему рассчитать суммы доходови расходов в разрезе каждого значения аналитики отдельно, независимо от того, какие еще группировки присутствуют в таблице. Полученный строковый результат мы можем использовать в настройках структуры отчета в качестве группировки колонок точно так же, как описано в Шаге 4 первого способа.
Примечание: Этот способ элегантен, но более требователен к вычислительным ресурсам сервера, так как расчеты производятся силами платформы 1С, а не СУБД.
Если требования к визуальному оформлению отчета крайне строгие (например, необходимо объединять ячейки шапки особым образом, рисовать кастомные границы или выводить сложные расшифровки), лучшим решением будет программный вывод отчета с последующей постобработкой результирующего табличного документа.
Для этого мы переопределяем стандартную обработку формирования отчета в модуле объекта отчета. Разберем шаблон кода, реализующий эту логику:
Процедура ПриКомпоновкеРезультата(ДокументРезультат, ДанныеРасшифровки, СтандартнаяОбработка)
// Отменяем стандартный вывод отчета системой
СтандартнаяОбработка = Ложь;
// Настраиваем программный вывод
КомпоновщикМакета = Новый КомпоновщикМакетаКомпоновкиДанных;
МакетКомпоновки = КомпоновщикМакета.Выполнить(СхемаКомпоновкиДанных, КомпоновщикНастроек.ПолучитьНастройки(), ДанныеРасшифровки);
ПроцессорКомпоновки = Новый ПроцессорКомпоновкиДанных;
ПроцессорКомпоновки.Инициализировать(МакетКомпоновки, , ДанныеРасшифровки, Истина);
ПроцессорВывода = Новый ПроцессорВыводаРезультатаКомпоновкиДанныхВТабличныйДокумент;
ПроцессорВывода.УстановитьДокумент(ДокументРезультат);
ПроцессорВывода.Вывести(ПроцессорКомпоновки);
// Начинаем программную модификацию полученного табличного документа
ДокументРезультат.Область().Защита = Ложь; // Снимаем защиту для редактирования
// Определяем координаты строки с показателем "Рентабельность"
НомерСтрокиРентабельности = 0;
Для НомерСтроки = 1 По ДокументРезультат.ВысотаТаблицы Цикл
ТекстЯчейки = ДокументРезультат.Область(НомерСтроки, 1).Текст;
Если ТекстЯчейки = "Рентабельность" Тогда
НомерСтрокиРентабельности = НомерСтроки;
Прервать;
КонецЕсли;
КонецЦикла;
Если НомерСтрокиРентабельности > 0 Тогда
// Вставляем новую строку над шапкой для группировки "Рентабелен / Не рентабелен"
ДокументРезультат.ВставитьОбласть(
ДокументРезультат.Область(1, 1, 1, ДокументРезультат.ШиринаТаблицы),
ДокументРезультат.Область(1, 1, 1, ДокументРезультат.ШиринаТаблицы),
ТипСмещенияТабличногоДокумента.ПоВертикали
);
// Пробегаем по колонкам аналитик и объединяем их по признаку значения рентабельности
НачалоГруппы = 0;
ТекущийПризнак = "";
Для НомерКолонки = 2 По ДокументРезультат.ШиринаТаблицы Цикл
// Получаем значение рентабельности (строка увеличилась на 1 после вставки новой строки)
ЗначениеРентабельности = Число(ДокументРезультат.Область(НомерСтрокиРентабельности + 1, НомерКолонки).Текст);
ПризнакКолонки = ?(ЗначениеРентабельности > 0, "Рентабелен", "Не рентабелен");
Если ТекущийПризнак <> ПризнакКолонки Тогда
// Объединяем предыдущую группу колонок
Если НачалоГруппы > 0 И (НомерКолонки - 1) >= НачалоГруппы Тогда
ОбластьШапки = ДокументРезультат.Область(1, НачалоГруппы, 1, НомерКолонки - 1);
ОбластьШапки.Объединить();
ОбластьШапки.Текст = ТекущийПризнак;
ОбластьШапки.ГоризонтальноеПоложение = ГоризонтальноеПоложение.Центр;
КонецЕсли;
НачалоГруппы = НомерКолонки;
ТекущийПризнак = ПризнакКолонки;
КонецЕсли;
КонецЦикла;
// Не забываем объединить последнюю группу колонок
Если НачалоГруппы > 0 Тогда
ОбластьШапки = ДокументРезультат.Область(1, НачалоГруппы, 1, ДокументРезультат.ШиринаТаблицы);
ОбластьШапки.Объединить();
ОбластьШапки.Текст = ТекущийПризнак;
ОбластьШапки.ГоризонтальноеПоложение = ГоризонтальноеПоложение.Центр;
КонецЕсли;
КонецЕсли;
КонецПроцедуры
Данный код находит в результирующей таблице строку, отвечающую за рентабельность, анализирует значения в каждой колонке, вставляет дополнительную строку сверху и красиво объединяет ячейки шапки с выводом соответствующего текста.
Помимо группировки колонок, часто возникает сопутствующий вопрос: как правильно рассчитать значение рентабельности в итоговой строке или в пересечениях, если в отчете выводятся разные типы показателей (например, Доход — это сумма, а Рентабельность — процентное соотношение)?
Для корректного вычисления различных показателей в одной колонке ресурса используйте условное выражение на вкладке "Ресурсы". Для поля ЗначениеПоказателя выражение расчета должно выглядеть следующим образом:
ВЫБОР
КОГДА Показатель = &ПоказательРентабельность
ТОГДА (СУММА(Доход) - СУММА(Расход)) / ВЫБОР КОГДА СУММА(Расход) = 0 ТОГДА 1 ИНАЧЕ СУММА(Расход) КОНЕЦ
ИНАЧЕ СУММА(ЗначениеПоказателя)
КОНЕЦ
Это выражение гарантирует, что обычные числовые показатели будут суммироваться стандартным образом, а для строки рентабельности система применит формулу расчета маржинальности, избегая при этом ошибки деления на ноль.
Мы проанализировали три различных подхода к решении задачи группировки по расчетному ресурсу:
Выбирайте наиболее подходящий вариант в зависимости от архитектурных ограничений вашего проекта!