При разработке отчетов на Системе компоновки данных (СКД) часто возникает классическая задача: необходимо вывести показатели за определенный выбранный пользователем период (например, начальный остаток, приход, расход и конечный остаток) и одновременно с этим отобразить в соседней колонке данные «за все время» (например, общий расход с начала ведения учета). Естественным решением кажется использование левого соединения основной таблицы за период с дополнительной таблицей (или вложенным запросом/временной таблицей), которая выбирает данные без ограничения по датам.
Однако на практике разработчики сталкиваются с неприятным поведением платформы 1С: в сформированном отчете колонка «за все время» упорно выводит точно такие же данные, как и колонка за период. Происходит это потому, что СКД «под капотом» автоматически транслирует стандартные параметры периода (&НачалоПериода и &КонецПериода) или пользовательские отборы во все виртуальные таблицы запроса, включая те, что находятся в левом соединении. Выясним причину этого поведения и подробно разберем, как его обойти.
Для того чтобы эффективно решить проблему, проанализируем внутренние механизмы СКД. Когда в схеме компоновки данных у набора данных-запроса включена опция Автозаполнение (а она включена по умолчанию в 95% случаев), компоновщик выполняет интеллектуальный анализ текста запроса перед отправкой его на СУБД:
ОстаткиИОбороты) не указаны параметры начала и конца периода, СКД автоматически подставляет туда глобальные параметры схемы — &НачалоПериода и &КонецПериода. Это происходит даже в том случае, если таблица находится внутри пакета запросов во временной таблице или является правой частью левого соединения.Период или системные поля регистра), СКД связывает их логически и накладывает пользовательский фильтр на обе части соединения.Давайте рассмотрим эффективные методы решения этой проблемы, начиная с самого простого и заканчивая профессиональными низкоуровневыми настройками компоновки.
Это наиболее быстрый, изящный и рекомендуемый способ разделения периодов в рамках одного запроса. Суть метода заключается в использовании фигурных скобок {} — специального синтаксиса расширения языка запросов СКД. Этот синтаксис явно указывает компоновщику, как именно нужно управлять параметрами конкретной виртуальной таблицы.
Проанализируем пример запроса, в котором первая временная таблица (ВТ_РасходЗаВсеВремя) выбирает данные за все время, а основной запрос соединяет ее с данными за выбранный период. Чтобы СКД не подставила стандартные параметры в первую таблицу, мы объявим для нее собственные «пустые» параметры внутри фигурных скобок:
ВЫБРАТЬ РАЗРЕШЕННЫЕ
ТоварыНаСкладахОбороты.Номенклатура КАК Номенклатура,
СУММА(ТоварыНаСкладахОбороты.КоличествоРасход) КАК РасходЗаВсеВремя
ПОМЕСТИТЬ ВТ_РасходЗаВсеВремя
ИЗ
РегистрНакопления.ТоварыНаСкладах.Обороты({&ПустаяДатаНачала}, {&ПустаяДатаКонца}, Авто, ) КАК ТоварыНаСкладахОбороты
СГРУППИРОВАТЬ ПО
ТоварыНаСкладахОбороты.Номенклатура
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ РАЗРЕШЕННЫЕ
ТоварыНаСкладахОстаткиИОбороты.Номенклатура КАК Номенклатура,
ТоварыНаСкладахОстаткиИОбороты.КоличествоНачальныйОстаток КАК НачальныйОстаток,
ТоварыНаСкладахОстаткиИОбороты.КоличествоПриход КАК Приход,
ТоварыНаСкладахОстаткиИОбороты.КоличествоРасход КАК РасходЗаПериод,
ТоварыНаСкладахОстаткиИОбороты.КоличествоКонечныйОстаток КАК КонечныйОстаток,
ЕСТЬNULL(ВТ_РасходЗаВсеВремя.РасходЗаВсеВремя, 0) КАК РасходЗаВсеВремя
ИЗ
РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, Авто, , ) КАК ТоварыНаСкладахОстаткиИОбороты
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_РасходЗаВсеВремя КАК ВТ_РасходЗаВсеВремя
ПО ТоварыНаСкладахОстаткиИОбороты.Номенклатура = ВТ_РасходЗаВсеВремя.Номенклатура
Разберем, что мы сделали в этом запросе:
РегистрНакопления.ТоварыНаСкладах.Обороты мы явно прописали параметры в фигурных скобках: {&ПустаяДатаНачала}, {&ПустаяДатаКонца}. Сигнатура фигурных скобок говорит СКД: "Не трогай эти параметры автозаполнением, я управляю ими самостоятельно".ОстаткиИОбороты мы оставили стандартные параметры &НачалоПериода и &КонецПериода.ПустаяДатаНачала и ПустаяДатаКонца:
Дата это будет значение 01.01.0001 0:00:00).Благодаря такой конструкции СКД при генерации итогового запроса подставит в первую таблицу пустые даты (что означает выборку за весь период существования базы данных), а во вторую — даты, выбранные пользователем в интерфейсе отчета.
Если вы не хотите вручную дописывать фигурные скобки в текст запроса, 1С предоставляет визуальный инструмент для решения этой задачи прямо в конструкторе запросов. Выполним настройку по шагам:
ТоварыНаСкладахОбороты).&НачалоВсеВремя и &КонецВсеВремя.Конструктор самостоятельно сформирует нужный синтаксис фигурных скобок в тексте запроса. Вам останется лишь зайти на вкладку Параметры схемы СКД, установить флаг Ограничение доступности для этих параметров и оставить их пустыми.
Иногда даже после правильного разделения параметров виртуальных таблиц СКД продолжает накладывать отборы на левое соединение. Это происходит из-за механизма автозаполнения полей компоновки. Если имя или синоним результирующего поля расхода за все время совпадает с именем поля расхода за период, СКД может применить пользовательский отбор к обоим полям.
Рассмотрим, как гарантированно изолировать поле «за все время» от внешних отборов:
РасходЗаВсеВремя вместо простого Расход.РасходЗаВсеВремя.ГДЕ в итоговом SQL-запросе).Этот шаг исключает ситуации, когда СКД пытается «оптимизировать» итоговый запрос СУБД, превращая левое соединение во внутреннее из-за наложенных условий фильтрации.
Этот способ предназначен для опытных разработчиков и сложных отчетов, где СКД ведет себя непредсказуемо при генерации запроса. Если снять флаг Автозаполнение на вкладке Наборы данных, компоновщик полностью перестанет вмешиваться в структуру запроса. Он не будет автоматически проталкивать параметры периодов и отборы внутрь ваших таблиц.
Однако помните о важных последствиях отключения автозаполнения:
ГДЕ Регистр.Номенклатура {&Номенклатура}.Этот метод дает 100% контроля над запросом, но требует написания большего объема вспомогательного кода компоновки.
Когда мы соединяем таблицу с ограниченным периодом (основная часть отчета) и таблицу за все время (которая может содержать миллионы записей), мы рискуем получить крайне медленный отчет. Проанализируем рекомендации по оптимизации производительности СУБД в таких сценариях:
Номенклатура (и другие поля связи, такие как Характеристика или Склад, если они используются).Применение этих простых правил в сочетании с переопределением параметров виртуальных таблиц в фигурных скобках {} позволит вам создавать сложные, информативные и при этом быстродействующие отчёты на СКД.