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