Как в СКД вывести последний документ движения по остаткам и решить проблему с периодом?

← На главную

При разработке отчетов в Системе компоновки данных (СКД) программисты часто сталкиваются с задачей: вывести актуальные остатки по взаиморасчетам (например, по счетам 60 и 62) и одновременно показать последний документ, который сделал движение по этим остаткам (документ-регистратор). На первый взгляд задача кажется простой, но при ее реализации в СКД возникает неприятный нюанс: отчет выводит регистратор только в том случае, если документ был проведен строго в выбранном периоде отчета (или на дату конца периода). Если же движение было раньше, поле регистратора остается пустым, хотя в консоли запросов всё выбирается корректно. Рассмотрим подробнее, почему так происходит, выясним причину и разберем правильные архитектурные решения этой проблемы.

В чем причина «исчезновения» документов-регистраторов?

Главным виновником такого поведения отчета является встроенный механизм СКД — Автозаполнение. Когда этот флаг включен в наборе данных-запросе, система пытается облегчить жизнь разработчику и автоматически оптимизировать запросы к виртуальным таблицам. Проанализируем ситуацию по шагам:

Вы используете виртуальную таблицу РегистрБухгалтерии.Хозрасчетный.ОстаткиИОбороты (или аналогичную таблицу накопления) для поиска последнего документа движения.
В параметрах этой таблицы вы намеренно оставляете поля «Начало периода» и «Конец периода» пустыми, рассчитывая, что запрос будет искать документы «с начала времен» до текущего момента.
При формировании отчета СКД видит пустые параметры в виртуальной таблице и автоматически (неявно) подставляет в них глобальные параметры отчета — &НачалоПериода и &КонецПериода.
В итоге ваш запрос по поиску документов жестко ограничивается рамками выбранного пользователем периода. Если в этот период движений по контрагенту не было (а был лишь готовый остаток, пришедший из прошлых периодов), то СКД просто не находит ни одной записи регистратора и выводит пустое поле.

Для того чтобы обойти это ограничение и заставить СКД не применять глобальные параметры к конкретной виртуальной таблице, необходимо использовать специальные конструкции языка запросов СКД.

Решение проблемы с помощью фигурных скобок (необязательные параметры)

Самый быстрый способ победить «самодеятельность» СКД — это использование фигурных скобок {} в параметрах виртуальной таблицы. Фигурные скобки указывают компоновщику данных, что данные параметры являются необязательными и управлять ими нужно отдельно. СКД перестанет принудительно подставлять туда стандартные даты отчета.

Разберем готовый рабочий запрос, к которому пришел автор темы в процессе обсуждения. В данном примере для поиска последнего движения используются отдельные параметры {&Нач} и {&Кон}, которые не пересекаются с основными параметрами получения остатков:


ВЫБРАТЬ
    ХозрасчетныйОстаткиИОбороты.Субконто1 КАК Субконто1,
    ХозрасчетныйОстаткиИОбороты.Субконто2 КАК Субконто2,
    ХозрасчетныйОстаткиИОбороты.Счет КАК Счет,
    ХозрасчетныйОстаткиИОбороты.Организация КАК Организация,
    ХозрасчетныйОстаткиИОбороты.СуммаКонечныйОстатокКт КАК Сумма
ПОМЕСТИТЬ Данные
ИЗ
    РегистрБухгалтерии.Хозрасчетный.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, , , Счет В ИЕРАРХИИ (&СчетАВ), , Организация = &Организация) КАК ХозрасчетныйОстаткиИОбороты
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВложенныйЗапрос.Субконто1 КАК Субконто1,
    МАКСИМУМ(ВложенныйЗапрос.Период) КАК Период,
    ХозрасчетныйОстаткиИОбороты.Регистратор КАК Регистратор,
    ВложенныйЗапрос.Субконто2
