Как правильно рассчитать итоги для полей с Количество(Различные...) в отчетах СКД 1С?

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

Суть проблемы: невозможность прямого суммирования агрегатных функций

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

1. Сколько уникальных дней работал каждый менеджер.
2. Какое общее количество накладных сделал каждый менеджер.
3. Какую сумму премии получил каждый менеджер (где премия зависит от количества отработанных дней).
4. Самое главное — нам нужен общий итог по всем менеджерам для количества отработанных дней и общей суммы премии.

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

Посмотрим на пример желаемой таблички:

Пример детальных данных:
Менеджер 1   35 накладных   01.11
Менеджер 2   42 накладных   01.11
Менеджер 1   35 накладных   02.11
Менеджер 2   42 накладных   02.11
Менеджер 3   35 накладных   03.11
Менеджер 2   42 накладных   03.11

Пример итогов по менеджерам:
Менеджер 1   2 дня отработано   Премия (2 дня * Коэф.)
Менеджер 2   3 дня отработано   Премия (3 дня * Коэф.)
Менеджер 3   1 день отработано   Премия (1 день * Коэф.)

И в конце нам нужен общий итог по всем менеджерам: 6 дней отработано, общая сумма премии.

Решение: функция ВычислитьВыражениеСГруппировкойМассив

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

Принцип работы и синтаксис ВычислитьВыражениеСГруппировкойМассив

Функция ВычислитьВыражениеСГруппировкойМассив выполняет следующие действия:

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

Синтаксис функции:

ВычислитьВыражениеСГруппировкойМассив(Выражение, ВыраженияПолейГруппировки, ОтборЗаписей, ОтборГруппировок)

• Выражение: Это строка, содержащая выражение, которое мы хотим вычислить. Например, "Количество(Различные ДатаКалендаря)". Важно помнить, что это выражение будет вычисляться для каждой записи в контексте внутренней группировки.
• ВыраженияПолейГруппировки: Строка с перечислением полей, по которым будет выполняться группировка для вычисления Выражения. Например, "Менеджер". Это означает, что Выражение будет вычислено для каждого уникального значения поля Менеджер.
• ОтборЗаписей: Необязательный параметр. Строка, содержащая условия отбора, применяемые к детальным записям до агрегирования.
• ОтборГруппировок: Необязательный параметр. Строка, содержащая условия отбора, применяемые к уже сгруппированным данным.

Пошаговая реализация в СКД

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

Шаг 1: Подготовка набора данных

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

ВЫБРАТЬ
    Реализации.Менеджер КАК Менеджер,
    Реализации.Дата КАК ДатаКалендаря,
    Реализации.КоличествоНакладных КАК КоличествоНакладных,
    Реализации.СуммаРеализации КАК СуммаРеализации
ИЗ
    Документ.РеализацияТоваровУслуг КАК Реализации
ГДЕ
    Реализации.Дата МЕЖДУ &ДатаНачала И &ДатаОкончания

Мы должны убедиться, что у нас есть поля Менеджер и ДатаКалендаря в выбранных полях набора данных.

Шаг 2: Создание вычисляемого поля для количества уникальных дней по менеджеру

1. Откройте настройки СКД (меню "Отчеты" -> "Настройки СКД").
2. Перейдите на закладку "Вычисляемые поля".
3. Создайте новое вычисляемое поле, например, КоличествоДнейМенеджера.
4. В поле "Выражение" для этого поля укажите: Количество(Различные ДатаКалендаря).
5. Это поле будет использоваться для отображения количества уникальных дней для каждой строки группировки по менеджеру.

Шаг 3: Настройка ресурсов для подсчета общих итогов

Теперь мы переходим к самому важному — расчету общих итогов. Для этого мы будем использовать закладку "Ресурсы" в настройках СКД.

1. Перейдите на закладку "Ресурсы".
2. Для общего количества накладных:
   • Добавьте ресурс Сумма(КоличествоНакладных). Это стандартный ресурс, который суммирует количество накладных.
   • В поле "Рассчитывать по..." выберите необходимые группировки (например, "ОбщийИтог" и "Менеджер").
3. Для общего количества отработанных дней (общий итог по всем менеджерам):
   • Создайте новый ресурс, назовите его, например, ОбщееКоличествоОтработанныхДней.
   • В поле "Выражение" для этого ресурса укажите следующее:
     Скопировать код

     
     Сумма(ВычислитьВыражениеСГруппировкойМассив("Количество(Различные ДатаКалендаря)", "Менеджер"))
     

   • Разберем это выражение:
     • Внутреннее выражение "Количество(Различные ДатаКалендаря)" будет вычислено для каждого уникального значения поля Менеджер.
     • Второй параметр "Менеджер" говорит СКД, чтобы она сгруппировала данные по Менеджеру и для каждой группы посчитала количество уникальных дат.
     • Функция ВычислитьВыражениеСГруппировкойМассив вернет массив этих значений (например, [2, 3, 1] для наших менеджеров).
     • Внешняя функция Сумма(...) затем просуммирует все элементы этого массива, давая нам общий итог по количеству отработанных дней (2 + 3 + 1 = 6).
   • В поле "Рассчитывать по..." для этого ресурса обычно выбирается ОбщийИтог, чтобы этот итог выводился в конце отчета, а не для каждой промежуточной группировки (если таковые имеются).
4. Для расчета общей премии:
   • Если премия рассчитывается как КоличествоОтработанныхДней * КоэффициентПремии, и нам нужна сумма премии по всем менеджерам, мы используем аналогичный подход.
   • Создайте новый ресурс, например, ОбщаяПремия.
   • В поле "Выражение" для этого ресурса укажите:
     Скопировать код

     
     Сумма(ВычислитьВыражениеСГруппировкойМассив("Количество(Различные ДатаКалендаря) * КоэффициентПремии", "Менеджер"))
     

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

Пример настройки группировок

Для того чтобы отчет выглядел так, как мы хотим (сначала детали по менеджеру, затем общие итоги), нам нужно правильно настроить группировки:

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

Важные моменты и дополнительные советы

• Контекст вычисления: Всегда помните, что строковое выражение внутри ВычислитьВыражениеСГруппировкойМассив вычисляется в контексте той группировки, которая указана во втором параметре. Убедитесь, что все поля, используемые в выражении (например, ДатаКалендаря, КоэффициентПремии), доступны в этом контексте.
• Производительность: Хотя ВычислитьВыражениеСГруппировкойМассив является мощным инструментом, его активное использование на очень больших объемах данных может сказаться на производительности отчета. В большинстве случаев для стандартных отчетов это не будет проблемой.
• Гибкость: Эта функция не ограничивается только суммированием "количество различных". Вы можете использовать её для любых сложных расчетов, требующих агрегирования агрегатов. Например, вы можете посчитать среднее значение от агрегатного поля и т.д.

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