Как вывести все периоды и заполнить пользовательские поля при дополнении периодов в СКД?

← На главную

При разработке отчетов на базе Системы компоновки данных (СКД) программисты часто сталкиваются с необходимостью вывести хронологическую последовательность периодов целиком, даже если по некоторым из них в информационной базе отсутствуют обороты. Для решения этой задачи в СКД предусмотрен стандартный механизм — дополнение периодов.

Однако при его использовании возникает неприятная особенность: стандартные поля периодов (например, ПериодМесяц) СКД дополняет и заполняет корректно, а вот сопутствующие пользовательские поля (например, числовой номер месяца, рассчитанный в запросе) в «дорисованных» строках остаются пустыми (содержат значение NULL). Кроме того, попытка связать такой набор данных с данными прошлых лет по номеру месяца часто приводит к потере периодов будущих месяцев текущего года.

В этой статье мы подробно разберем природу этого поведения СКД и рассмотрим несколько практических способов решения проблемы, которые позволят вам построить красивый и корректный отчет.

Выясняем причину: почему поля из запроса «теряются» при дополнении?

Для начала проанализируем, как работает механизм дополнения СКД. Когда мы выполняем запрос к виртуальной таблице остатков и оборотов, СУБД возвращает только те строки, по которым физически есть записи в базе данных. Если за август и сентябрь движений не было, СУБД этих строк не вернет.

Когда в настройках компоновки для поля группировки (например, ПериодМесяц) мы устанавливаем тип дополнения «Месяц», СКД берет результат запроса и на этапе компоновки результата искусственно генерирует строки для недостающих месяцев. При этом:

Значения генерируются только для тех полей, у которых в наборе данных на уровне ролей явно прописана роль «Период» с соответствующим типом.
Любые другие поля, которые мы рассчитали непосредственно в тексте запроса СУБД (например, выражение МЕСЯЦ(ПериодМесяц) КАК ПериодМесяцЧисло), для искусственно сгенерированных строк будут равны NULL. СУБД ничего не знает о строках, которые СКД «дорисовала» позже в оперативной памяти.

Рассмотрим по шагам три надежных способа обхода этой проблемы.

Способ 1. Использование механизма «Вычисляемых полей» СКД

Это самый простой и лаконичный способ, если вам нужно просто вывести сопутствующее значение (например, номер месяца, квартал или год) для дополненных строк, не усложняя текст запроса.

Поскольку СКД сама генерирует дату в поле периода, мы можем настроить вычисление производных полей на уровне самой СКД, а не в запросе к СУБД. Вычисляемые поля рассчитываются на самом финальном этапе компоновки, когда строки периодов уже дополнены.

Выполним следующие действия:

Перейдем в схему компоновки данных и откроем вкладку «Набор данных».

Изменим текст запроса, удалив из него расчет номера месяца на стороне СУБД. Наш исходный запрос будет выглядеть примерно так:


ВЫБРАТЬ
    ВзаиморасчетыОбороты.Организация КАК Организация,
    ВзаиморасчетыОбороты.Контрагент КАК Контрагент,
    ВзаиморасчетыОбороты.ПериодМесяц КАК ПериодМесяц,
    ВзаиморасчетыОбороты.СуммаОборот КАК СуммаОборот
ИЗ
    РегистрНакопления.ВзаиморасчетыСКонтрагентами.Обороты(&НачалоПериода, &КонецПериода, Месяц, ) КАК ВзаиморасчетыОбороты

Перейдем на вкладку «Вычисляемые поля».
Добавим новую строку:
- Путь к данным: ПериодМесяцЧисло
- Выражение: Месяц(ПериодМесяц)
- Заголовок: Номер месяца
На вкладке «Настройки» в выбранных полях группировки укажем наше вычисляемое поле ПериодМесяцЧисло.

Теперь, когда СКД дополнит недостающие периоды (например, добавит отсутствующий август), она запишет в поле ПериодМесяц дату начала месяца (01.08.202X), а затем успешно вычислит функцию Месяц() для этой даты. Поле больше не будет пустым!

Способ 2. Генерация «сетки» периодов (календаря) в запросе

Метод с вычисляемыми полями отлично работает для отображения данных. Однако, если перед вами стоит более сложная задача — например, связать текущие данные с данными прошлого года по числу месяца (как описывал автор темы), — вычисляемые поля СКД могут не подойти для организации связей наборов данных.

