Рассмотрим достаточно распространенную задачу при разработке отчетов в 1С: у нас есть таблица с произвольными периодами (например, интервалы дней 1-2, 3-6, 7-20) и стандартный регистр оборотов. Нам необходимо получить обороты из регистра, сгруппированные именно по этим нетиповым периодам. Проанализируем ситуацию, выясним причину частых ошибок при настройке связей в СКД и разберем по шагам несколько рабочих способов решения.
Часто разработчики пытаются решить задачу "в лоб": создают первый набор данных (с периодами), второй набор данных (запрос к виртуальной таблице регистра оборотов) и пытаются связать их на вкладке «Связи наборов данных», прописывая условие связи вида Периоды.НачалоПериода >= рег.Период. При попытке исполнения СКД выдает ошибку о том, что поле не найдено.
Разберем подробнее, почему так происходит. В Системе компоновки данных (СКД) связи наборов работают не как классическое ЛЕВОЕ СОЕДИНЕНИЕ в SQL-запросе. Второе множество данных здесь выступает как зависимое. Чтобы корректно передать границы периода во второй набор, нам необходимо использовать колонку «Параметр» в настройках связи, а не пытаться прописать это в «Условие связи».
Посмотрим на правильный алгоритм действий:
НачалоПериода).НачПериода).В процессе настройки может возникнуть потребность передать конец периода, сдвинутый на конец дня. Если попытаться использовать функцию языка запросов КонецПериода(&КонПериода, ДЕНЬ) прямо в выражениях СКД, платформа выдаст ошибку.
Важно помнить: на вкладке связи наборов данных функции применяются в синтаксисе языка выражений СКД, а не языка запросов. Правильное написание требует передачи вида периода в виде строки (в кавычках). Рассмотрим правильный синтаксис:
КОНЕЦПЕРИОДА(ПолеДатыОкончания, "ДЕНЬ")
Еще одна неочевидная сложность — появление лишних параметров. Если во втором наборе для виртуальной таблицы оборотов включено «Автозаполнение», СКД попытается "помочь" и автоматически добавит свои скрытые параметры &НачалоПериода и &КонецПериода. В то же время вы прописываете в параметрах виртуальной таблицы свои параметры, переданные через связи: Регистр.Обороты(&НачПериода, &КонПериода). В результате в пользовательских настройках появляются целых 4 параметра дат.
Чтобы решить эту проблему, воспользуемся расширением языка запросов СКД для виртуальных таблиц. Поместим наши параметры в фигурные скобки:
ВЫБРАТЬ
Регистр.Номенклатура,
Регистр.КоличествоОборот
ИЗ
РегистрНакопления.Продажи.Обороты({(&НачПериода)}, {(&КонПериода)}) КАК Регистр
Фигурные скобки укажут компоновщику макета, что эти параметры следует применять только тогда, когда они явно передаются (в нашем случае — для каждой строки первого набора через связь).
Проанализируем производительность такого подхода. Связь двух наборов данных в СКД с передачей параметров работает по принципу запроса в цикле. Это значит, что платформа сначала выполняет первый запрос (получает список периодов), а затем для каждой строки этого результата делает отдельный SQL-запрос к базе данных для второго набора.
Если у вас 10 интервалов, это не критично. Но если периодов сотни, база данных получит сотни запросов к виртуальной таблице. К сожалению, галочка «Список параметров» в связях здесь не поможет, так как виртуальные таблицы регистров 1С (ни Обороты, ни Остатки) не умеют принимать массивы дат в качестве параметров начала и конца периода.
Рассмотрим более оптимальное решение, которое в 1С считается "best practice". Вместо двух наборов данных и сложных связей, мы решим задачу в рамках одного запроса. Для этого мы соединим нашу таблицу периодов не с виртуальной, а с физической таблицей регистра.
Разберем по шагам пример кода:
// Предположим, таблица периодов уже сформирована и помещена в ВТ_Периоды
ВЫБРАТЬ
ВТ_Периоды.НомерПериода,
ВТ_Периоды.НачалоПериода,
ВТ_Периоды.КонецПериода,
Регистр.Номенклатура,
СУММА(Регистр.Количество) КАК КоличествоОборот
ИЗ
ВТ_Периоды КАК ВТ_Периоды
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.Продажи КАК Регистр
ПО Регистр.Период МЕЖДУ ВТ_Периоды.НачалоПериода И ВТ_Периоды.КонецПериода
И Регистр.Активность = ИСТИНА
СГРУППИРОВАТЬ ПО
ВТ_Периоды.НомерПериода,
ВТ_Периоды.НачалоПериода,
ВТ_Периоды.КонецПериода,
Регистр.Номенклатура
В чем преимущество? При таком подходе СУБД выполняет соединение физической таблицы за один проход, проверяя вхождение даты каждой записи регистра в интервал МЕЖДУ. Итоги суммируются стандартной агрегатной функцией. Это работает в десятки раз быстрее и не требует сложной отладки параметров в схеме компоновки.
В некоторых специфических ситуациях, если периодов немного, но они жестко заданы программно (например, колоночный отчет по конкретным временным отрезкам), разработчики применяют динамическую сборку текста запроса с использованием ОБЪЕДИНИТЬ ВСЕ.
ВЫБРАТЬ
&НомерПериода1 КАК НомерПериода,
&НачПериода1 КАК НачПериода,
&КонПериода1 КАК КонПериода,
Т.СуммаОборот КАК Сумма
ИЗ
РегистрНакопления.Продажи.Обороты(&НачПериода1, &КонПериода1) КАК Т
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
&НомерПериода2 КАК НомерПериода,
&НачПериода2 КАК НачПериода,
&КонПериода2 КАК КонПериода,
Т.СуммаОборот КАК Сумма
ИЗ
РегистрНакопления.Продажи.Обороты(&НачПериода2, &КонПериода2) КАК Т
Затем СКД отлично справляется с группировками (и кросс-таблицами), если вывести поле НомерПериода в колонки. Такой метод избавляет от необходимости соединений в принципе, однако требует написания кода на встроенном языке перед выводом отчета и подходит только для ограниченного количества периодов.
Выбор конкретного решения всегда зависит от объема данных. Для небольших таблиц смело используйте связи наборов с передачей параметров — это быстро настраивается визуально. Для тяжелых отчетов проектируйте запрос с соединением физических таблиц.