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