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