Как корректно получить и вывести агрегированные данные из запроса в параметры отчета на 1С?

← На главную

При разработке отчетов в 1С мы часто сталкиваемся с необходимостью извлекать данные из базы, агрегировать их по определенным критериям, а затем выводить полученные итоги в заранее подготовленный макет. Такая задача может показаться простой на первый взгляд, но имеет свои нюансы, особенно когда речь идет о корректном взаимодействии между выборкой из запроса и параметрами макета. Рассмотрим подробнее, как избежать распространенных ошибок и эффективно заполнять параметры отчета.

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

Выясним причину: некорректное сравнение строковых значений

Основная причина, по которой параметры в отчете могли оставаться незаполненными, часто кроется в ошибках при сравнении строковых значений в условных конструкциях. Как справедливо было замечено в обсуждении, условие Если Выборка.НазначениеПомещения = "Жилая недвижимость" Тогда может быть «никогда не истина». Почему это происходит?

Лишние пробелы: Строка, полученная из базы данных (даже если она выглядит одинаково), может содержать лишние начальные или конечные пробелы. Например, "Жилая недвижимость " вместо "Жилая недвижимость".
Регистр символов: В некоторых СУБД или при определенных настройках платформы, сравнение строк может быть регистрозависимым. "Жилая недвижимость" не равно "жилая недвижимость".
Тип данных: Хотя в данном случае Номенклатура.бит_стр_НазначениеПомещения предполагает строковый тип, всегда стоит убедиться, что мы сравниваем однотипные данные.
Значения группировок: Если мы обходим выборку с группировками (ОбходРезультатаЗапроса.ПоГруппировкам), то на уровне итогов или некоторых промежуточных группировок поле НазначениеПомещения может быть NULL или пустым, а не конкретным строковым значением.

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

