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