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

← На главную

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

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

Выясняем причину: почему ручная группировка остатков в запросе — это ошибка

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

Остатки — это не агрегируемые величины. В отличие от оборотов, которые можно просто сложить функцией СУММА(), остатки нельзя складывать между собой по временной шкале. Если мы группируем записи по периодам программно в SQL-запросе, СУБД начинает группировать остатки как обычные числа, что полностью искажает логику их расчета на начало и конец каждого интервала.
Влияние поля Регистратор. Если в параметрах виртуальной таблицы установлена периодичность Авто, а в самом запросе или настройках компоновки присутствует поле Регистратор (даже если оно используется косвенно в условиях), система автоматически детализирует остатки до каждого документа движения. При ручном сворачивании такого запроса мы получаем множество строк с пустыми интервалами и раздробленными остатками.
Игнорирование механизмов СКД. Система компоновки данных спроектирована так, чтобы самостоятельно управлять расчетом остатков. Для этого ей требуются «чистые» данные из виртуальных таблиц без предварительной группировки силами СУБД.

Ниже приведены три надежных способа решения этой задачи.

Решение 1. Настройка ролей полей компоновки данных (самый правильный и лаконичный способ)

Чтобы СКД корректно сворачивала остатки по выбранным периодам без дублирования строк, необходимо полностью убрать ручную группировку (секцию СГРУППИРОВАТЬ ПО) из текста запроса и переложить эту задачу на саму СКД через механизм ролей полей.

Разберем этот процесс по шагам:

Убираем из текста запроса конструкцию СГРУППИРОВАТЬ ПО и функции СУММА для остатков. Запрос должен возвращать детальные записи напрямую из виртуальной таблицы ОстаткиИОбороты.
В конструкторе схемы компоновки данных переходим на вкладку «Наборы данных».
Находим поля остатков и настраиваем для них колонку «Роль»:
- Для поля начального остатка (например, КоличествоНачальныйОстаток) указываем тип: Начальный остаток. В поле «Имя» вписываем общее имя группы (например, Количество), а в поле «Поле периода» выбираем наше расчетное поле периодичности.
- Для поля конечного остатка (например, КоличествоКонечныйОстаток) указываем тип: Конечный остаток. Имя группы указываем то же самое (Количество), а в поле «Поле периода» также привязываем наше поле периодичности.
Для самого поля выбранного периода (например, Периодичность_) в роли указываем тип Период и задаем порядковый номер.

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

Решение 2. Разделение данных на два набора (Остатки и Обороты)

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

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


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

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


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

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

Решение 3. Использование расширения языка запросов СКД для периодичности

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

Посмотрим на пример такого запроса:


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

В данном случае в параметрах виртуальной таблицы мы указали конструкцию {&Периодичность}. На вкладке «Параметры» в СКД появится параметр Периодичность, для которого мы можем задать доступные значения из системного перечисления ШагПериода (например: День, Неделя, Месяц, Год). При формировании отчета СКД сама подставит выбранный пользователем шаг периода напрямую в SQL-запрос на уровне базы данных, что исключит избыточность еще до момента компоновки результата.

Решение 4. Настройка дополнения периодов для «пустых» интервалов

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

Переходим в настройки структуры отчета на вкладку «Настройки».
Выбираем группировку по нашему полю периода.
В свойствах этой группировки находим параметр «Тип дополнения» и устанавливаем его в значение «Период».
В качестве полей «Начало периода» и «Конец периода» указываем соответствующие параметры отчета.

Теперь СКД автоматически создаст строки для всех недостающих периодов, в которых не было активности, и аккуратно «протянет» туда конечные остатки из предыдущих актуальных периодов.