Сообщить("Полученное значение: """ + Выборка.НазначениеПомещения + """");

Это позволит нам увидеть, если есть какие-либо невидимые символы или отличия.

Решение 1: Корректная обработка выборки и присвоение параметров

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

Инициализация переменных: Перед началом цикла необходимо инициализировать переменные, в которых будут накапливаться суммы.
Обход выборки и сравнение: Внутри цикла мы обходим каждую строку выборки. Для каждой строки мы очищаем строковое значение от лишних пробелов с помощью СокрЛП(Строка(Выборка.НазначениеПомещения)) и затем сравниваем его с требуемыми строками.
Накопление сумм: Если условие выполняется, мы присваиваем или добавляем сумму к соответствующей переменной. Важно понимать, что если запрос уже сгруппирован по назначению помещения (как в данном случае ИТОГИ ПО НазначениеПомещения) и мы обходим группы, то Выборка.Сумма уже будет итоговой для данной группы. Если бы запрос был без итогов и мы обрабатывали детальные записи, то потребовалось бы накопление ДКПЖилая = ДКПЖилая + Выборка.Сумма. В данном случае мы исходим из предположения, что каждая группа выдается один раз.
Вывод области: После завершения цикла, когда значения ДКПЖилая и ДКПмашиноместо будут окончательно сформированы, мы присваиваем их параметрам области макета и выводим область в табличный документ.

Пример исправленного кода:


// ... (начало кода запроса и получения макета) ...

    Запрос.УстановитьПараметр("СчетДт", ПланыСчетов.Хозрасчетный.НайтиПоКоду("62"));
    Запрос.УстановитьПараметр("СчетКт", ПланыСчетов.Хозрасчетный.НайтиПоКоду("90.01.1"));
    Запрос.УстановитьПараметр("Организация", Организация);

    РезультатЗапроса = Запрос.Выполнить();
    // Обходим выборку по группировкам, чтобы получить итоги по "НазначениеПомещения"
    Выборка = РезультатЗапроса.Выбрать(ОбходРезультатаЗапроса.ПоГруппировкам);

    ОбластьШапка.Параметры.Дата = Формат(ТекущаяДата(),"ДФ=dd.MM.yyyy");
    ТабДок.Вывести(ОбластьШапка);

    ДКПЖилая = 0;
    ДКПмашиноместо = 0;

    Пока Выборка.Следующий() Цикл
        // Проверяем, что текущая строка выборки является группой по НазначениюПомещения
        // и не является общим итогом (где НазначениеПомещения может быть NULL/пустым)
        Если Выборка.Уровень() = 1 Тогда // Предполагаем, что группировка по НазначениеПомещения - первый уровень
            // Очищаем строку от лишних пробелов для надежного сравнения
            СтрокаНазначения = СокрЛП(Строка(Выборка.НазначениеПомещения));

            Если СтрокаНазначения = "Жилая недвижимость" Тогда
                ДКПЖилая = Выборка.Сумма; // Сумма уже итоговая для этой группы
            ИначеЕсли СтрокаНазначения = "Машиноместо (паркинг)" Тогда
                ДКПмашиноместо = Выборка.Сумма; // Сумма уже итоговая для этой группы
            КонецЕсли;

            // Для отладки
            Сообщить("Назначение: """ + СтрокаНазначения + """, Сумма: " + Выборка.Сумма);
        КонецЕсли;
    КонецЦикла;

    ОбластьВыручка.Параметры.ВыручкаДКПЖилая = (ДКПЖилая/1000);
    ОбластьВыручка.Параметры.ВыручкаДКПмашиноместо = (ДКПмашиноместо/1000);
    ТабДок.Вывести(ОбластьВыручка);

Мы добавили проверку Выборка.Уровень() = 1, чтобы убедиться, что мы работаем именно с группами по НазначениеПомещения, а не с детальными записями или общими итогами, где это поле может быть неопределенным или пустым.

Решение 2: Агрегация данных напрямую в запросе (пивотирование)

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

Преимущества данного метода:

Оптимизация производительности: Все вычисления выполняются на стороне СУБД, которая оптимизирована для таких задач.
Упрощение кода на 1С: Нам не потребуется сложный цикл обхода выборки с условиями, так как запрос вернет уже готовые агрегированные значения.
Чистота и читаемость: Код становится более компактным и понятным.

Для реализации этого подхода мы используем конструкцию СУММА(ВЫБОР КОГДА ... ТОГДА ... ИНАЧЕ 0 КОНЕЦ) внутри запроса.

Пример модифицированного запроса:


// ... (получение макета) ...

    Запрос = Новый Запрос;
    Запрос.Текст =
    "ВЫБРАТЬ
    |    СУММА(ВЫБОР
    |            КОГДА ЕСТЬNULL(Номенклатура.бит_стр_НазначениеПомещения, """""") = ""Жилая недвижимость""
    |                ТОГДА ХозрасчетныйДвиженияССубконто.Сумма
    |            ИНАЧЕ 0
    |        КОНЕЦ) КАК СуммаЖилаяНедвижимость,
    |    СУММА(ВЫБОР
    |            КОГДА ЕСТЬNULL(Номенклатура.бит_стр_НазначениеПомещения, """""") = ""Машиноместо (паркинг)""
    |                ТОГДА ХозрасчетныйДвиженияССубконто.Сумма
    |            ИНАЧЕ 0
    |        КОНЕЦ) КАК СуммаМашиноместо
    |ИЗ
    |    РегистрБухгалтерии.Хозрасчетный.ДвиженияССубконто КАК ХозрасчетныйДвиженияССубконто
    |        ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Номенклатура КАК Номенклатура
    |        ПО (ХозрасчетныйДвиженияССубконто.СубконтоКт3 = Номенклатура.Ссылка)
    |ГДЕ
    |    ХозрасчетныйДвиженияССубконто.Активность = ИСТИНА
    |    И ХозрасчетныйДвиженияССубконто.СчетДт В ИЕРАРХИИ(&СчетДт)
    |    И ХозрасчетныйДвиженияССубконто.СчетКт = &СчетКт
    |    И ХозрасчетныйДвиженияССубконто.Организация = &Организация"; // Здесь не требуется СГРУППИРОВАТЬ ПО, если нужны только общие итоги по всему набору данных.

    Запрос.УстановитьПараметр("СчетДт", ПланыСчетов.Хозрасчетный.НайтиПоКоду("62"));
    Запрос.УстановитьПараметр("СчетКт", ПланыСчетов.Хозрасчетный.НайтиПоКоду("90.01.1"));
    Запрос.УстановитьПараметр("Организация", Организация);

    РезультатЗапроса = Запрос.Выполнить();
    // Теперь выборка содержит всего одну строку с агрегированными суммами
    Выборка = РезультатЗапроса.Выбрать();
    Если Выборка.Следующий() Тогда
        ОбластьВыручка.Параметры.ВыручкаДКПЖилая = (Выборка.СуммаЖилаяНедвижимость / 1000);
        ОбластьВыручка.Параметры.ВыручкаДКПмашиноместо = (Выборка.СуммаМашиноместо / 1000);
    Иначе
        // Если запрос не вернул ни одной строки (например, нет данных)
        ОбластьВыручка.Параметры.ВыручкаДКПЖилая = 0;
        ОбластьВыручка.Параметры.ВыручкаДКПмашиноместо = 0;
    КонецЕсли;

    ТабДок.Вывести(ОбластьВыручка);

Обратите внимание, что в этом случае нам не нужны группировки СГРУППИРОВАТЬ ПО и ИТОГИ ПО в запросе, если мы хотим получить только две итоговые суммы. Запрос сам по себе выполняет агрегацию по всему набору данных, возвращая одну строку.

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

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

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

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

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

Альтернативный подход: Таблица значений как параметр запроса (решение автора)

Автор в процессе решения проблемы пришел к выводу: "Из запроса в ТЗ, а из ТЗ в параметр :)". Этот подход, хотя и менее оптимален для данной конкретной задачи, имеет свое применение в 1С. Обычно он используется, когда необходимо передать в запрос сложный набор данных (например, список элементов, которые нельзя просто передать как массив или один параметр) или когда нужно выполнить многошаговую обработку данных.

Как работает этот подход:

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

Примерная логика (без полного кода, так как автор не предоставил свою реализацию):


// 1. Создание ТаблицыЗначений и заполнение ее данными из первого запроса
ТаблицаИтогов = Новый ТаблицаЗначений;
ТаблицаИтогов.Колонки.Добавить("НазначениеПомещения", Новый ОписаниеТипов("Строка", , , 200)); // Указываем тип и размерность
ТаблицаИтогов.Колонки.Добавить("Сумма", Новый ОписаниеТипов("Число", 15, 2)); // Указываем тип и размерность

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

ЗапросИсходный.УстановитьПараметр("СчетДт", ПланыСчетов.Хозрасчетный.НайтиПоКоду("62"));
ЗапросИсходный.УстановитьПараметр("СчетКт", PlansSсhetov.Хозрасчетный.НайтиПоКоду("90.01.1"));
ЗапросИсходный.УстановитьПараметр("Организация", Организация);

РезультатЗапросаИсходный = ЗапросИсходный.Выполнить();
ВыборкаИсходная = РезультатЗапросаИсходный.Выбрать();
Пока ВыборкаИсходная.Следующий() Цикл
    НоваяСтрока = ТаблицаИтогов.Добавить();
    НоваяСтрока.НазначениеПомещения = ВыборкаИсходная.НазначениеПомещения;
    НоваяСтрока.Сумма = ВыборкаИсходная.Сумма;
КонецЦикла;

// 2. Передача ТаблицыЗначений как параметра в новый запрос для финальной агрегации
ЗапросДляПараметров = Новый Запрос;
ЗапросДляПараметров.Текст =
"ВЫБРАТЬ
|    СУММА(ВЫБОР
|            КОГДА ТаблицаИтогов.НазначениеПомещения = ""Жилая недвижимость""
|                ТОГДА ТаблицаИтогов.Сумма
|            ИНАЧЕ 0
|        КОНЕЦ) КАК СуммаЖилаяНедвижимость,
|    СУММА(ВЫБОР
|            КОГДА ТаблицаИтогов.НазначениеПомещения = ""Машиноместо (паркинг)""
|                ТОГДА ТаблицаИтогов.Сумма
|            ИНАЧЕ 0
|        КОНЕЦ) КАК СуммаМашиноместо
|ИЗ
|    &ТаблицаИтогов КАК ТаблицаИтогов";

ЗапросДляПараметров.УстановитьПараметр("ТаблицаИтогов", ТаблицаИтогов);
РезультатИтогов = ЗапросДляПараметров.Выполнить();
ВыборкаИтогов = РезультатИтогов.Выбрать();
Если ВыборкаИтогов.Следующий() Тогда
    ОбластьВыручка.Параметры.ВыручкаДКПЖилая = (ВыборкаИтогов.СуммаЖилаяНедвижимость / 1000);
    ОбластьВыручка.Параметры.ВыручкаДКПмашиноместо = (ВыборкаИтогов.SуммаМашиноместо / 1000);
Иначе
    ОбластьВыручка.Параметры.ВыручкаДКПЖилая = 0;
    ОбластьВыручка.Параметры.ВыручкаДКПмашиноместо = 0;
КонецЕсли;

ТабДок.Вывести(ОбластьВыручка);

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

Общие рекомендации по разработке отчетов

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

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

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