ПОМЕСТИТЬ Изм
ИЗ
    (ВЫБРАТЬ
        МАКСИМУМ(ХозрасчетныйОстаткиИОбороты.Период) КАК Период,
        ХозрасчетныйОстаткиИОбороты.Субконто1 КАК Субконто1,
        ХозрасчетныйОстаткиИОбороты.Субконто2 КАК Субконто2
    ИЗ
        РегистрБухгалтерии.Хозрасчетный.ОстаткиИОбороты({(&Нач)}, {(&Кон)}, Запись, , Счет В ИЕРАРХИИ (&СчетАВ), , Организация = &Организация) КАК ХозрасчетныйОстаткиИОбороты
    
    СГРУППИРОВАТЬ ПО
        ХозрасчетныйОстаткиИОбороты.Субконто1,
        ХозрасчетныйОстаткиИОбороты.Субконто2) КАК ВложенныйЗапрос
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрБухгалтерии.Хозрасчетный.ОстаткиИОбороты({(&Нач)}, {(&Кон)}, Запись, , Счет В ИЕРАРХИИ (&СчетАВ), , Организация = &Организация) КАК ХозрасчетныйОстаткиИОбороты
        ПО ВложенныйЗапрос.Субконто1 = ХозрасчетныйОстаткиИОбороты.Субконто1
            И ВложенныйЗапрос.Период = ХозрасчетныйОстаткиИОбороты.Period
            И ВложенныйЗапрос.Субконто2 = ХозрасчетныйОстаткиИОбороты.Субконто2

СГРУППИРОВАТЬ ПО
    ВложенныйЗапрос.Субконто1,
    ВложенныйЗапрос.Субконто2,
    ХозрасчетныйОстаткиИОбороты.Регистратор
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    Данные.Организация,
    Данные.Счет,
    Данные.Субконто1,
    Данные.Субконто2,
    Данные.Сумма,
    Изм.Период,
    Изм.Регистратор
ПОМЕСТИТЬ я
ИЗ
    Данные КАК Данные
        ЛЕВОЕ СОЕДИНЕНИЕ Изм КАК Изм
        ПО Данные.Субконто1 = Изм.Субконто1
            И Данные.Субконто2 = Изм.Субконто2
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    "1. Авансы полученные" КАК ВидДебиторскойЗадолженности,
    я.Период,
    я.Организация,
    я.Счет,
    я.Регистратор,
    я.Субконто1,
    я.Субконто2,
    я.Сумма КАК Сумма
ИЗ
    я КАК я

Обратите внимание на конструкцию {(&Нач)}, {(&Кон)}. Благодаря ей СКД не подменяет эти параметры стандартным периодом отчета, что позволяет коду корректно находить последнее движение по регистратору за рамками дат основного отчета.

Опасность использования таблицы ОстаткиИОбороты с периодичностью Запись

Несмотря на то что приведенное выше решение является рабочим, оно содержит серьезную архитектурную проблему производительности. Рассмотрим, почему этот подход крайне не рекомендуется использовать на крупных базах данных реальных предприятий.

Использование виртуальной таблицы ОстаткиИОбороты с периодичностью Запись (или Регистратор) заставляет СУБД выполнять сложнейшие математические расчеты «на лету», соединяя физические таблицы движений с таблицами итогов. Когда мы запускаем такой запрос «с начала времен» (без указания даты начала), СУБД вынуждена выполнить полное сканирование базы данных (Full Scan). На реальной рабочей базе с миллионами проводок это гарантированно приведет к следующим последствиям:

Колоссальная нагрузка на процессор и дисковую подсистему сервера СУБД.
Временная блокировка таблиц и замедление работы всех пользователей.
Выпадение отчета по ошибке превышения времени ожидания (Time out).

Как сделать правильно и оптимально?

Вместо тяжелой таблицы ОстаткиИОбороты для поиска последнего документа гораздо эффективнее обращаться напрямую к физической таблице движений (например, РегистрБухгалтерии.Хозрасчетный или таблице субконто). Схема оптимального алгоритма выглядит так:

