Разработка отчетов План-Факт является одной из самых частых и в то же время трудоемких задач. Основная сложность возникает, когда плановые показатели вводятся укрупненно (например, на всю номенклатурную группу), а фактические данные в бухгалтерии фиксируются детально (по конкретным элементам). В этой ситуации стандартное соединение таблиц в запросе часто приводит к потере данных или их «задвоению».
В данной статье мы разберем пошаговый алгоритм решения этой задачи, проанализируем типичные ошибки и рассмотрим продвинутые настройки Системы компоновки данных (СКД) для работы с иерархией.
Рассмотрим ситуацию: в документе планирования пользователь может указать как конкретную НоменклатурнуюГруппу, так и ее родительскую группу. Факт собирается со счета 90.01, где аналитика всегда привязана к конкретному элементу справочника. Если мы попытаемся сделать простое ЛЕВОЕ СОЕДИНЕНИЕ плана к факту по равенству ссылок, мы потеряем данные, так как ссылка «Группа» в плане не равна ссылке «Элемент» в факте.
Кроме того, если мы хотим видеть в отчете структуру всех групп (даже тех, по которым не было движений), нам необходим «эталонный» список всех номенклатурных групп.
Для начала нам необходимо подготовить данные. Мы не будем соединять всё в одном огромном пакете, а разделим логику на получение «каркаса» справочника, данных плана и данных факта. Проанализируем пример корректного запроса для получения плана:
// Получаем каркас всех нужных нам групп
ВЫБРАТЬ
НоменклатурныеГруппы.Ссылка КАК НоменклатурнаяГруппа
ПОМЕСТИТЬ ВТ_НоменклатурныеГруппы
ИЗ
Справочник.НоменклатурныеГруппы КАК НоменклатурныеГруппы
ГДЕ
НоменклатурныеГруппы.Ссылка В ИЕРАРХИИ(&НоменклатурнаяГруппа);
////////////////////////////////////////////////////////////////////////////////
// Получаем данные плана из документа (или регистра)
ВЫБРАТЬ
ПланированиеВыручки.Сумма КАК План,
ПланированиеВыручки.НоменклатурнаяГруппа КАК НоменклатурнаяГруппа
ПОМЕСТИТЬ ВТ_План
ИЗ
Документ.ПланированиеВыручки.НомеклатурныеГруппы КАК ПланированиеВыручки
ГДЕ
ПланированиеВыручки.Ссылка.Дата МЕЖДУ &НачалоПериода И &КонецПериода
И ПланированиеВыручки.Ссылка.Проведен;
////////////////////////////////////////////////////////////////////////////////
// Объединяем эталонный список с данными плана
ВЫБРАТЬ
ВТ_НоменклатурныеГруппы.НоменклатурнаяГруппа КАК НоменклатурнаяГруппа,
ЕСТЬNULL(ВТ_План.План, 0) КАК План
ИЗ
ВТ_НоменклатурныеГруппы КАК ВТ_НоменклатурныеГруппы
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_План КАК ВТ_План
ПО ВТ_НоменклатурныеГруппы.НоменклатурнаяГруппа = ВТ_План.НоменклатурнаяГруппа;
Обратите внимание на использование ЛЕВОЕ СОЕДИНЕНИЕ от таблицы ВТ_НоменклатурныеГруппы. Это гарантирует, что даже если в документе плана нет какой-то группы, она все равно попадет в итоговую выборку с нулевым значением.
Когда у нас есть отдельно набор данных «План» и набор данных «Факт», наиболее эффективным способом их «склейки» будет использование вкладки Связи наборов данных в СКД. Это гораздо гибче, чем соединения в тексте запроса.
Рассмотрим, как правильно настроить связи:
НаборСправочник, НаборПлан, НаборФакт.НаборСправочник как источник.НаборФакт укажем условие: Параметр связи — НоменклатурнаяГруппа, а в поле Условие связи выберем В ИЕРАРХИИ.Именно использование В ИЕРАРХИИ в связях наборов данных позволяет СКД автоматически сопоставить факт, упавший на элементы, с планом, заданным на уровне родительской группы.
При использовании иерархии часто возникает проблема: сумма плана по группе отображается корректно, но при развертывании до элементов эта же сумма «дублируется» в каждой строке или суммируется неверно. Чтобы этого избежать, проанализируем настройки ресурсов.
Вместо стандартного выражения СУММА(План) для плановых показателей, заданных на группы, иногда полезно использовать выражение:
Максимум(План)
Это выражение следует применять в разрезе группировки НоменклатурнаяГруппа. Таким образом, если на группу «Мебель» установлен план 100 000, то при любом количестве вложенных элементов общая сумма по группе останется равной 100 000, а не превратится в миллионы за счет сложения одинаковых плановых значений в каждой строке факта.
Иногда требуется скрыть лишние уровни иерархии или специфическим образом вычислить процент выполнения. Для этого в СКД существуют функции ВычислитьВыражение. Рассмотрим пример настройки отбора, чтобы избавиться от пустых уровней, которые могут возникать при сложных соединениях.
На закладке Настройки в пользовательских полях можно создать выражение Родитель:
ВычислитьВыражение("НоменклатурнаяГруппа", "", "Иерархия")
Далее, для группировки по номенклатурной группе можно применить фильтр После группировки или для иерархии, где ограничить вывод уровнем вложенности через системное поле УровеньВГруппировке. Это позволит «схлопнуть» пустые ветки дерева справочника.
Выясним, как добавить в этот же отчет второй раздел, например, затраты по 20 счету в разрезе статей затрат. Лучшая стратегия здесь — не пытаться соединить номенклатурные группы и статьи затрат в одну таблицу «в бок», так как это сущности разной природы.
Правильным подходом будет использование параллельных группировок в СКД:
НоменклатурнаяГруппа.СтатьяЗатрат.В результате в одном отчете мы получим два последовательных раздела. При этом общие параметры (Период, Организация) будут действовать на оба раздела одновременно, обеспечивая целостность данных.
Важный совет: всегда используйте ЕСТЬNULL при работе с суммами в запросах, особенно в левых соединениях. Отсутствие значения (NULL) может привести к тому, что арифметические операции в ресурсах СКД вернут пустой результат вместо ожидаемой суммы.