Почему в запросе к регистру накопления 'ОстаткиИОбороты' появляются дополнительные строки с начальными и конечными остатками?

← На главную

Многие разработчики 1С сталкиваются с неожиданным поведением виртуальной таблицы ОстаткиИОбороты регистра накопления, когда при запросе, сгруппированном по регистратору, выводятся дополнительные строки с начальными и конечными остатками, не привязанными к конкретным движениям. Давайте разберем эту ситуацию на примере и выясним, почему так происходит, а также как можно решить данную проблему, чтобы получить ожидаемый и понятный отчет.

Предположим, у нас есть регистр накопления "Оборудование" со следующей структурой:

Измерения: МестоХранения (составной тип: Справочники.Организации и Справочники.Партнеры), Оборудование (тип Справочники.Оборудование).
Ресурс: Количество (Число).

В этом регистре отражаются движения основных средств по клиентам и организациям. Допустим, в регистре имеются следующие записи по двум документам: "Поступление оборудования в организацию" и "Передача оборудования клиенту":


Период               Регистратор                            Номер строки    Место хранения    Оборудование    Количество
29.11.2021 1:37:10   Поступление оборудования 000000001      1               Организация1      Холодильник     2
10.12.2021 18:00:02  Передача оборудования клиенту 000000001  1               Организация1      Холодильник     -1
10.12.2021 18:00:02  Передача оборудования клиенту 000000001  2               Клиент 2          Холодильник     1

При выполнении запроса, который должен показать начальные и конечные остатки, а также движения по оборудованию в разрезе документов, мы используем следующую конструкцию запроса, задав период с 01.11.2021 по 31.12.2021:


ВЫБРАТЬ
    ОборудованиеОстаткиИОбороты.МестоХранения КАК МестоХранения,
    ОборудованиеОстаткиИОбороты.Оборудование КАК Оборудование,
    ОборудованиеОстаткиИОбороты.Регистратор КАК Регистратор,
    ЕСТЬNULL(ОборудованиеОстаткиИОбороты.КоличествоНачальныйОстаток, 0) КАК НачальныйОстаток,
    ЕСТЬNULL(ОборудованиеОстаткиИОбороты.КоличествоПриход, 0) КАК Приход,
    ЕСТЬNULL(ОборудованиеОстаткиИОбороты.КоличествоРасход, 0) КАК Расход,
    ЕСТЬNULL(ОборудованиеОстаткиИОбороты.КоличествоКонечныйОстаток, 0) КАК КонечныйОстаток
ИЗ
    РегистрНакопления.Оборудование.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, Регистратор, , ) КАК ОборудованиеОстаткиИОбороты

УПОРЯДОЧИТЬ ПО
    ОборудованиеОстаткиИОбороты.МестоХранения.Наименование,
    ОборудованиеОстаткиИОбороты.Регистратор.Дата
ИТОГИ
    СУММА(НачальныйОстаток),
    СУММА(Приход),
    СУММА(Расход),
    СУММА(КонечныйОстаток)
ПО
    МестоХранения,
    Оборудование

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

Почему появляются дополнительные строки с остатками? Анализируем поведение виртуальной таблицы ОстаткиИОбороты

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

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

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

Это поведение особенно заметно, когда:

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

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

Решения проблемы: Как получить ожидаемый результат

Существует несколько подходов для решения этой проблемы и получения более точного и понятного отчета без дублирующих или "лишних" строк с остатками.

Решение 1: Анализ параметров ОстаткиИОбороты и использование фильтрации

В нашем исходном запросе мы используем Регистратор как третий параметр виртуальной таблицы ОстаткиИОбороты, что определяет периодичность получения данных. Это означает, что для каждой уникальной комбинации измерений и регистратора будут выведены остатки и обороты. Как мы выяснили, даже при отсутствии оборотов по конкретному регистратору, таблица может вывести остатки.

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


ВЫБРАТЬ
    ...
ИЗ
    РегистрНакопления.Оборудование.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, Регистратор, , ) КАК ОборудованиеОстаткиИОбороты
ГДЕ
    ЕСТЬNULL(ОборудованиеОстаткиИОбороты.КоличествоПриход, 0) <> 0
    ИЛИ ЕСТЬNULL(ОборудованиеОстаткиИОбороты.КоличествоРасход, 0) <> 0
УПОРЯДОЧИТЬ ПО
    ...
ИТОГИ
    ...
ПО
    ...

Этот фильтр исключит те строки, где Приход и Расход равны нулю. Однако, важно понимать, что этот подход также исключит строки, которые изначально несут информацию только о начальном или конечном остатке, но не имеют детализации по регистратору (например, суммарные начальные остатки на начало периода, которые вы хотели бы видеть в отдельной строке). Если наша цель — именно разделить информацию об остатках и оборотах, то этот метод не является идеальным, поскольку он просто "обрезает" данные, а не реструктурирует их. Для более тонкого контроля нам потребуются более сложные подходы.

