При разработке отчетов в системе 1С программисты часто сталкиваются с задачей, когда необходимо вывести данные из двух разных источников: регистра накопления остатков (например, ПереданнаяВозвратнаяТара) и регистра оборотов (например, ДвиженияСерийНоменклатуры). Основная сложность заключается в разной детализации (гранулярности) данных. Если в регистре остатков нет измерения Серия, а в регистре оборотов оно есть, то обычное соединение (ЛЕВОЕ СОЕДИНЕНИЕ) приведет к тому, что сумма остатка из первого регистра продублируется для каждой строки с серией из второго регистра. В итоге общие итоги по отчету окажутся неверными.
В этой статье мы разберем несколько профессиональных подходов, которые позволяют корректно решить эту задачу, обеспечив точность данных и удобство настройки отчета в Системе компоновки данных (СКД).
Проанализируем самый надежный способ, который позволяет избежать дублирования данных на уровне запроса. Вместо того чтобы соединять таблицы «в бок», мы будем объединять их «вниз», приводя к единой структуре. Суть метода заключается в создании трех выборок, которые затем группируются.
Рассмотрим структуру такого запроса подробнее:
Остатки на дату начала периода. Поле Серия заполняем значением NULL или пустой ссылкой.Приход и Расход в разрезе серий.Разберем пример кода запроса, который реализует эту логику:
ВЫБРАТЬ
Т.Номенклатура КАК Номенклатура,
Т.Серия КАК Серия,
СУММА(Т.НачальныйОстаток) КАК НачальныйОстаток,
СУММА(Т.Приход) КАК Приход,
СУММА(Т.Расход) КАК Расход,
СУММА(Т.КонечныйОстаток) КАК КонечныйОстаток
ИЗ
(ВЫБРАТЬ
Остатки.Номенклатура КАК Номенклатура,
ЗНАЧЕНИЕ(Справочник.СерииНоменклатуры.ПустаяСсылка) КАК Серия,
Остатки.КоличествоОстаток КАК НачальныйОстаток,
0 КАК Приход,
0 КАК Расход,
0 КАК КонечныйОстаток
ИЗ
РегистрНакопления.ПереданнаяВозвратнаяТара.Остатки(&НачалоПериода, ) КАК Остатки
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ОборотыСерий.Номенклатура,
ОборотыСерий.Серия,
0,
ОборотыСерий.КоличествоПриход,
ОборотыСерий.КоличествоРасход,
0
ИЗ
РегистрНакопления.ДвиженияСерийНоменклатуры.Обороты(&НачалоПериода, &КонецПериода, , ) КАК ОборотыСерий
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
Остатки.Номенклатура,
ЗНАЧЕНИЕ(Справочник.СерииНоменклатуры.ПустаяСсылка),
0,
0,
0,
Остатки.КоличествоОстаток
ИЗ
РегистрНакопления.ПереданнаяВозвратнаяТара.Остатки(&КонецПериода, ) КАК Остатки) КАК Т
СГРУППИРОВАТЬ ПО
Т.Номенклатура,
Т.Серия
Этот метод гарантирует, что общая сумма остатков по номенклатуре останется неизменной, а обороты будут детализированы по сериям. Однако у него есть нюанс: остатки будут «висеть» на пустой серии.
Выясним причину, по которой иногда стоит отказаться от использования виртуальной таблицы остатков. Если нам критически важно видеть остаток именно «по сериям», а в основном регистре их нет, мы можем попробовать рассчитать остатки, опираясь на данные регистра, где серии присутствуют.
Как отметил один из участников обсуждения, общий план расчета может выглядеть так:
Посмотрим на пример логики в запросе:
ВЫБРАТЬ
Обороты.Номенклатура,
Обороты.Серия,
СУММА(ВЫБОР
КОГДА Обороты.Период < &ДатаНачала
ТОГДА Обороты.КоличествоПриход - Обороты.КоличествоРасход
ИНАЧЕ 0
КОНЕЦ) КАК НачальныйОстаток,
СУММА(ВЫБОР
КОГДА Обороты.Период МЕЖДУ &ДатаНачала И &ДатаКонца
ТОГДА Обороты.КоличествоПриход
ИНАЧЕ 0
КОНЕЦ) КАК Приход,
СУММА(ВЫБОР
КОГДА Обороты.Период МЕЖДУ &ДатаНачала И &ДатаКонца
ТОГДА Обороты.КоличествоРасход
ИНАЧЕ 0
КОНЕЦ) КАК Расход,
СУММА(Обороты.КоличествоПриход - Обороты.КоличествоРасход) КАК КонечныйОстаток
ИЗ
РегистрНакопления.ДвиженияСерийНоменклатуры КАК Обороты
ГДЕ
Обороты.Период <= &ДатаКонца
СГРУППИРОВАТЬ ПО
Обороты.Номенклатура,
Обороты.Серия
Важное предостережение: этот метод сработает только в том случае, если вся тара проходит по регистру оборотов серий. Если часть тары не имеет серий и не записывается в этот регистр, данные по ней будут потеряны. В таком случае необходимо комбинировать данные из двух регистров через ОБЪЕДИНИТЬ ВСЕ, как описано в первом методе.
Рассмотрим более элегантный способ, предоставляемый платформой 1С — использование системы компоновки данных. Вместо того чтобы писать один сложный запрос, мы можем создать два отдельных набора данных (Query) и связать их на закладке «Связи наборов данных».
Пошаговый алгоритм настройки:
Номенклатура (и Склад, если есть).Преимущество этого метода в том, что СКД самостоятельно управляет итогами. Если вы правильно настроите роли полей (укажете, что поле КоличествоНачальныйОстаток является остатком), система не будет суммировать его повторно при выводе детальных записей по сериям. Это позволяет избежать «размножения» цифр без написания громоздких UNION-запросов.
Разберем ситуацию, когда вы все же решили использовать ЛЕВОЕ СОЕДИНЕНИЕ. Чтобы СКД не дублировала остатки в итогах, необходимо зайти на закладку Набор данных и в колонке Роли настроить параметры для ресурсов остатка.
Для поля НачальныйОстаток установите следующие настройки:
ПериодСекунда).Количество), чтобы СКД понимала связь между начальным и конечным значением.Благодаря этим настройкам, СКД применит специальный алгоритм агрегации: она будет брать значение остатка только один раз для каждой группировки по номенклатуре, игнорируя дубли строк, возникшие из-за присоединения таблицы серий.
Выбор конкретного метода зависит от ваших целей. Если вам нужно просто «добавить серии рядом», используйте Связи наборов данных в СКД. Если вам нужен сложный расчет с учетом того, что часть тары может не иметь серий вовсе, используйте ОБЪЕДИНИТЬ ВСЕ.
Помните о проблеме «пустых серий». Если остаток сформировался документом без указания серии, в отчете такая строка должна быть четко видна. Для этого используйте функцию ЕСТЬNULL(Серия, "Без серии") в тексте запроса или настройках оформления СКД. Проанализировав все способы, мы видим, что наиболее надежным и расширяемым является подход с объединением выборок, так как он дает программисту полный контроль над каждой цифрой в отчете.