Как исключить определенный вид документа из виртуальной таблицы Остатки и обороты с сохранением корректности данных

Программист 1С v8.3 (Управляемые формы) 1С:ERP Управление предприятием Управленческий учет Торговля и дистрибуция

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

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

Почему стандартный фильтр не подходит?

Проанализируем ситуацию: виртуальная таблица ОстаткиИОбороты строится на основе итогов регистра накопления. Если мы используем периодичность День, система выдает нам уже рассчитанные срезы на начало и конец дня. Попытка наложить фильтр на Регистратор внутри параметров таблицы при такой периодичности часто приводит к пустым результатам или некорректным данным, так как за один день может пройти множество документов разных типов.

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

Способ №1: Метод математического вычитания оборотов

Рассмотрим самый надежный метод, который подходит для большинства версий платформы. Его суть заключается в том, чтобы получить общие итоги (включая исключаемый документ) и отдельно получить обороты только по исключаемому документу, а затем вычесть их в итоговом запросе.

Разберем алгоритм по шагам:

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

Посмотрим на пример кода, реализующий эту логику:


ВЫБРАТЬ
    ОбщиеДанные.ОбъектРасчетов КАК ОбъектРасчетов,
    ОбщиеДанные.Период КАК Период,
    ОбщиеДанные.СуммаНачальныйОстаток КАК НачОстаток,
    ОбщиеДанные.СуммаПриход - ЕСТЬNULL(ИсключаемыеДанные.СуммаПриход, 0) КАК СкорректированныйПриход,
    ОбщиеДанные.СуммаРасход - ЕСТЬNULL(ИсключаемыеДанные.СуммаРасход, 0) КАК СкорректированныйРасход
ПОМЕСТИТЬ ВтСкорректированныеОбороты
ИЗ
    РегистрНакопления.РасчетыСПоставщиками.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, День, , ) КАК ОбщиеДанные
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.РасчетыСПоставщиками.Обороты(&НачалоПериода, &КонецПериода, День, ) КАК ИсключаемыеДанные
    ПО ОбщиеДанные.ОбъектРасчетов = ИсключаемыеДанные.ОбъектРасчетов
    И ОбщиеДанные.Период = ИсключаемыеДанные.Период
    И (ИсключаемыеДанные.Регистратор ССЫЛКА Документ.ВзаимозачетЗадолженности)
;

Важно помнить, что Конечный остаток в этом случае нельзя брать из таблицы ОбщиеДанные. Его необходимо рассчитать формулой: НачальныйОстаток + СкорректированныйПриход - СкорректированныйРасход.

Способ №2: Использование периодичности "Регистратор" и сворачивание данных

Выясним причину, по которой автор темы жаловался на "корявые" данные при смене периодичности. При использовании Периодичность = Регистратор, таблица возвращает записи на каждый момент движения. Чтобы получить данные по дням, нам нужно сначала отфильтровать записи, а затем сгруппировать их обратно до дня.

Проанализируем этот подход подробнее:

В виртуальной таблице указываем периодичность Регистратор.
В условии ГДЕ прописываем НЕ Регистратор ССЫЛКА Документ.ВзаимозачетЗадолженности.
Помещаем результат во временную таблицу.
В следующем запросе группируем данные по полям НАЧАЛОПЕРИОДА(Период, ДЕНЬ).

Однако здесь кроется ловушка: остатки в виртуальной таблице при такой фильтрации все равно будут "реальными" (из итогов), а не "виртуально пересчитанными". Поэтому данный способ эффективен только для получения оборотов, но не остатков.

Способ №3: Работа с физической таблицей движений (Самый точный метод)

Если нам нужно построить отчет, в котором остатки полностью игнорируют существование определенного документа, лучшим решением будет отказаться от виртуальной таблицы ОстаткиИОбороты и использовать физическую таблицу движений регистра (РегистрНакопления.ИмяРегистра).

Рассмотрим, как это сделать:

Получаем начальный остаток на дату начала отчета из таблицы Остатки.
Выбираем все записи из таблицы движений за период, исключая нужный документ через ГДЕ НЕ Регистратор ССЫЛКА ....
Объединяем эти данные и рассчитываем накопительный итог.

Для расчета накопительного итога (остатка) на каждую дату в современных версиях платформы (8.3.20+) мы рекомендуем использовать оконные функции. Это значительно ускоряет работу запроса.

Пример использования оконной функции для расчета остатка:


ВЫБРАТЬ
    Движения.Период КАК Период,
    Движения.ОбъектРасчетов КАК ОбъектРасчетов,
    Движения.Сумма КАК СуммаОборота,
    СУММА(Движения.Сумма) ОБРАБОТКА (УПОРЯДОЧИТЬ ПО Движения.Период) КАК НакопительныйОстаток
ИЗ
    (ВЫБРАТЬ
        Регистратор.Дата КАК Период,
        ОбъектРасчетов КАК ОбъектРасчетов,
        ВЫБОР КОГДА ВидДвижения = ЗНАЧЕНИЕ(ВидДвиженияНакопления.Приход) ТОГДА Сумма ИНАЧЕ -Сумма КОНЕЦ КАК Сумма
    ИЗ
        РегистрНакопления.РасчетыСПоставщиками
    ГДЕ
        Период МЕЖДУ &НачалоПериода И &КонецПериода
        И НЕ Регистратор ССЫЛКА Документ.ВзаимозачетЗадолженности) КАК Движения

Этот метод гарантирует, что каждая копейка в отчете будет обоснована только теми документами, которые мы оставили в выборке. Остаток будет "перетекать" правильно из одного дня в другой.

Важные замечания по производительности

Помните: исключение данных из виртуальных таблиц через Регистратор заставляет систему СУБД выполнять тяжелые операции сканирования таблиц вместо использования заранее подготовленных итогов. Если база данных содержит миллионы записей, расчет такого отчета может занять значительное время.

Чтобы оптимизировать работу, придерживайтесь следующих правил:

Всегда ограничивайте выборку по Объекту расчетов или Контрагенту в параметрах виртуальной таблицы, как это сделано в исходном запросе автора темы.
Если отчет строится часто, рассмотрите возможность добавления в регистр дополнительного реквизита-признака (например, ИсключатьИзУправленческойОтчетности), чтобы фильтровать по нему, не обращаясь к типу ссылки документа.
Используйте временные таблицы для промежуточных расчетов, чтобы не перегружать основной запрос сложными соединениями.

Итоги

Мы проанализировали ситуацию и выяснили, что задача исключения документа из ОстаткиИОбороты требует комплексного подхода. Простого фильтра недостаточно — необходимо либо вычитать обороты из общих итогов, либо строить расчет остатков "с нуля" по таблице движений. Выбор метода зависит от версии вашей платформы и требований к точности остатков. Для получения корректного управленческого баланса метод с физической таблицей движений и оконными функциями является наиболее предпочтительным.

← На главную