При разработке отчетов в 1С программисты часто сталкиваются с задачей модернизации существующих запросов. Типичный сценарий: отчет, который изначально разрабатывался под выбор одного конкретного параметра (например, одного вида цены), требуется перевести на работу со списком значений (например, вывод нескольких видов цен одновременно). На первый взгляд задача кажется простой — достаточно заменить знак равенства на конструкцию В. Однако на практике разработчики сталкиваются с неприятными побочными эффектами: данные начинают дублироваться, в отчете появляются пустые строки, а при попытке добавить группировки СУБД начинает требовать обязательного указания регистраторов или выдает ошибки агрегации.
В этой статье мы подробно разберем природу этих ошибок, выясним, почему "плывут" итоги и двоятся строки, а также рассмотрим три надежных способа решения этой задачи в зависимости от архитектуры вашего отчета.
Давайте проанализируем ситуацию, когда в запросе использовался параметр &ВидЦены (одиночное значение), а затем его заменили на параметр-список &ВидыЦен через конструкцию В (&ВидыЦен). Почему возникают две основные проблемы?
Проблема 1. Появление "пустого вида цены" и некорректное распределение ресурсов.
В исходном запросе, рассчитанном на один вид цены, скорее всего, использовалось ЛЕВОЕ СОЕДИНЕНИЕ таблицы остатков или оборотов (например, по номенклатуре) с таблицей цен. Когда параметр был одиночным, условие связи или фильтрации ГДЕ Цены.ВидЦены = &ВидЦены работало предсказуемо: для каждой номенклатуры подтягивалась ровно одна цена (или не подтягивалась вообще, оставляя значение NULL).
Когда мы передаем список значений и пишем условие ГДЕ Цены.ВидЦены В (&ВидыЦен), на одну строку номенклатуры теперь может приходиться несколько подходящих записей из таблицы цен (например, Оптовая, Розничная и Дистрибьюторская). В результате левого соединения происходит так называемое декартово произведение (размножение строк): количество строк номенклатуры с остатками умножается на количество выбранных видов цен. Если у товара вообще нет цен из списка, поле цены принимает значение NULL, и в отчете формируется отдельная строка с пустым видом цены, куда и "схлопываются" все реальные количественные остатки. Для товаров с ценами остатки начинают дублироваться по каждой строке цены, искажая общие итоги отчета.
Проблема 2. Ошибка "не хватает регистраторов" при группировке.
Пытаясь исправить дублирование строк, разработчики часто пытаются применить секцию СГРУППИРОВАТЬ ПО. Однако, если в детальных записях отчета требуется выводить документ движения (поле Регистратор), мы попадаем в замкнутый круг. Если добавить Регистратор в секцию группировки, строки продолжат дублироваться (так как у каждого регистратора свои движения). Если убрать Регистратор из группировки, СУБД выдаст синтаксическую ошибку, так как это поле не участвует в агрегатных функциях. Сгруппировать детальные данные по регистраторам и динамическим ценам одновременно в простом плоском запросе без искажения итогов невозможно.
---
Если ваш отчет построен на СКД, то пытаться вручную разворачивать колонки с ценами в запросе — это грубейшая архитектурная ошибка. СКД создана для того, чтобы решать подобные задачи декларативно. Рассмотрим, как правильно настроить отчет в компоновке данных шаг за шагом.
НаборДанныхОстатки и НаборДанныхЦены. На вкладке "Связи наборов данных" свяжите их по полю Номенклатура, передавая параметр ВидЦены в качестве связующего элемента.Номенклатура, Регистратор (при необходимости детальных записей).ВидЦены.Максимум(Цена).При такой архитектуре СКД самостоятельно выполнит эффективные запросы к базе данных, транспонирует (развернет) выбранные пользователем виды цен в отдельные колонки по горизонтали и корректно сопоставит их с регистраторами без какого-либо дублирования строк и пустых значений.
---
Если вам по какой-то причине необходимо решить задачу в рамках одного пакета запросов (например, для выгрузки в Таблицу Значений), то передача списка значений напрямую в параметры виртуальных таблиц может негативно сказаться на производительности. Проанализируем, как правильно оптимизировать этот процесс с помощью временных таблиц.
Вместо передачи списка значений напрямую в параметры условий, мы сначала помещаем его во временную таблицу. Рассмотри пример кода подготовки запроса на встроенном языке:
// Выгружаем значения из Списка Значений в массив для передачи в параметры
МассивВидовЦен = СписокВидовЦен.ВыгрузитьЗначения();
Запрос.УстановитьПараметр("МассивВидовЦен", МассивВидовЦен);
В самом тексте запроса мы сначала выбираем данные из этого массива и создаем индексированную временную таблицу. Это позволит СУБД построить оптимальный план запроса:
ВЫБРАТЬ
Т.Значение КАК ВидЦены
ПОМЕСТИТЬ ВТ_ВыбранныеВидыЦен
ИЗ
&МассивВидовЦен КАК Т
ИНДЕКСИРОВАТЬ ПО
ВидЦены
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ЦеныСрезПоследних.Номенклатура КАК Номенклатура,
ЦеныСрезПоследних.ВидЦены КАК ВидЦены,
ЦеныСрезПоследних.Цена КАК Цена
ПОМЕСТИТЬ ВТ_ЦеныНоменклатуры
ИЗ
РегистрСведений.ЦеныНоменклатуры.СрезПоследних(, ВидЦены В (ВЫБРАТЬ ВТ_ВыбранныеВидыЦен.ВидЦены ИЗ ВТ_ВыбранныеВидыЦен)) КАК ЦеныСрезПоследних
;
Использование временной таблицы ВТ_ВыбранныеВидыЦен и внутреннего соединения (или фильтра в параметрах виртуальной таблицы) гарантирует, что в выборку попадут только те виды цен, которые реально отметил пользователь, что предотвратит появление пустых значений NULL.
---
Если отчет выводится в обычный табличный документ вручную, а колонки цен должны идти строго слева направо (каждый выбранный вид цены — в своей колонке), статический запрос написать не получится, так как количество выбранных пользователем элементов в списке заранее неизвестно.
В этом случае нам поможет динамическое формирование текста запроса в коде модуля. Давайте разберем этот алгоритм по шагам. Мы будем использовать агрегатную функцию МАКСИМУМ в сочетании с условной конструкцией ВЫБОР, чтобы "схлопнуть" цены в одну строку по каждой номенклатуре и регистратору.
ТекстВыбораЦен = "";
Индекс = 0;
Для Каждого ЭлементСписка Из СписокВидовЦен Цикл
// Проверяем, что элемент выбран пользователем
Если ЭлементСписка.Пометка Тогда
ИмяПараметра = "ВидЦены_" + Индекс;
// Формируем динамическое поле для каждого вида цены
ТекстВыбораЦен = ТекстВыбораЦен +
", МАКСИМУМ(ВЫБОР КОГДА ВТ_Цены.ВидЦены = &" + ИмяПараметра + " ТОГДА ВТ_Цены.Цена ИНАЧЕ 0 КОНЕЦ) КАК Цена_" + Индекс;
// Устанавливаем уникальный параметр для каждого вида цены
Запрос.УстановитьПараметр(ИмяПараметра, ЭлементСписка.Значение);
Индекс = Индекс + 1;
КонецЕсли;
КонецЦикла;
// Собираем итоговый текст запроса
ТекстЗапроса =
"ВЫБРАТЬ
| ВТ_Данные.Номенклатура КАК Номенклатура,
| ВТ_Данные.Регистратор КАК Регистратор,
| ВТ_Данные.КоличествоОборот КАК Количество"
+ ТекстВыбораЦен +
"
|ИЗ
| ВТ_Данные КАК ВТ_Данные
| ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Цены КАК ВТ_Цены
| ПО ВТ_Данные.Номенклатура = ВТ_Цены.Номенклатура
|ГРУППИРОВАТЬ ПО
| ВТ_Данные.Номенклатура,
| ВТ_Данные.Регистратор,
| ВТ_Данные.КоличествоОборот";
Запрос.Текст = ТекстЗапроса;
Давайте проанализируем, почему этот подход решает все проблемы одновременно:
Номенклатуре и Регистратору, каждая уникальная пара "Товар - Документ движения" гарантированно выведется только один раз.ВЫБОР КОГДА... ТОГДА... ИНАЧЕ 0 КОНЕЦ и агрегатной функции МАКСИМУМ, цены по разным видам распределяются в свои именованные колонки (Цена_0, Цена_1 и т.д.) внутри одной строки.Регистратор находится в секции группировки основного запроса, что исключает ошибки синтаксиса СУБД.Выбирайте наиболее подходящий под ваши задачи вариант реализации и проектируйте ваши запросы правильно!