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

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

Как СКД "дорабатывает" запрос и вызывает неявную группировку

СКД в 1С — это мощный инструмент, который оптимизирует запросы к базе данных для достижения максимальной производительности. Частью этой оптимизации является модификация исходного запроса набора данных, который мы определяем. Когда мы исключаем поле из вывода в настройках отчета, но это поле присутствует в запросе (особенно если оно используется в секции ВЫБРАТЬ), СКД может автоматически удалить его из итогового запроса, который будет выполнен в СУБД. Это происходит, потому что СКД считает, что если поле не используется для вывода, сортировки или группировки, оно не нужно в конечном запросе.

Суть проблемы заключается в следующем: если наш исходный запрос уже содержит группировку (например, через оператор СГРУППИРОВАТЬ ПО) или подразумевает ее из-за наличия агрегатных функций, то удаление одного из полей, по которым происходила детализация, приведет к тому, что группировка будет выполнена по оставшимся полям. Это автоматически агрегирует данные до более высокого уровня, что и создает впечатление "неявной группировки", как будто система сгруппировала результат, а потом его вывела, теряя исходную детализацию.

Мы видим детальные записи, когда все ключевые поля присутствуют. Например, если у нас есть данные по "Период", "Филиал", "Название", "План" и "Контрагент", и каждая строка уникальна по этим пяти полям, то исключение "Контрагент" из выборки может привести к тому, что СКД, при наличии группировки или оптимизации, будет группировать по "Период", "Филиал", "Название", "План", объединяя записи разных контрагентов в одну.

Решение первой части проблемы: Роль поля "Обязательное"

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

1. Перейдите к схеме компоновки данных. Откройте вашу схему СКД в конфигураторе.
2. Найдите закладку "Наборы данных". Выберите ваш набор данных, который используется в отчете.
3. Перейдите к закладке "Поля". Здесь перечислены все поля, доступные в вашем запросе.
4. Установите роль "Обязательное" для нужного поля. Для поля, которое вы не хотите убирать из запроса при скрытии (в нашем случае это "Контрагент"), установите флажок в колонке "Роль" напротив этого поля и выберите значение "Обязательное".

Что это дает: Установка роли "Обязательное" для поля Контрагент указывает СКД, что это поле должно присутствовать в итоговом запросе, независимо от того, отмечено ли оно для вывода в настройках отчета. Таким образом, даже если вы снимете флажок вывода для поля Контрагент в пользовательских настройках, СКД не удалит его из секции ВЫБРАТЬ итогового запроса. Это сохраняет исходную детализацию данных и предотвращает неявную группировку по оставшимся полям, поскольку все поля, необходимые для уникальной идентификации строки (включая Контрагент), будут по-прежнему участвовать в запросе. В результате, отчет будет выводиться "как нужно", просто без отображения колонки "Контрагент", но с сохранением исходного количества строк и детализации.

Как показывает опыт участников обсуждения, этот метод оказался рабочим для сохранения детализации при скрытии колонки: "это помогло".

Следующий шаг: Корректная агрегация, когда нужна фактическая группировка

Однако, что делать, если наша цель — действительно сгруппировать данные по оставшимся полям и выполнить агрегацию? Например, мы хотим убрать "Контрагент" и получить сумму какого-либо показателя (например, поля "Факт") по всем контрагентам, относящимся к одному "Периоду", "Филиалу", "Названию" и "Плану".

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

ВЫБРАТЬ
    ПланыФактыКоличествоКлиентов2.Период,
    ПланыФактыКоличествоКлиентов2.Филиал,
    ПланыФактыКоличествоКлиентов2.Название,
    ПланыФактыКоличествоКлиентов2.План,
    СУММА(ВЫБОР КОГДА ПланыФактыКоличествоКлиентов2.Стоимость >= ПланыФактыКоличествоКлиентов2.СуммаПродажиНаОдногоКлиента ТОГДА 1 ИНАЧЕ 0 КОНЕЦ) КАК Факт
ИЗ
    ПланыФактыКоличествоКлиентов2 КАК ПланыФактыКоличествоКлиентов2
СГРУППИРОВАТЬ ПО
    ПланыФактыКоличествоКлиентов2.Период,
    ПланыФактыКоличествоКлиентов2.Филиал,
    ПланыФактыКоличествоКлиентов2.Название,
    ПланыФактыКоличествоКлиентов2.План

В этом запросе мы явно пытаемся сгруппировать данные и просуммировать поле "Факт", которое рассчитывается как 1 или 0 в зависимости от условия. Однако, даже при таком запросе, можно столкнуться с тем, что поле "Факт" всегда выводит 1 вместо корректной суммы. Это происходит потому, что смешивание группировок в запросе и механизмов СКД может приводить к конфликтам и неочевидному поведению.

Фундаментальный принцип: Группировка в СКД vs. группировка в запросе

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

Если мы выполняем группировку с помощью оператора СГРУППИРОВАТЬ ПО непосредственно в запросе, то СКД получает уже пред-агрегированные данные. В таком случае, если мы затем пытаемся применить дополнительные настройки группировки или вычисления ресурсов в СКД, система будет работать уже с объединенными строками, что может исказить конечный результат, особенно для сложных вычисляемых полей. Например, если в запросе уже сгруппировали и получили Факт = 1 по какой-то логике, то СКД не сможет "увидеть" исходные детальные 0 и 1 для правильного суммирования.

Лучшая практика:

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

Агрегация вычисляемых полей и проблема "Факт" = 1

