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