При разработке отчетов на платформе 1С:Предприятие 8 программисты часто сталкиваются с весьма специфическим поведением Системы компоновки данных (СКД). Одна из самых распространенных и ставящих в тупик проблем — это ситуация, когда абсолютно корректный, простой запрос к регистру накопления (например, по учету НДФЛ) прекрасно работает в обычной консоли запросов и возвращает данные, но при запуске в виде отчета СКД выдает пустой результат.
В этой статье мы подробно разберем, почему так происходит, изучим внутренние механизмы работы СКД с виртуальными и физическими таблицами регистров накопления, а также рассмотрим несколько надежных практических способов решения этой проблемы.
Давайте проанализируем типичную ситуацию. Нам необходимо выбрать данные из регистра накопления РасчетыНалогоплательщиковСБюджетомПоНДФЛ (или аналогичного ему регистра РасчетыНалоговыхАгентовСБюджетомПоНДФЛ) с отбором по измерению МесяцНалоговогоПериода за определенный интервал времени. Это измерение хранит дату, указывающую на месяц, к которому относится начисленный или удержанный налог.
В консоли запросов мы пишем следующий простейший запрос к виртуальной таблице оборотов:
ВЫБРАТЬ
РасчетыНалогоплательщиковОбороты.МесяцНалоговогоПериода КАК МесяцНалоговогоПериода,
РасчетыНалогоплательщиковОбороты.СуммаПриход КАК СуммаПриход
ИЗ
РегистрНакопления.РасчетыНалогоплательщиковСБюджетомПоНДФЛ.Обороты(, , , МесяцНалоговогоПериода МЕЖДУ &НачПериод И &КонПериод) КАК РасчетыНалогоплательщиковОбороты
Этот запрос отрабатывает мгновенно и выводит нужные строки с суммами. Однако, как только мы переносим этот же текст запроса в схему компоновки данных (СКД) и пытаемся сформировать отчет, система выводит пустую таблицу. При этом мы точно знаем, что параметры &НачПериод и &КонПериод заполнены корректно, а в самой базе данных есть записи за этот период.
Более того, даже если мы перепишем запрос к физической таблице регистра, результат в СКД все равно может остаться пустым:
ВЫБРАТЬ
РегистрНДФЛ.МесяцНалоговогоПериода КАК МесяцНалоговогоПериода,
РегистрНДФЛ.Сумма КАК Сумма
ИЗ
РегистрНакопления.РасчетыНалоговыхАгентовСБюджетомПоНДФЛ КАК РегистрНДФЛ
ГДЕ
РегистрНДФЛ.МесяцНалоговогоПериода МЕЖДУ &Нач И &Кон
Выясним причину такого странного поведения платформы.
Главная причина расхождения результатов в консоли запросов и в СКД заключается в механизме автозаполнения (флаг "Автозаполнение" на вкладке "Наборы данных" в СКД). СКД — это не просто инструмент для выполнения запросов, это интеллектуальный генератор отчетов. На основе метаданных конфигурации СКД пытается "оптимизировать" наш запрос перед его отправкой на СУБД.
Давайте посмотрим, что именно происходит за кулисами процесса генерации отчета:
.Обороты, она автоматически добавляет скрытые параметры виртуальной таблицы: &НачалоПериода и &КонецПериода. Если эти параметры не выведены на форму отчета и не заполнены пользователем напрямую, СКД передает в них пустые значения (дату 01.01.0001 00:00:00). В итоге виртуальная таблица пытается рассчитать обороты внутри пустого интервала времени, что приводит к нулевому результату еще до того, как применится ваше условие по измерению МесяцНалоговогоПериода.Период в регистре имеет предопределенную роль "Период". СКД автоматически связывает глобальные настройки периода отчета с этим полем. Если период отчета не задан или равен пустой дате, СКД модифицирует SQL-запрос, добавляя жесткое скрытое условие вида: ГДЕ Период МЕЖДУ ДАТАВРЕМЯ(1,1,1) И ДАТАВРЕМЯ(1,1,1). Естественно, такой запрос не вернет ни одной строки.Период — это дата фактической записи в регистр (например, дата выплаты зарплаты в январе). Поле МесяцНалоговогоПериода — это месяц, за который начислен доход (например, декабрь предыдущего года). Если СКД неявно накладывает ограничение по текущему периоду на поле Период, а вы пытаетесь выбрать данные по измерению МесяцНалоговогоПериода, эти временные отрезки могут не пересечься в физической таблице базы данных.Рассмотрим детально четыре проверенных способа решения этой проблемы, которые позволят получить корректный результат в СКД.
Самый простой и надежный способ вернуть полный контроль над логикой вашего запроса — это отключение автоматического вмешательства СКД.
Выполним следующие шаги:
НаборДанныхЗапрос) и снимите галочку в колонке "Автозаполнение".При отключенном автозаполнении СКД перестанет автоматически модифицировать ваш запрос и накладывать скрытые фильтры по стандартным датам. Запрос уйдет на СУБД ровно в том виде, в каком вы его написали, и фильтрация по измерению МесяцНалоговогоПериода сработает корректно.
Если вам все же необходимо сохранить механизм автозаполнения для других полей отчета, вы можете явно указать СКД правила наложения ограничений с помощью специальных фигурных скобок {}. Это синтаксис расширения языка запросов СКД.
Если вы используете физическую таблицу регистра, перепишите секцию ГДЕ следующим образом:
ВЫБРАТЬ
РегистрНДФЛ.МесяцНалоговогоПериода КАК МесяцНалоговогоПериода,
РегистрНДФЛ.Сумма КАК Сумма
ИЗ
РегистрНакопления.РасчетыНалоговыхАгентовСБюджетомПоНДФЛ КАК РегистрНДФЛ
{ГДЕ
(РегистрНДФЛ.МесяцНалоговогоПериода МЕЖДУ &Нач И &Кон)}
Если используется виртуальная таблица оборотов, то параметры стандартного периода в параметрах виртуальной таблицы можно сделать необязательными для СКД:
ВЫБРАТЬ
Обороты.МесяцНалоговогоПериода КАК МесяцНалоговогоПериода,
Обороты.СуммаПриход КАК СуммаПриход
ИЗ
РегистрНакопления.РасчетыНалогоплательщиковСБюджетомПоНДФЛ.Обороты({&НачалоПериода}, {&КонецПериода}, , {МесяцНалоговогоПериода МЕЖДУ &НачПериод И &КонПериод}) КАК Обороты
Использование фигурных скобок указывает СКД, что данные условия являются необязательными и должны применяться динамически только тогда, когда соответствующие параметры или отборы установлены пользователем в настройках отчета.
Если СКД принудительно накладывает отборы по скрытым параметрам &НачалоПериода и &КонецПериода на вашу виртуальную таблицу оборотов, мы можем программно или декларативно "обмануть" систему, передав туда максимально широкий интервал дат.
Выполним настройку на вкладке "Параметры":
НачалоПериода и КонецПериода.НачалоПериода напишите выражение: ДАТАВРЕМЯ(1,1,1).КонецПериода напишите выражение: ДАТАВРЕМЯ(3999,12,31).&Нач и &Кон) и используйте их в тексте запроса для фильтрации измерения МесяцНалоговогоПериода.Благодаря этому виртуальная таблица оборотов будет всегда рассчитываться за всю историю ведения учета (без ограничения по физическому периоду регистрации), а реальная фильтрация данных в отчете будет происходить исключительно по вашим пользовательским параметрам, наложенным на измерение налогового периода.
Если проблема возникает при обращении к физической таблице и вы хотите сохранить автозаполнение, можно скорректировать роли полей, чтобы СКД перестала ассоциировать системные периоды с полями вашего регистра.
Для этого выполните следующие действия:
Период.МесяцНалоговогоПериода не установлены конфликтующие роли, мешающие наложению условий.После удаления роли "Период" система компоновки данных перестанет неявно связывать это поле со стандартными настройками интервала отчета и добавлять скрытые условия фильтрации в итоговый SQL-запрос.
Для того чтобы в будущем быстро локализовать подобные проблемы, настоятельно рекомендуем использовать специализированный инструмент — Консоль компоновки данных (не путать с обычной консолью запросов).
Обычная консоль запросов выполняет исходный текст вашего запроса "как есть". Консоль компоновки данных позволяет увидеть промежуточный и конечный варианты запроса, которые генерирует сама СКД с учетом всех пользовательских настроек, отборов и автозаполнения. Посмотрев на реальный SQL-запрос, отправляемый в СУБД, вы сразу увидите лишние условия вида Период МЕЖДУ... или пустые параметры виртуальных таблиц, что позволит моментально принять решение о применении одного из четырех описанных выше способов.