Когда вычисление поля Факт происходит с использованием конструкции ВЫБОР КОГДА ... ТОГДА 1 ИНАЧЕ 0 КОНЕЦ, а затем это поле агрегируется функцией СУММА, поведение может быть некорректным, если группировка происходит слишком рано или без необходимых полей.

Предположим, у нас есть исходные данные:

Если мы хотим просуммировать Факт по "Период", "Филиал", "Название", "План", то ожидаемый результат будет 1+0+1 = 2.

Однако, если группировка (явная в запросе или неявная через оптимизацию СКД) происходит таким образом, что для каждой сгруппированной строки поле Факт принимает значение 1, это означает, что исходные детальные 0 и 1 были потеряны до этапа суммирования. Например, если система каким-то образом сначала определяет, что "для данной группы есть хотя бы один факт", и на этом этапе присваивает 1, а затем это 1 суммируется, то результат будет некорректным.

Для получения корректной суммы:

1. Убедитесь, что ваш запрос возвращает поле Факт для каждой детальной строки в виде 0 или 1.
2. В СКД на закладке "Ресурсы" определите новый ресурс с выражением СУММА(Факт).
3. На закладке "Настройки" добавьте группировки по нужным вам полям (например, "Период", "Филиал", "Название", "План"). СКД будет автоматически суммировать ваш ресурс Факт по этим группировкам, используя все детальные значения 0 и 1, которые были получены из запроса.

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

Работа с временными таблицами в СКД

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

Если вы активно используете временные таблицы и сталкиваетесь с неочевидным поведением, рассмотрите следующие подходы:

1. Программное управление запросом: Если запрос очень сложный и СКД постоянно "оптимизирует" его не так, как вы ожидаете, возможно, стоит программно формировать запрос, передавая его в СКД уже в готовом виде, используя объект ТекстЗапроса в схеме компоновки данных.
2. Менеджер временных таблиц: При программном формировании отчета, если вы используете сложную структуру временных таблиц, можно использовать МенеджерВременныхТаблиц. Передача экземпляра МенеджераВременныхТаблиц в ПроцессорКомпоновкиДанных может обеспечить более предсказуемое поведение при работе с ВТ, так как СКД будет работать с уже существующими временными таблицами, а не пытаться их пересоздать или оптимизировать.

Комплексные рекомендации по работе с СКД для избежания подобных проблем

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

1. Максимальная детализация в запросе: Старайтесь, чтобы запрос набора данных в СКД возвращал максимально детализированные данные. Избегайте использования оператора СГРУППИРОВАТЬ ПО в запросе набора данных, если вы планируете группировать данные в СКД. Все агрегации и группировки лучше настраивать на закладках "Настройки" и "Ресурсы" СКД. Это дает СКД полный контроль над агрегацией и позволяет ей корректно обрабатывать пользовательские настройки и вариации отчетов.

   Скопировать код

   
   // Пример максимально детализированного запроса
   ВЫБРАТЬ
       ПланыФактыКоличествоКлиентов2.Период,
       ПланыФактыКоличествоКлиентов2.Филиал,
       ПланыФактыКоличествоКлиентов2.Название,
       ПланыФактыКоличествоКлиентов2.План,
       ПланыФактыКоличествоКлиентов2.Контрагент,
       ПланыФактыКоличествоКлиентов2.Стоимость,
       ПланыФактыКоличествоКлиентов2.СуммаПродажиНаОдногоКлиента,
       ВЫБОР КОГДА ПланыФактыКоличествоКлиентов2.Стоимость >= ПланыФактыКоличествоКлиентов2.СуммаПродажиНаОдногоКлиента ТОГДА 1 ИНАЧЕ 0 КОНЕЦ КАК ФактДетальный
   ИЗ
       ПланыФактыКоличествоКлиентов2 КАК ПланыФактыКоличествоКлиентов2
   

   В этом запросе мы получаем каждое поле, включая Контрагент, и вычисляем ФактДетальный для каждой строки. Затем в СКД мы уже можем управлять группировками и агрегациями:

   • На закладке "Ресурсы" для поля ФактДетальный устанавливаем агрегатную функцию СУММА(ФактДетальный).
   • На закладке "Настройки" устанавливаем группировки по Период, Филиал, Название, План.

2. Использование роли "Обязательное": Для полей, которые важны для сохранения детализации или корректной работы агрегатных функций, но не всегда выводятся в отчет, всегда устанавливайте роль "Обязательное" в свойствах поля на закладке "Поля" набора данных СКД. Это предотвратит их удаление из итогового запроса.

   Скопировать код

   
   // В конфигураторе, в СхемеКомпоновкиДанных:
   // НаборДанных1 -> Поля -> (Найти поле "Контрагент") -> Роль: Обязательное
   

3. Правильное определение вычисляемых полей и ресурсов: Если вычисляемое поле, такое как наш Факт (который равен 0 или 1), должно быть просуммировано, его следует настроить как ресурс на закладке "Ресурсы" в схеме компоновки данных. Выражение для ресурса должно быть СУММА(Факт), а само поле Факт должно быть корректно рассчитано на уровне детальных записей в запросе.

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

5. Управление группировками СКД: СКД позволяет гибко управлять группировками, включая вложенные группировки, а также выводить их в пользовательские настройки. Изучите возможности закладок "Настройки" и "Группировки" в схеме компоновки данных, чтобы настроить отчет именно так, как вам нужно, без необходимости "дорабатывать" запрос.

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