При разработке отчетов и аналитических обработок в 1С часто возникает задача получить данные из регистра накопления с фильтрацией по конкретным типам документов-регистраторов (например, только по ПоступлениеТоваровУслуг и ОтчетОРозничныхПродажах), а затем сгруппировать информацию по номенклатуре и корректно вывести ее в макет табличного документа. Разберем по шагам, как правильно составить такой запрос, какие типичные ошибки допускают начинающие разработчики и как эффективно организовать программный вывод данных.
Часто начинающие разработчики пытаются решить задачу выборки данных по нескольким типам документов через конструкцию ОБЪЕДИНИТЬ или ОБЪЕДИНИТЬ ВСЕ, дублируя один и тот же текст запроса с разными условиями в секции ГДЕ. Посмотрим на типичный пример неоптимального подхода:
ВЫБРАТЬ
Товары.Номенклатура КАК Номенклатура,
Товары.Склад КАК Склад,
Товары.ВНаличииПриход КАК ВНаличииПриход,
Товары.ВНаличииРасход КАК ВНаличииРасход,
Товары.Регистратор КАК Регистратор
ИЗ
РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, Регистратор, , ) КАК Товары
ГДЕ
Товары.Регистратор ССЫЛКА Документ.ПоступлениеТоваровУслуг
ОБЪЕДИНИТЬ
ВЫБРАТЬ
Товары.Номенклатура,
Товары.Склад,
Товары.ВНаличииПриход,
Товары.ВНаличииРасход,
Товары.Регистратор
ИЗ
РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, Регистратор, , ) КАК Товары
ГДЕ
Товары.Регистратор ССЫЛКА Документ.ОтчетОРозничныхПродажах
Проанализируем недостатки такого решения:
ОБЪЕДИНИТЬ (без ключевого слова ВСЕ) выполняет неявную проверку на уникальность строк с предварительной сортировкой всего массива данных, что создает колоссальную нагрузку на сервер и память СУБД.В языке запросов 1С поддерживается стандартная логическая операция ИЛИ. Мы можем объединить фильтры по типам документов в рамках одного условия ГДЕ, используя оператор ССЫЛКА. Рассмотрим правильный синтаксис:
ВЫБРАТЬ
Товары.Номенклатура КАК Номенклатура,
Товары.Склад КАК Склад,
Товары.Регистратор КАК Регистратор,
Товары.ВНаличииПриход КАК Приход,
Товары.ВНаличииРасход КАК Расход
ИЗ
РегистрНакопления.ТоварыНаСкладах.Обороты(&НачалоПериода, &КонецПериода, Регистратор, Склад = &Склад И Номенклатура В ИЕРАРХИИ (&Номенклатура)) КАК Товары
ГДЕ
Товары.Регистратор ССЫЛКА Документ.ПоступлениеТоваровУслуг
ИЛИ Товары.Регистратор ССЫЛКА Документ.ОтчетОРозничныхПродажах
ИТОГИ
СУММА(Приход),
СУММА(Расход)
ПО
Номенклатура
Этот вариант компактен, понятен и транслируется платформой 1С в эффективный SQL-код.
Если типов документов много, вместо длинной цепочки условий ИЛИ можно использовать функцию ТИПЗНАЧЕНИЯ в комбинации с оператором множественной проверки В. Посмотрим на реализацию:
ВЫБРАТЬ
Товары.Номенклатура КАК Номенклатура,
Товары.Регистратор КАК Регистратор,
Товары.ВНаличииПриход КАК Приход,
Товары.ВНаличииРасход КАК Расход
ИЗ
РегистрНакопления.ТоварыНаСкладах.Обороты(&НачалоПериода, &КонецПериода, Регистратор, Склад = &Склад) КАК Товары
ГДЕ
ТИПЗНАЧЕНИЯ(Товары.Регистратор) В (
ТИП(Документ.ПоступлениеТоваровУслуг),
ТИП(Документ.ОтчетОРозничныхПродажах),
ТИП(Документ.ПеремещениеТоваров)
)
Важное примечание: С точки зрения быстродействия при небольшом количестве типов документов (2-4 типа) проверка через оператор ССЫЛКА обычно работает быстрее, так как напрямую использует внутренний номер типа ссылки в индексах таблиц 1С.
Обратим внимание на критическую ошибку в логике работы с регистрами накопления. Если мы обращаемся к таблице ОстаткиИОбороты с периодичностью Регистратор и одновременно накладываем фильтр на поле Регистратор в секции ГДЕ, начальные и конечные остатки теряют смысл и становятся искаженными.
Выясним причину: таблица ОстаткиИОбороты рассчитывает остаток на момент каждого регистратора последовательно. Если мы фильтруем регистраторы через ГДЕ, платформа исключает строки с другими документами, из-за чего непрерывность расчета остатков нарушается. Кроме того, суммирование полей ВНаличииНачальныйОстаток и ВНаличииКонечныйОстаток (например, СУММА(Товары.ВНаличииНачальныйОстаток)) приводит к математическому задвоению остатков на каждую детальную строку.
Разберем, как поступить правильно в зависимости от задачи:
Обороты. Она работает значительно быстрее и не выполняет лишних вычислений остатков.ОстаткиИОбороты без отбора по регистратору в ГДЕ, а выделение движений конкретных документов делаем через условную конструкцию ВЫБОР КОГДА.Посмотрим на пример правильного разделения:
ВЫБРАТЬ
Товары.Номенклатура КАК Номенклатура,
Товары.ВНаличииНачальныйОстаток КАК НачальныйОстаток,
Товары.ВНаличииКонечныйОстаток КАК КонечныйОстаток,
Товары.Регистратор КАК Регистратор,
ВЫБОР
КОГДА Товары.Регистратор ССЫЛКА Документ.ПоступлениеТоваровУслуг
ТОГДА Товары.ВНаличииПриход
ИНАЧЕ 0
КОНЕЦ КАК ПриходПоПоступлениям,
ВЫБОР
КОГДА Товары.Регистратор ССЫЛКА Документ.ОтчетОРозничныхПродажах
ТОГДА Товары.ВНаличииРасход
ИНАЧЕ 0
КОНЕЦ КАК РасходПоПродажам
ИЗ
РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(
&НачалоПериода,
&КонецПериода,
Регистратор,
,
Склад = &Склад
) КАК Товары
ИТОГИ
МАКСИМУМ(НачальныйОстаток),
МАКСИМУМ(КонечныйОстаток),
СУММА(ПриходПоПоступлениям),
СУММА(РасходПоПродажам)
ПО
Номенклатура
Обратите внимание на то, куда помещаются условия отбора. Условие по измерению Склад = &Склад и Номенклатура В ИЕРАРХИИ (&Номенклатура) должно передаваться строго в параметры виртуальной таблицы (четвертый параметр для таблицы ОстаткиИОбороты или Обороты), а не в секцию ГДЕ.
Если условие помещено в ГДЕ, СУБД сначала прочитает весь регистр по всем складам компании, рассчитает временные таблицы и лишь затем отфильтрует строки. Передача условий в параметры виртуальной таблицы отсекает лишние данные на самом раннем этапе исполнения запроса.
Если отчет строится вручную через код в обработке (без системы компоновки данных), для сворачивания строк по группировке Номенклатура с детализацией по документам необходимо использовать иерархический обход выборки с помощью метода Выбрать() с параметром ОбходРезультатаЗапроса.ПоГруппировкам.
Разберем по шагам типовой алгоритм вывода данных в макет:
// 1. Инициализация табличного документа и макета
ТабДок = Новый ТабличныйДокумент;
Макет = ПолучитьМакет("МакетОтчета");
ОбластьШапка = Макет.ПолучитьОбласть("Шапка");
ОбластьГруппаНоменклатура = Макет.ПолучитьОбласть("ГруппаНоменклатура");
ОбластьСтрокаДокумент = Макет.ПолучитьОбласть("СтрокаДокумент");
ОбластьПодвал = Макет.ПолучитьОбласть("Подвал");
ТабДок.Вывести(ОбластьШапка);
// 2. Выполнение запроса
РезультатЗапроса = Запрос.Выполнить();
// 3. Обход верхнего уровня итогов (по группировке Номенклатура)
ВыборкаНоменклатура = РезультатЗапроса.Выбрать(ОбходРезультатаЗапроса.ПоГруппировкам, "Номенклатура");
Пока ВыборкаНоменклатура.Следующий() Цикл
// Заполняем параметры области группировки общими суммами
ОбластьГруппаНоменклатура.Параметры.Заполнить(ВыборкаНоменклатура);
ТабДок.Вывести(ОбластьГруппаНоменклатура, ВыборкаНоменклатура.Уровень());
// 4. Обход подчиненных детальных записей (Регистраторов)
ВыборкаДетали = ВыборкаНоменклатура.Выбрать();
Пока ВыборкаДетали.Следующий() Цикл
// Заполняем параметры детальной строки конкретного документа
ОбластьСтрокаДокумент.Параметры.Заполнить(ВыборкаДетали);
ТабДок.Вывести(ОбластьСтрокаДокумент, ВыборкаДетали.Уровень());
КонецЦикла;
КонецЦикла;
// 5. Вывод общих итогов отчета
ОбластьПодвал.Параметры.Заполнить(РезультатЗапроса.Выбрать(ОбходРезультатаЗапроса.ПоГруппировкам));
ТабДок.Вывести(ОбластьПодвал);
ТабДок.Показать();
При таком подходе отчет получается структурированным, а табличный документ автоматически поддерживает сворачивание и разворачивание группировок благодаря передаче уровня иерархии вторым параметром в метод Вывести().