Как убрать задвоение остатков в отчете по товарам без движений при детализации по регистратору?

← На главную

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

В этой статье мы подробно проанализируем, почему платформа 1С:Предприятие ведет себя именно так, выясним первопричину проблемы и разберем четыре практических способа борьбы с «задвоением» строк для товаров без движений.

Выясняем причину: почему виртуальная таблица возвращает две строки?

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

Однако на практике мы получаем две отдельные строки по этому товару:

В первой строке заполнен только начальный остаток (конечный остаток равен нулю или отсутствует), а поле Регистратор пустое (имеет значение NULL или Неопределено).
Во второй строке заполнен только конечный остаток (начальный равен нулю), а поле Регистратор также пустое.

Почему так происходит? Регистры накопления физически не хранят остатки на каждый день или на каждую секунду, если не было движений. Чтобы выдать нам остатки на границы выбранного периода (на начало и на конец), платформа принудительно «дорисовывает» эти точки в выборке. Так как мы выбрали периодичность Регистратор и метод ДвиженияИГраницыПериода, система генерирует две искусственные «граничные» записи. В первой записи фиксируется состояние на начало периода, во второй — на конец. Движений между ними нет, поэтому мы видим две строки вместо одной.

Если же мы попытаемся установить метод дополнения в значение Движения, то платформа перестанет генерировать эти граничные точки. Но в этом случае возникнет другая проблема: товары, по которым не было движений в выбранном периоде, вообще пропадут из отчета, даже если по ним есть огромные остатки на складе.

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

Способ 1. Использование ролей полей в СКД (Рекомендуемый)

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

Сначала скорректируем текст нашего запроса. Нам нужно явно преобразовать пустое значение регистратора в реальный NULL. Проанализируем пример конструкции:


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

После того как текст запроса готов, перейдем к настройке схемы компоновки данных:

Откроем вкладку «Наборы данных» и найдем наше поле Регистратор.
В колонке «Роль» нажмем на кнопку редактирования.
В открывшемся окне свойств роли установим флажок «Игнорировать NULL».
Убедимся, что для поля Регистратор указан тип роли «Период» с соответствующим порядковым номером (обычно это дополнительный период).

Благодаря этой настройке СКД при выводе детальных записей автоматически «схлопнет» и скроет строки, в которых поле Регистратор содержит значение NULL. При этом на уровне группировки по номенклатуре остатки будут рассчитаны абсолютно верно, а сама номенклатура без движений не пропадет из отчета.

Способ 2. Отбор на уровне группировки в СКД

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

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

В параметрах виртуальной таблицы запроса оставляем метод дополнения ДвиженияИГраницыПериода. Это гарантирует, что товары без движений попадут в общую выборку.
Переходим на вкладку «Настройки» в схеме компоновки данных.
В структуре отчета находим группировку «Детальные записи» (или созданную нами группировку по полю Регистратор).
Выделяем именно эту группировку и переходим на ее локальную вкладку «Отбор» (обратите внимание, отбор нужно накладывать не на весь отчет, а конкретно на эту ветку структуры).
Добавляем условие отбора: Регистратор Заполнено (или Регистратор ЕСТЬ НЕ NULL).

Проанализируем результат: общие итоги по отчету и группировки верхнего уровня (по номенклатуре) рассчитаются правильно. Товары без движений выведутся в отчет со своими остатками. Но при этом детальные строки, содержащие пустые границы периодов, будут отсечены на этапе вывода детальных записей, и раздражающее «задвоение» исчезнет.

Способ 3. Переход на периодичность «Авто»

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

Рассмотрим, как правильно настроить такой запрос:

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

Пример структуры полей в запросе:


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

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

Способ 4. Сборка данных через объединение таблиц (для обычных запросов без СКД)

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

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

Проанализируем детальный пример реализации такого запроса:


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

СГРУППИРОВАТЬ ПО
    ИсходныеДанные.Номенклатура,
    ИсходныеДанные.Регистратор
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВременнаяТаблицаСбора.Номенклатура КАК Номенклатура,
    ВременнаяТаблицаСбора.Регистратор КАК Регистратор,
    ВременнаяТаблицаСбора.КоличествоНачальныйОстаток КАК КоличествоНачальныйОстаток,
    ВременнаяТаблицаСбора.КоличествоПриход КАК КоличествоПриход,
    ВременнаяТаблицаСбора.КоличествоРасход КАК КоличествоРасход,
    ВременнаяТаблицаСбора.КоличествоКонечныйОстаток КАК КоличествоКонечныйОстаток
ИЗ
    ВременнаяТаблицаСбора КАК ВременнаяТаблицаСбора

Разберем, как работает эта схема:

Первая таблица собирает все остатки на начало периода. Поля прихода, расхода и регистратора мы принудительно заполняем нулевыми и пустыми значениями.
Вторая таблица выбирает только обороты за указанный период с детализацией по регистраторам. Остатки здесь равны нулю.
Третья таблица выбирает остатки на конец периода. Поля прихода, расхода и регистратора снова заполняются нулями и пустым значением.
В завершение мы группируем все полученные строки по Номенклатуре и Регистратору, суммируя ресурсы.

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