При разработке отчетов на Системе компоновки данных (СКД) в 1С программисты часто сталкиваются со следующей задачей: необходимо вывести в одной строке (например, в разрезе контрагентов) показатели оборотов за два совершенно разных, не связанных между собой периода. Это может быть сравнение текущего месяца с аналогичным месяцем прошлого года, или просто анализ двух произвольных интервалов, таких как сентябрь и октябрь.
Казалось бы, решение очевидно — использовать набор данных-объединение (Union) или написать один запрос с конструкцией ОБЪЕДИНИТЬ ВСЕ, где в каждой части указать свои параметры периода для виртуальной таблицы оборотов. Однако при попытке реализовать это стандартным способом разработчики обнаруживают странное поведение отчета: СКД упорно выводит данные только по одному (обычно первому) периоду в обе колонки, полностью игнорируя настройки для второго периода. В этой статье мы подробно разберем, почему так происходит, и рассмотрим несколько эффективных способов решения этой проблемы.
Чтобы понять природу этого поведения, нам нужно проанализировать внутренние механизмы работы СКД. По умолчанию в системе включена функция «Автозаполнение» для наборов данных типа «Запрос». Когда СКД анализирует текст запроса и видит обращение к виртуальной таблице (например, РегистрБухгалтерии.Хозрасчетный.Обороты), она автоматически определяет параметры этой таблицы и связывает их с глобальными параметрами схемы компоновки данных — &НачалоПериода и &КонецПериода.
Если мы объединяем два запроса к одной и той же виртуальной таблице в рамках одного набора данных или используем набор данных «Объединение», СКД пытается оптимизировать результирующий запрос к СУБД. Она видит одинаковые виртуальные таблицы и автоматически подменяет их локальные параметры общими глобальными значениями. В итоге оба подзапроса начинают выполняться за один и тот же период, заданный в общих параметрах схемы. Именно поэтому данные во второй колонке либо дублируют первую, либо вовсе не выводятся.
Давайте разберем пошагово, как обойти это ограничение и заставить СКД корректно работать с разными периодами.
Самый простой и быстрый способ решить проблему «схлопывания» параметров — явно указать СКД, что параметры виртуальных таблиц в каждом подзапросе являются независимыми (локальными). Для этого используются фигурные скобки {} — специальное расширение языка запросов СКД.
Когда мы помещаем параметры виртуальной таблицы в фигурные скобки, мы отключаем для них механизм автоматического связывания. СКД больше не пытается оптимизировать их и привести к единому знаменателю. Мы получаем возможность управлять параметрами каждого подзапроса отдельно.
Рассмотрим пример запроса, в котором мы объединяем данные за два разных периода (например, период основных продаж и период дополнительных оплат):
ВЫБРАТЬ
ХозрасчетныйОбороты1.Субконто1 КАК Контрагент,
ХозрасчетныйОбороты1.СуммаОборотДт КАК ОплатаЗаПериод1,
0 КАК ОплатаЗаПериод2
ИЗ
РегистрБухгалтерии.Хозрасчетный.Обороты({(&НачалоПериода1)}, {(&КонецПериода1)}, , , , , , ) КАК ХозрасчетныйОбороты1
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ХозрасчетныйОбороты2.Субконто1 КАК Контрагент,
0 КАК ОплатаЗаПериод1,
ХозрасчетныйОбороты2.СуммаОборотДт КАК ОплатаЗаПериод2
ИЗ
РегистрБухгалтерии.Хозрасчетный.Обороты({(&НачалоПериода2)}, {(&КонецПериода2)}, , , , , , ) КАК ХозрасчетныйОбороты2
Обратите внимание на синтаксис: параметры {(&НачалоПериода1)} и {(&КонецПериода1)} в первом запросе, а также {(&НачалоПериода2)} и {(&КонецПериода2)} во втором запросе заключены в фигурные скобки. Это указывает компоновщику данных, что данные параметры должны обрабатываться как индивидуальные элементы настройки.
Если вы не хотите писать фигурные скобки вручную, вы можете воспользоваться встроенным конструктором запросов 1С:
Если структура вашего отчета достаточно сложная (так называемый «монстр», к которому нужно добавить еще одну колонку за произвольный период), использование объединения в одном запросе может сильно усложнить чтение и отладку кода. В этом случае отличной архитектурной альтернативой является использование механизма Связей наборов данных в СКД.
Вместо того чтобы объединять запросы на уровне СУБД, мы можем создать два независимых набора данных и связать их на уровне СКД. Разберем этот процесс подробно:
НаборДанныхОсновной). В нем пишем обычный запрос для получения основных данных за первый период. Параметры виртуальной таблицы задаем стандартно: &НачалоПериода1 и &КонецПериода1.НаборДанныхДополнительный). В нем пишем запрос к тому же регистру, но уже для получения данных за второй период. Параметры виртуальной таблицы называем иначе, например: &НачалоПериода2 и &КонецПериода2.НаборДанныхОсновнойНаборДанныхДополнительныйКонтрагент = Контрагент и Договор = Договор.При таком подходе СКД выполнит два независимых запроса к базе данных с правильными (разными) параметрами периодов, а затем соединит полученные результаты в памяти по указанным ключевым полям. Это избавляет нас от необходимости использовать фигурные скобки и существенно упрощает структуру каждого отдельного запроса.
Если периоды, за которые нужно получить обороты, идут последовательно друг за другом (например, нам нужны колонки по месяцам: сентябрь, октябрь, ноябрь), то дублировать запросы или использовать связи наборов данных вообще не требуется. Платформа 1С изначально спроектирована так, чтобы решать подобные задачи силами настроек вывода отчета.
Давайте проанализируем, как реализовать это наиболее производительным способом:
Месяц (или Авто).ПериодМесяц.Контрагент.ПериодМесяц.СуммаОборот.СКД автоматически выполнит один оптимальный запрос к базе данных, а затем «развернет» строки в кросс-таблицу, где колонками будут выступать месяцы. Чтобы даты в заголовках колонок выглядели презентабельно, в свойствах набора данных для поля ПериодМесяц можно задать формат редактирования, например, ДФ='ММММ гггг' (тогда вместо «01.09.2019» пользователь увидит «Сентябрь 2019»). Для наглядного примера настройки колонок по периодам можно использовать пример формирования шапки отчета СКД по неделям и месяцам.
Что делать, если периоды абсолютно произвольные (например, один период — это «с 10 по 15 сентября», а второй — «с 20 по 25 октября»), их нельзя объединить в красивую последовательность, но при этом хочется получить максимальную производительность без множественных запросов к базе данных?
В этом случае мы можем применить метод условного расчета показателей внутри одного запроса. Мы выбираем данные за один «общий» период, который покрывает оба наших интервала (от самой ранней даты начала до самой поздней даты окончания), а затем разделяем суммы по колонкам с помощью условного выбора.
Посмотрим на пример такого запроса:
ВЫБРАТЬ
ХозрасчетныйОбороты.Субконто1 КАК Контрагент,
ВЫБОР
КОГДА ХозрасчетныйОбороты.Период МЕЖДУ &НачалоПериода1 И &КонецПериода1
ТОГДА ХозрасчетныйОбороты.СуммаОборотДт
ИНАЧЕ 0
КОНЕЦ КАК ОплатаЗаПериод1,
ВЫБОР
КОГДА ХозрасчетныйОбороты.Период МЕЖДУ &НачалоПериода2 И &КонецПериода2
ТОГДА ХозрасчетныйОбороты.СуммаОборотДт
ИНАЧЕ 0
КОНЕЦ КАК ОплатаЗаПериод2
ИЗ
РегистрБухгалтерии.Хозрасчетный.Обороты(&ОбщееНачало, &ОбщийКонец, День, , , , , ) КАК ХозрасчетныйОбороты
Важное примечание: Чтобы этот метод работал корректно, в параметре «Периодичность» виртуальной таблицы обязательно нужно указать детализацию (например, День), чтобы поле Период было доступно в выборке для анализа условий. Если оставить периодичность по умолчанию (Период), то СКД не сможет разложить обороты по дням внутри виртуальной таблицы, и условие МЕЖДУ отработает некорректно.
Этот метод чрезвычайно производителен, поскольку СУБД делает всего один проход по индексам таблицы оборотов за общий интервал, вместо выполнения нескольких тяжелых запросов и их последующего соединения.
Если мы оставляем в отчете четыре отдельных параметра (две даты начала и две даты окончания), пользователю будет крайне неудобно работать с таким интерфейсом. Ему придется вручную выбирать четыре даты в календаре. Чтобы сделать работу с отчетом комфортной, мы можем настроить связь параметров с удобным стандартным типом данных 1С — СтандартныйПериод.
Разберем по шагам, как настроить такую связку:
ПериодОтчета1 и ПериодОтчета2. Задайте им тип данных СтандартныйПериод. Именно эти параметры мы сделаем доступными для пользователя (включим флаг «Быстрый доступ»).НачалоПериода1, КонецПериода1, НачалоПериода2, КонецПериода2), которые используются в запросе, установите флаг «Ограничение доступности». Это скроет их от пользователя, чтобы не загромождать интерфейс.НачалоПериода1: &ПериодОтчета1.ДатаНачалаКонецПериода1: КонецПериода(&ПериодОтчета1.ДатаОкончания, "День")НачалоПериода2: &ПериодОтчета2.ДатаНачалаКонецПериода2: КонецПериода(&ПериодОтчета2.ДатаОкончания, "День")Использование функции КонецПериода с параметром "День" крайне важно! По умолчанию тип СтандартныйПериод при выборе даты окончания (например, 31 октября) устанавливает время на начало дня (00:00:00). Если не использовать функцию КонецПериода, то все обороты, совершенные в течение последнего дня периода (31 октября), не попадут в отчет. Функция КонецПериода гарантирует, что дата окончания будет установлена на последнюю секунду дня (23:59:59).
Благодаря такой настройке пользователь будет видеть на форме отчета всего два привычных поля выбора периода с удобным выпадающим календарем и возможностью выбора стандартных интервалов (например, «Прошлый месяц», «Текущий квартал»), а СКД сама корректно разложит эти периоды на граничные даты для выполнения запросов. Для расширения возможностей пользовательского интерфейса отчета можно применить решение для стыковки элементов управляемой формы и пользовательских настроек СКД.
Мы подробно разобрали проблему получения оборотов по двум разным периодам из одного регистра в СКД и изучили четыре различных метода ее решения. Выбор конкретного подхода зависит от архитектурных особенностей вашего отчета: