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