В этом случае классическим и наиболее профессиональным решением является генерация «сетки» периодов прямо в запросе. Мы принудительно создаем таблицу, гарантированно содержащую все 12 месяцев года, и затем левым соединением подключаем к ней таблицы остатков и оборотов.

Проанализируем пример запроса, создающего такую сетку:


ВЫБРАТЬ
    1 КАК НомерМесяца
ПОМЕСТИТЬ ВТ_Месяцы
ОБЪЕДИНИТЬ ВЫБРАТЬ 2
ОБЪЕДИНИТЬ ВЫБРАТЬ 3
ОБЪЕДИНИТЬ ВЫБРАТЬ 4
ОБЪЕДИНИТЬ ВЫБРАТЬ 5
ОБЪЕДИНИТЬ ВЫБРАТЬ 6
ОБЪЕДИНИТЬ ВЫБРАТЬ 7
ОБЪЕДИНИТЬ ВЫБРАТЬ 8
ОБЪЕДИНИТЬ ВЫБРАТЬ 9
ОБЪЕДИНИТЬ ВЫБРАТЬ 10
ОБЪЕДИНИТЬ ВЫБРАТЬ 11
ОБЪЕДИНИТЬ ВЫБРАТЬ 12
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВТ_Месяцы.НомерМесяца КАК ПериодМесяцЧисло,
    ЕСТЬNULL(ДанныеТекущегоГода.СуммаНачальныйОстаток, 0) КАК НачальныйОстаток,
    ЕСТЬNULL(ДанныеТекущегоГода.СуммаКонечныйОстаток, 0) КАК КонечныйОстаток,
    ЕСТЬNULL(ДанныеПрошлогоГода.СуммаОборот, 0) КАК ОборотПрошлыйГод
ИЗ
    ВТ_Месяцы КАК ВТ_Месяцы
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ВзаиморасчетыСКонтрагентами.ОстаткиИОбороты(&НачалоГода, &КонецГода, Авто, , ) КАК ДанныеТекущегоГода
        ПО ВТ_Месяцы.НомерМесяца = МЕСЯЦ(ДанныеТекущегоГода.ПериодМесяц)
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ВзаиморасчетыСКонтрагентами.Обороты(&НачалоПрошлогоГода, &КонецПрошлогоГода, Месяц, ) КАК ДанныеПрошлогоГода
        ПО ВТ_Месяцы.НомерМесяца = МЕСЯЦ(ДанныеПрошлогоГода.ПериодМесяц)

Преимущества данного подхода:

Запрос гарантированно возвращает ровно 12 строк (по одной на каждый месяц), вне зависимости от наличия фактических движений в информационной базе.
Данные текущего и прошлого года сопоставляются абсолютно корректно на уровне СУБД.
Нам больше не требуется включать механизм дополнения периодов в настройках СКД, что существенно повышает производительность и предсказуемость построения отчета.
Исключаются любые проблемы со связями наборов данных на уровне СКД.

Способ 3. Объединение наборов данных вместо связей

Если данные прошлого и текущего года находятся в разных физических таблицах или базах и вы вынуждены использовать несколько наборов данных в СКД, избегайте связи наборов по типу «Левое соединение» на вкладке «Связи наборов данных», если в ведущем наборе могут отсутствовать периоды.

Вместо этого используйте структуру «Набор данных - Объединение»:

Создайте корневой набор данных с типом Объединение.
Внутрь него поместите два набора данных-запроса: НаборДанныхТекущийГод и НаборДанныхПрошлыйГод.
В обоих запросах приведите поля к единому наименованию (например, ПериодМесяцЧисло и соответствующие ресурсы).
В запросе прошлого года вы можете транслировать даты на текущий год или оперировать только номерами месяцев.
СКД самостоятельно объединит эти наборы. Если в текущем году нет данных за 4-й квартал, но они есть в прошлом году, строка с номером месяца все равно попадет в итоговую выборку. Механизм дополнения периодов СКД отработает корректно для всей объединенной коллекции строк.

Выбор конкретного метода зависит от архитектуры вашего отчета. Для простых отчетов без соединений идеально подойдет Способ 1 (Вычисляемые поля). Для сложных аналитических таблиц со сравнением периодов «год к году» лучшим и наиболее надежным решением станет Способ 2 (Календарная сетка в запросе).