Быстро получаем только актуальные остатки на &КонецПериода, используя легкую виртуальную таблицу Остатки (без оборотов).
Для полученных аналитик (Контрагент, Договор) левым соединением обращаемся к физической таблице движений регистра, чтобы найти МАКСИМУМ(Период). Поиск по физической таблице с использованием индексов по субконто происходит в разы быстрее.
Зная максимальную дату движения для каждой аналитики, мы точечно соединяем ее с таблицей движений и получаем конкретный Регистратор.

Важная деталь: Ошибка поиска через МАКСИМУМ(Регистратор)

В процессе обсуждения на форумах часто проскакивает совет использовать агрегатную функцию МАКСИМУМ(Регистратор). Запомните: это грубейшая ошибка!

Поле Регистратор является ссылочным типом данных. На уровне СУБД функция МАКСИМУМ для ссылок сравнивает их уникальные идентификаторы (GUID), а не даты документов. Поскольку GUID генерируются платформой не в строгом хронологическом порядке, функция МАКСИМУМ(Регистратор) вернет вам абсолютно случайный документ, а не тот, который был проведен последним по времени. Сначала всегда нужно находить МАКСИМУМ(Период), и только потом по найденному периоду получать соответствующий ему документ-регистратор.

Рекомендуемый эталонный подход: Использование связей наборов данных в СКД

Наиболее красивым, быстрым и «родным» для платформы 1С способом решения данной задачи является разделение логики на два независимых набора данных внутри схемы компоновки:

Шаг 1. Создаем НаборДанных1 (Остатки)

Этот набор данных будет отвечать только за получение остатков. Запрос получается очень легким, так как используется оптимальная виртуальная таблица Остатки:


ВЫБРАТЬ
    ХозрасчетныйОстатки.Субконто1 КАК Контрагент,
    ХозрасчетныйОстатки.Субконто2 КАК Договор,
    ХозрасчетныйОстатки.СуммаОстатокДт КАК СуммаОстатка
ИЗ
    РегистрБухгалтерии.Хозрасчетный.Остатки(&КонецПериода, Счет = &Счет, , Организация = &Организация) КАК ХозрасчетныйОстатки

Шаг 2. Создаем НаборДанных2 (ПоследнийДокумент)

Этот запрос будет параметризованным. Он должен находить ровно один (последний) документ движения по конкретному контрагенту и договору:


ВЫБРАТЬ ПЕРВЫЕ 1
    ХозрасчетныйДвижения.Период КАК Период,
    ХозрасчетныйДвижения.Регистратор КАК Регистратор
ИЗ
    РегистрБухгалтерии.Хозрасчетный КАК ХозрасчетныйДвижения
ГДЕ
    ХозрасчетныйДвижения.Субконто1 = &П_Контрагент
    И ХозрасчетныйДвижения.Субконто2 = &П_Договор
    И ХозрасчетныйДвижения.Счет = &Счет
    И ХозрасчетныйДвижения.Период <= &КонецПериода

УПОРЯДОЧИТЬ ПО
    Период УБЫВ

Шаг 3. Настройка связи наборов данных

Переходим на вкладку «Связи наборов данных» в СКД и связываем наши наборы следующим образом:

Источник связи: НаборДанных1
Приемник связи: НаборДанных2
Условие связи: Контрагент = П_Контрагент И Договор = П_Договор
Параметры: В колонке «Параметры» указываем передачу полей Контрагент в параметр &П_Контрагент, а Договор в &П_Договор.

При таком подходе СКД работает максимально эффективно: сначала она получает список контрагентов, у которых реально есть остатки на нужную дату. Затем, используя связь, платформа выполняет быстрые точечные запросы к индексам физической таблицы только по этим контрагентам, мгновенно вытаскивая последний документ движения. Это полностью исключает зависание базы данных и гарантирует стопроцентную точность результата.