Почему в отчете СКД пропадают пустые месяцы при отборе по менеджеру и как это исправить

← На главную

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

Разбор причины: механизм трансформации соединений

Рассмотрим ситуацию на примере запроса, где используется ПОЛНОЕ СОЕДИНЕНИЕ регистра Календарь с регистрами накопления ВыручкаИСебестоимостьПродаж и ЗаказыКлиентов. Когда в СКД накладывается стандартный отбор, система добавляет условие в секцию ГДЕ итогового SQL-запроса.

Проблема заключается в логике работы SQL: любое условие фильтрации в секции ГДЕ, наложенное на поле из правой (или соединяемой) таблицы, фактически превращает ЛЕВОЕ или ПОЛНОЕ СОЕДИНЕНИЕ во ВНУТРЕННЕЕ. Если в конкретном месяце не было продаж по выбранному менеджеру, поле менеджера примет значение NULL. Условие ОсновнойМенеджер = &Параметр для значения NULL вернет результат «Ложь», и вся строка, включая дату из календаря, будет отсечена из итоговой выборки.

Решение №1: Объединение наборов данных (самый надежный способ)

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

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

Создадим Набор данных №1 (Скелет): Этот запрос будет выбирать только уникальные даты или периоды из регистра Календарь. Здесь не должно быть никаких ссылок на продажи или менеджеров.
Создадим Набор данных №2 (Данные): Этот запрос выбирает выручку, заказы и аналитику (менеджеров). Именно здесь мы разрешим накладывать отборы.
Настроим связи: В конструкторе схемы на закладке «Связи наборов данных» свяжем Набор №1 с Набором №2 по полю Период (или Месяц). Набор №1 должен быть ведущим.

При такой структуре, когда пользователь установит отбор по менеджеру, он применится только к Набору №2. В Наборе №1 (календаре) останутся все даты, и отчет выведет пустые ячейки там, где данные второго набора не подошли под фильтр.

Решение №2: Использование необязательных условий в тексте запроса

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

Посмотрим на пример правильного оформления соединения:


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

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

Решение №3: Виртуальные таблицы с параметрами

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

Проанализируем пример использования параметров виртуальной таблицы:


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

В данном случае фильтрация происходит до соединения. Система сначала выберет обороты только нужного менеджера, а затем присоединит их к календарю. Те месяцы, в которых у менеджера не было оборотов, просто получат NULL, но сами даты из результата не исчезнут.

Общие рекомендации по работе с календарными отчетами

При построении подобных отчетов всегда придерживайтесь следующих правил:

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

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

← На главную