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