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

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

Ограничения и сложности типовых механизмов

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

1. Отсутствие соединения по неравенству в связях СКД: штатными визуальными средствами СКД нельзя связать наборы данных по условию вхождения даты в интервал (Дата МЕЖДУ ДатаНачала И ДатаОкончания).
2. Неоптимальность соединений по диапазону в СУБД: если передавать таблицу интервалов во временную таблицу и выполнять соединение с регистрами по условию неравенства, СУБД обычно использует алгоритм Nested Loops (вложенные циклы), что критически замедляет отчет на больших массивах данных.
3. Проблема N+1 запросов: при передаче параметров периода из одного набора данных в виртуальную таблицу другого СКД выполняет отдельный запрос к базе для каждой строки интервала. При двух-трех периодах это незаметно, но при десятках интервалов время формирования отчета существенно возрастает.
4. Задвоение итогов при пересечении интервалов: если заданные пользователем периоды перекрывают друг друга (например, первая половина месяца и весь месяц целиком), то одни и те же движения попадут в оба интервала. Стандартный горизонтальный расчет общего итога просто просуммирует эти данные, что приведет к искажению сумм.

Способ 1. Связывание набора данных-объекта и набора данных-запроса

Разберем классический способ, который не требует модификации текста запроса «на лету» и настраивается непосредственно в схеме компоновки данных.

В этом сценарии мы разделяем данные на два набора:

1. Набор данных-объект («Периоды»): внешняя таблица значений, которую пользователь заполняет в интерфейсе. Она содержит колонки ДатаНачала, ДатаОкончания и ПредставлениеПериода (строка, которая будет заголовком колонки в кросс-таблице).
2. Набор данных-запрос («Обороты»): обращается к виртуальной таблице регистра и принимает даты через параметры.

Посмотрим на текст запроса для второго набора данных:

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

Теперь настроим параметры и связи наборов:

1. На вкладке «Параметры» схемы компоновки создаем параметры &НачалоПериода и &КонецПериода, снимаем у них флаг доступности пользователю.
2. Переходим на вкладку «Связи наборов данных».
3. Добавляем связь: Источник связи — «Периоды», Приемник связи — «Обороты».
4. В табличной части параметров связи указываем: для параметра НачалоПериода выражением источника будет поле ДатаНачала, а для КонецПериода — поле ДатаОкончания.
5. На вкладке «Настройки» формируем структуру отчета: добавляем таблицу, в строках указываем нужные группировки (например, Номенклатура), а в колонках выбираем поле ПредставлениеПериода из набора-объекта. В выбранных полях группировки колонок указываем ресурс СуммаОборот.

Перед формированием отчета в модуле передаем таблицу интервалов в процессор компоновки:

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

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

Способ 2. Предварительная агрегация по дням с объединением во временной таблице

Если периодов много, передача параметров в набор-запрос приведет к множественным обращениям к базе. Чтобы обойти это, применим подход с агрегацией по минимальной периодичности:

1. Программно или запросом определяем минимальную дату начала и максимальную дату окончания среди всех интервалов пользователя.
2. Запрашиваем обороты регистра один раз с периодичностью День на общий диапазон (от абсолютного минимума до абсолютного максимума) и помещаем во временную таблицу.
3. Таблицу интервалов также передаем как временную таблицу в единый пакетный запрос.
4. Соединяем подневные итоги с таблицей интервалов по условию вхождения даты дня в период.

Разберем пример запроса для такой схемы:

ВЫБРАТЬ
    ТаблицаПериодов.ПредставлениеПериода КАК ПредставлениеПериода,
    ОборотыПоДням.Номенклатура КАК Номенклатура,
    ОборотыПоДням.СуммаОборот КАК СуммаОборот
ИЗ
    ВТ_ОборотыПоДням КАК ОборотыПоДням
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Периоды КАК ТаблицаПериодов
        ПО ОборотыПоДням.ПериодДень >= ТаблицаПериодов.ДатаНачала
            И ОборотыПоДням.ПериодДень <= ТаблицаПериодов.ДатаОкончания

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

Способ 3. Программная генерация вычисляемых полей

Если интервалы гарантированно не пересекаются или требуется абсолютно исключить размножение записей, мы можем использовать генерацию вычисляемых полей через конструкцию ВЫБОР КОГДА.

В этом случае базовый запрос получает детальные обороты с периодичностью Регистратор или День за весь совокупный интервал. Затем перед компоновкой в коде выполняется алгоритм:

1. Очищаются динамические вычисляемые поля схемы.
2. Для каждой строки таблицы интервалов в СхемаКомпоновкиДанных.ВычисляемыеПоля добавляется поле (например, Сумма_Период_1, Сумма_Период_2).
3. Выражение для поля формируется по шаблону:
   Скопировать код

   
   ВычисляемоеПоле.Выражение = "ВЫБОР КОГДА Период >= &ДатаНач1 И Период <= &ДатаКон1 ТОГДА СуммаОборот ИНАЧЕ 0 КОНЕЦ";
   

4. Каждое поле регистрируется на вкладке «Ресурсы» с агрегатной функцией Сумма(...).
5. Поля помещаются в выбранные поля колонок кросс-таблицы.

Этот способ позволяет получить идеальный план выполнения одного запроса к базе данных, однако он требует написания программного кода по перенастройке структуры схемы.

Корректная настройка итогов кросс-таблицы

Если интервалы дат пересекаются, обязательно проконтролируйте итоговые строки отчета. Чтобы исключить некорректное суммирование одних и тех же движений в горизонтальном итоге кросс-таблицы, рекомендуется отключить автоматический расчет общих итогов по горизонтали в настройках группировки:

1. В настройках структуры отчета выберите элемент «Таблица».
2. В окне свойств перейдите к пункту «Расположение общих итогов по горизонтали».
3. Установите значение «Нет».

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