Как использовать таблицу значений в качестве временной для запроса СКД: дополнение периодов и работа с внешними данными

← На главную

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

Метод 1. Использование стандартного механизма дополнения периодов

Рассмотрим распространенное заблуждение: многие считают, что стандартное дополнение периодов в СКД работает только с виртуальными таблицами регистров накопления. На самом деле это не так. СКД умеет дополнять любые данные, если правильно настроены роли полей. Проанализируем этот алгоритм.

Для того чтобы система «увидела» необходимость вывода пустых интервалов, выполним следующие шаги:

В наборе данных (запросе) выберем поле даты.
На вкладке «Наборы данных» в колонке «Роль» для этого поля нажмем на кнопку редактирования.
Установим флажок «Период» и укажем тип периода (например, День, Месяц).
В настройках отчета, на вкладке «Группировки», выберем наше поле периода.
В свойствах группировки установим «Тип дополнения» (например, Месяц), а также укажем параметры «Начало периода» и «Конец периода».

Этот метод является самым «чистым», так как не требует написания кода и работает полностью на уровне настроек компоновки.

Метод 2. Использование регистра «Данные производственного календаря»

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

Разберем, как это реализовать в запросе:

Выберем все даты из регистра за нужный интервал. Это будет наша «эталонная» таблица периодов.
Применим ЛЕВОЕ СОЕДИНЕНИЕ этой таблицы с вашими основными данными.
Используем функцию ЕСТЬNULL(Данные.Значение, 0), чтобы вместо пустых записей выводились нули.

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

Метод 3. Набор данных — объект и связь наборов

Когда таблицу периодов необходимо сформировать программно (например, на основе сложной бизнес-логики в модуле), мы можем использовать «Набор данных - объект». Рассмотрим, как передать таблицу значений в СКД и связать её с основным запросом.

Сначала подготовим таблицу в коде:


ТЗ_Периоды = Новый ТаблицаЗначений;
ТЗ_Периоды.Колонки.Добавить("Период", Новый ОписаниеТипов("Дата"));
// Заполняем ТЗ циклом по датам
...
ДанныеРасшифровки = Новый ДанныеРасшифровкиКомпоновкиДанных;
КомпоновщикМакета = Новый КомпоновщикМакетаКомпоновкиДанных;
МакетКомпоновки = КомпоновщикМакета.Выполнить(СхемаКомпоновкиДанных, КомпоновщикНастроек.ПолучитьНастройки(), ДанныеРасшифровки);

ВнешниеНаборыДанных = Новый Структура;
ВнешниеНаборыДанных.Вставить("ТаблицаПериодов", ТЗ_Периоды);

ПроцессорКомпоновки = Новый ПроцессорКомпоновкиДанных;
ПроцессорКомпоновки.Инициализировать(МакетКомпоновки, ВнешниеНаборыДанных, ДанныеРасшифровки);

В самой схеме СКД создаем второй набор данных с типом «Объект», называем его ТаблицаПериодов. На вкладке «Связи наборов данных» указываем связь от таблицы периодов к основному запросу по полю даты. Важно помнить: такое соединение выполняется в памяти сервера приложений, поэтому не рекомендуется передавать таким образом огромные массивы данных.

Метод 4. Порождающий запрос (генерация дат в SQL)

Выясним причину, почему программисты часто избегают передачи ТЗ. Это связано с тем, что соединение «объекта» и «запроса» в СКД работает медленнее, чем чистый SQL. Можно сгенерировать последовательность дат прямо в запросе. Рассмотрим пример «порождающего запроса»:

Мы можем взять любую таблицу, в которой гарантированно есть записи (например, системную таблицу пользователей или справочник валют), и выполнить кросс-соединение (соединение «каждого с каждым») самой с собой несколько раз. Получив таблицу из 10 000 строк, мы можем пронумеровать их функцией АВТОНОМЕРЗАПИСИ() и добавить это число к начальной дате периода. Это позволит получить непрерывный календарь без использования внешних объектов.

Метод 5. Динамическая подмена текста запроса (Технология представлений)

Этот метод считается наиболее продвинутым и часто используется в сложных типовых решениях, таких как ЗУП. Суть заключается в том, чтобы в тексте запроса СКД оставить «заплатку» — пустую временную таблицу, которую мы заполним реальными данными UNION ALL прямо перед выполнением отчета.

Рассмотрим пример запроса-шаблона в СКД:


ВЫБРАТЬ
    ДАТАВРЕМЯ(1, 1, 1) КАК ДатаНачала,
    ДАТАВРЕМЯ(1, 1, 1) КАК ДатаОкончания
ПОМЕСТИТЬ ВтПериоды
ГДЕ
    &ПараметрПериоды = ИСТИНА
;
//////////////////////////////////////////////////////////
ВЫБРАТЬ * ИЗ ВтПериоды КАК ВтПериоды
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.Продажи КАК Продажи ...

В модуле объекта отчета, в процедуре ПриКомпоновкеРезультата, мы перехватываем схему и программно модифицируем текст запроса. Проанализируем фрагмент кода, который подменяет «пустышку» реальными данными из вашей таблицы значений:


Процедура ПриКомпоновкеРезультата(ДанныеРасшифровки, СтандартнаяОбработка)
    СтандартнаяОбработка = Ложь;
    
    // Получаем текст запроса из схемы
    ТекстЗапроса = СхемаКомпоновкиДанных.НаборыДанных.НаборДанных1.Запрос;
    
    // Генерируем строку из UNION ALL на основании нашей ТЗ
    ТекстВставки = "";
    Для Каждого СтрокаТЗ Из МояТаблицаПериодов Цикл
        ТекстВставки = ТекстВставки + " ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ ДАТАВРЕМЯ(" 
                     + Формат(СтрокаТЗ.Дата, "ДФ=yyyy,MM,dd") + ")";
    EndЦикла;
    
    // Заменяем маркер в запросе на сгенерированный текст
    НовыйТекст = СтрЗаменить(ТекстЗапроса, "&ПараметрПериоды = ИСТИНА", " ЛОЖЬ " + ТекстВставки);
    СхемаКомпоновкиДанных.НаборыДанных.НаборДанных1.Запрос = НовыйТекст;
    
    // Далее стандартный вывод отчета
    ...
КонецПроцедуры

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

Инструменты отладки

Проанализируем ситуацию с отладкой: как проверить, что мы «налепили» в коде? Для этого рекомендуется использовать специализированные консоли запросов и отчетов:

Консоль системы компоновки данных: позволяет выгрузить макет, который сформировал ваш код, и посмотреть итоговый текст запроса со всеми подстановками.
Инструменты разработчика: позволяют перехватить запрос на уровне СУБД. Это единственный надежный способ увидеть, как временная таблица, созданная из UNION ALL, выглядит в глазах SQL-сервера.

Выясним, какой метод выбрать? Если ваша задача — просто вывести пустые месяцы, начните с Метода 1 (настройка ролей). Если логика формирования периодов сложная и зависит от внешних данных, используйте Метод 5 для максимальной производительности или Метод 3 для простоты реализации.