Решение 2: Раздельное использование виртуальных таблиц Остатки и Обороты с последующим объединением

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

Получение движений: Мы используем виртуальную таблицу Обороты регистра накопления. Она показывает только приход и расход за период, сгруппированные по выбранным измерениям и, при необходимости, по регистратору.
Получение начальных остатков: Используем виртуальную таблицу Остатки на дату НачалоПериода, чтобы получить остатки на начало отчетного периода.
Получение конечных остатков: Используем виртуальную таблицу Остатки на дату КонецПериода, чтобы получить остатки на конец отчетного периода.
Объединение: Результаты этих запросов можно объединить с помощью оператора ОБЪЕДИНИТЬ ВСЕ, а затем выполнить окончательную группировку и расчет итогов.

Давайте посмотрим, как можно построить такой запрос, чтобы четко разграничить типы данных:


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

ОБЪЕДИНИТЬ ВСЕ

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

ОБЪЕДИНИТЬ ВСЕ

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

УПОРЯДОЧИТЬ ПО
    МестоХранения.Наименование,
    Оборудование.Наименование,
    ДатаДвижения,
    ТипЗаписи, // Сначала начальные остатки, потом движения, потом конечные
    Регистратор.Дата // Для движений

Этот запрос будет сложнее, но он дает полный контроль над тем, какие данные и в каком виде выводятся. Мы специально заполняем ненужные поля нулями или НЕОПРЕДЕЛЕНО, чтобы обеспечить совместимость типов при объединении. Важно правильно задать условия и параметры для каждой виртуальной таблицы, а также корректно упорядочить результат для получения логичной последовательности: сначала начальный остаток по группе измерений, затем все движения по регистраторам, и в конце — конечный остаток.

Мы используем дополнительное поле ДатаДвижения и ТипЗаписи для правильной сортировки. ДатаДвижения позволяет нам расположить начальный остаток по конкретному измерению перед первыми движениями, а конечный остаток — после последних движений. ТипЗаписи помогает определить порядок вывода внутри одной даты, если таковое необходимо.

Решение 3: Использование Системы Компоновки Данных (СКД)

Для построения гибких и сложных отчетов, особенно когда требуется динамическое управление группировками, детальными записями и расчетом итогов, Система Компоновки Данных (СКД) является предпочтительным инструментом. В СКД мы можем достичь желаемого результата, не прибегая к сложному ручному написанию объединенных запросов.

Давайте разберем основные шаги и преимущества использования СКД для нашей задачи:

Определение набора данных: Мы можем использовать виртуальную таблицу ОстаткиИОбороты как основной источник данных. СКД умеет работать с ней очень эффективно.
Настройка полей: Выбираем необходимые поля (МестоХранения, Оборудование, Регистратор, КоличествоНачальныйОстаток, КоличествоПриход, КоличествоРасход, КоличествоКонечныйОстаток) из набора данных.
Определение группировок: Создаем иерархическую структуру отчета. Например, сначала группируем по МестоХранения, затем по Оборудование, и уже внутри — по Регистратор.
Управление выводом детальных записей и итогов: Это ключевой момент в СКД.
- В настройках структуры отчета для группировки по Оборудование мы можем указать, чтобы НачальныйОстаток выводился только в заголовке этой группировки (перед всеми движениями и остатками по регистраторам).
- Аналогично, КонечныйОстаток может выводиться только в подвале этой группировки (после всех движений).
- Поля Приход и Расход мы можем настроить для вывода только в детальных записях или в группировке по Регистратор.
- При использовании группировки по Регистратор, СКД автоматически сгенерирует строки только там, где есть движения, связанные с данным регистратором, что устранит проблему "лишних" строк с нулевыми оборотами, которые создавала ручная выборка из ОстаткиИОбороты.
Использование вычисляемых полей и выражений: СКД позволяет создавать сложную логику без написания большого объема SQL-кода вручную. Например, можно создать вычисляемые поля, которые агрегируют данные или форматируют их специфическим образом.

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

Преимущества СКД:

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

Если задача требует частого формирования отчетов с разными разрезами и детализацией, освоение СКД — критически важный навык.

Дополнительные рекомендации

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

Важный момент: всегда четко понимайте, какую информацию вы хотите получить и в каком виде.

Если вам нужны только движения по документам за период, используйте виртуальную таблицу Обороты.
Если вам нужны только остатки на конкретную дату, используйте виртуальную таблицу Остатки.
Если вам нужна сложная агрегация с остатками и оборотами, где остатки и движения должны быть четко разделены и структурированы, как в нашем случае, рассмотрите объединение запросов к Оборотам и Остаткам, или же построение отчета с помощью СКД.

Заключение

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