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

← На главную

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

1. Корректная группировка периодов по колонкам в запросе и СКД

Выясним причину, почему при попытке получить периоды функциями ГОД(Период) или МЕСЯЦ(Период) отчет формирует некорректные колонки и отказывается нормально агрегировать данные. Использование числовых функций приводит к потере типа данных Дата, из-за чего СКД не может автоматически дополнять периоды и выстраивать хронологический порядок.

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


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

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

Для поля ПериодМесяц укажем роль Период и зададим порядковый номер уровня (например, 1).
Для поля ПериодГод укажем роль Период с порядковым номером уровня 2.
Для форматирования отображения колонок в пользовательском интерфейсе зададим форматную строку представления (например, ДФ='ММММ гггг' для месяца).

2. Устранение задвоения и умножения остатков в группировках

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

Рассмотрим основные способы решения этой проблемы:

Способ А. Использование виртуальной таблицы ОстаткиИОбороты

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


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

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

Способ Б. Разделение расчета ресурсов по группировкам

Если остатки и продажи выбираются из разных регистров, настроим выражения на вкладке Ресурсы. Вместо сплошного суммирования укажем, по каким именно группировкам должен рассчитываться остаток:

Для ресурса КоличествоПродаж оставляем выражение Сумма(КоличествоПродаж) для всех группировок.
Для ресурса КоличествоОстаток в колонке Рассчитывать по... выберем только группировку строк Номенклатура, исключив группировку колонок по периодам. В выражении для группировок номенклатуры можно использовать Максимум(КоличествоОстаток) или Сумма(КоличествоОстаток) в зависимости от структуры запроса.

3. Особенности отборов при связывании двух наборов данных

Посмотрим на частую архитектурную проблему: в схеме настроено два набора данных («ВыборкаНоменклатуры» и «Продажи»), связанных по полю Номенклатура. По умолчанию связь работает как левое соединение (LEFT JOIN). Однако при наложении пользователем отбора по полю подчиненного набора (например, по Подразделение) строки с номенклатурой без продаж исчезают из отчета.

Выясним причину: когда накладывается глобальный отбор отчета на поле зависимого набора, платформа оптимизирует результирующий запрос и преобразует связь во внутреннее соединение (INNER JOIN).

Для сохранения позиций номенклатуры без движений применим один из следующих подходов:

Передача отборов через параметры связи наборов: в настройках второго набора данных создадим параметр запроса (например, &ОтборПодразделение). На вкладке Связи наборов данных свяжем этот параметр с доступными полями схемы. В таком случае фильтрация выполнится внутри зависимого запроса, а левое соединение не нарушится.
Локальный отбор в структуре отчета: накладываем фильтр по подразделению не в общих настройках отчета, а непосредственно в настройках конкретной группировки колонок таблицы.

4. Логика условий с проверкой на NULL при фильтрации

Разберем ситуацию, когда условие вида Остаток > 0 ИЛИ Продажи > 0 в отборе отчета отрабатывает как логическое «И» или полностью скрывает позиции. Причиной такого поведения является значение NULL, возникающее в полях подчиненных наборов при отсутствии записей.

В реляционной алгебре и языке выражений 1С любое прямое сравнение с NULL (например, NULL > 0) возвращает Ложь (False). Если у товара есть остаток, но нет продаж, выражение ЕстьNull не было применено, и проверка продаж возвращает NULL, что ломает условие.

Для корректного отбора выполним следующие шаги:

На вкладке Вычисляемые поля добавим поля с явной обработкой неопределенных значений:
```
ЕстьNull(КоличествоОстаток, 0)
ЕстьNull(КоличествоПродаж, 0)
```
Либо наложим отбор не на детальные записи схемы, а на уровне группировки отчета в секции Отбор по итоговым полям-ресурсам. СКД при агрегации ресурсов автоматически заменяет NULL на числовой ноль, благодаря чему условие ИЛИ отрабатывает корректно.