При разработке отчетов в 1С:Предприятие, особенно касающихся план-фактного анализа, разработчики часто сталкиваются с ситуацией, когда на уровне детальных записей данные выглядят корректно, но стоит свернуть группировку или включить иерархию — цифры «взлетают» в несколько раз. Например, плановые показатели умножаются на количество фактов отгрузки.
В этой статье мы подробно разберем, почему возникает такая ошибка, проведем анализ отчета на наличие ошибок в коде, который привел к неверным расчетам АКБ (Активной Клиентской Базы), и рассмотрим правильные методы решения этой задачи с помощью языка запросов и базовых настроек Системы Компоновки Данных (СКД).
Давайте рассмотрим типичную ситуацию. Нам необходимо построить отчет, который показывает выполнение плана по АКБ и факт посещения торговых точек. Логика проста: если посещение или продажа были — ставим 1, если нет — 0.
Разработчик пишет запрос, выводит данные в отчет. Проверяет по конкретной торговой точке: План = 1, Факт = 1. Все отлично. Но как только пользователь настраивает группировку по Менеджеру или Торговой сети, вместо ожидаемой суммы планов (например, 10 точек), отчет показывает цифру 50 или 100. Подсчеты ломаются, выводятся данные, превышающие реальные в разы.
Давайте проанализируем код запроса, который вызывает подобное поведение, и найдем в нем логическую ошибку.
В нашем примере данные собираются из нескольких источников: заказы торговых представителей, планы и фактические отгрузки. Рассмотрим фрагмент кода, отвечающий за выборку факта продаж (АКБ) через РегистрНакопления:
ВЫБРАТЬ РАЗЛИЧНЫЕ
ГМС_ОтгрузкаТМЦ.Контрагент КАК Контрагент,
ГМС_ОтгрузкаТМЦ.ТорговыйОбъект КАК ТорговыйОбъект,
ГМС_ОтгрузкаТМЦ.Менеджер КАК Менеджер,
ГМС_ОтгрузкаТМЦ.Регистратор КАК Регистратор
ПОМЕСТИТЬ ВТ_ФактАКБ
ИЗ
РегистрНакопления.ГМС_ОтгрузкаТМЦ КАК ГМС_ОтгрузкаТМЦ
ГДЕ
ГМС_ОтгрузкаТМЦ.Период МЕЖДУ &ДатаНач И &ДатаКон
И ГМС_ОтгрузкаТМЦ.Регистратор ССЫЛКА Документ.РасходнаяНакладнаяТМЦ
;
Обратите внимание на поле ГМС_ОтгрузкаТМЦ.Регистратор. В таблицу ВТ_ФактАКБ попадают записи с детализацией до конкретного документа отгрузки (Расходной накладной).
Далее происходит соединение этой таблицы с таблицей планов и справочником торговых точек (таблица ВТ_Данные):
...
ИЗ
ВТ_Данные КАК ВТ_Данные
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ПланАКБ КАК ВТ_ПланАКБ
ПО ...
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ФактАКБ КАК ВТ_ФактАКБ
ПО ВТ_Данные.СсылкаКонтрагент = ВТ_ФактАКБ.Контрагент
И ВТ_Данные.Ссылка = ВТ_ФактАКБ.ТорговыйОбъект
И ВТ_Данные.СсылкаМенеджер = ВТ_ФактАКБ.Менеджер
Здесь и кроется корень зла. Это классическая проблема соединения «Один-ко-Многим» (One-to-Many), которая не учтена при дальнейшей агрегации.
ВТ_Данные (и ВТ_ПланАКБ) одна строка соответствует одной торговой точке за период.ВТ_ФактАКБ из-за наличия поля Регистратор одной торговой точке может соответствовать множество строк (если за месяц было 5 отгрузок, будет 5 строк).ВТ_Данные размножается столько раз, сколько документов отгрузки нашлось в ВТ_ФактАКБ.Результат: Если у клиента план по АКБ равен 1, а отгрузок было 5, то в результирующей выборке мы получим 5 строк. В каждой из них поле ПланПоАКБ будет равно 1.
В детальных записях мы видим просто список документов. Но когда СКД начинает считать итоги (Сумму), она складывает все эти единицы: 1 + 1 + 1 + 1 + 1 = 5. План завышен в 5 раз.
Самый надежный способ исправить ситуацию — привести таблицы к единой детализации (гранулярности) до момента их соединения. Нам не нужен конкретный документ («Регистратор») в итоговой таблице для сравнения «План-Факт». Нам важен сам факт наличия продаж по точке.
Необходимо переписать запрос формирования ВТ_ФактАКБ, используя группировку данных в запросе:
ВЫБРАТЬ
ГМС_ОтгрузкаТМЦ.Контрагент КАК Контрагент,
ГМС_ОтгрузкаТМЦ.ТорговыйОбъект КАК ТорговыйОбъект,
ГМС_ОтгрузкаТМЦ.Менеджер КАК Менеджер,
1 КАК ФактАКБ
// Или КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ГМС_ОтгрузкаТМЦ.Регистратор) если нужно кол-во заказов
ПОМЕСТИТЬ ВТ_ФактАКБ
ИЗ
РегистрНакопления.ГМС_ОтгрузкаТМЦ КАК ГМС_ОтгрузкаТМЦ
ГДЕ
ГМС_ОтгрузкаТМЦ.Период МЕЖДУ &ДатаНач И &ДатаКон
И ГМС_ОтгрузкаТМЦ.Регистратор ССЫЛКА Документ.РасходнаяНакладнаяТМЦ
СГРУППИРОВАТЬ ПО
ГМС_ОтгрузкаТМЦ.Контрагент,
ГМС_ОтгрузкаТМЦ.ТорговыйОбъект,
ГМС_ОтгрузкаТМЦ.Менеджер
;
Теперь в таблице ВТ_ФактАКБ каждому уникальному сочетанию Контрагента, Торговой точки и Менеджера будет соответствовать ровно одна строка. При соединении с таблицей планов дублирования строк не произойдет, и итоги будут рассчитаны корректно.
Если по каким-то причинам нам необходимо оставить детализацию до регистратора в итоговой выборке (например, пользователь хочет расшифровывать данные до накладной), проблему с дублированием плановых показателей можно решить настройкой ресурсов СКД.
В Системе Компоновки Данных на вкладке «Ресурсы» мы определяем, как рассчитывать итоги по полям. Для тех случаев, когда отчет создается программно, можно применить СКДБилдер для упрощения работы с объектной моделью СКД.
Обычно для поля ПланПоАКБ используется функция Сумма(ПланПоАКБ). Именно она дает ошибку при дублях. Чтобы избежать этого, можно изменить формулу расчета ресурсов:
Максимум(ПланПоАКБ), но это сработает только если план одинаков для всех записей в группе, что не всегда верно для иерархии.Однако более правильным подходом в СКД для ситуаций «Один-ко-Многим» является использование не одного набора данных, а механизма связи наборов данных. Если данные поступают из внешних источников, стоит изучить методы оптимизации отчетов с набором данных - объект.
Мы создаем два отдельных набора данных:
Затем на вкладке «Связи наборов данных» настраиваем связь между ними по Контрагенту/Торговой точке. В этом случае СКД самостоятельно понимает структуру данных и корректно рассчитывает ресурсы, не задваивая показатели из родительской таблицы (Планов) при выводе дочерних записей (Фактов).
В исходном коде автора мы видим частое использование конструкции ВЫБРАТЬ РАЗЛИЧНЫЕ (SELECT DISTINCT). Рассмотрим, как на это влияет оптимизация производительности запросов.
Использование РАЗЛИЧНЫЕ заставляет СУБД сначала выбрать все записи (включая дубли), а затем выполнить операцию сортировки и удаления дубликатов. На больших объемах данных это может быть ресурсоемкой операцией. Если же вам требуется не просто убрать дубли, а, например, собрать значения колонки из нескольких строк в одну, потребуются иные механизмы группировки.
Гораздо эффективнее использовать конструкцию СГРУППИРОВАТЬ ПО (GROUP BY) с агрегатными функциями. Это не только делает запрос более предсказуемым для планировщика СУБД, но и часто работает быстрее. Кроме того, группировка позволяет нам явно управлять тем, какие данные мы "схлопываем", и применять агрегатные функции (Сумма, Количество, Максимум) к остальным полям.
В обсуждении также был затронут методологический вопрос: что считать АКБ? Давайте уточним терминологию, так как это влияет на структуру запроса.
АКБ (Активная Клиентская База) — это количество уникальных торговых точек, совершивших покупку (или сделавших заказ) в отчетном периоде.
Поэтому в запросе автора попытка вычислить:
ВЫБОР
КОГДА ЕСТЬNULL(ВТ_ФактАКБ.Регистратор, 0) = 0
ТОГДА 0
ИНАЧЕ 1
КОНЕЦ КАК ФактАКБ
является верной по сути, но, как мы выяснили, должна применяться к уже сгруппированным данным. Если вы делаете это в ресурсах СКД, то формула для ресурса АКБ должна выглядеть как Количество(Различные Контрагент) или Количество(Различные ТорговыйОбъект), в зависимости от того, что является единицей учета.
Проблема некорректных итогов при использовании иерархии почти всегда связана с декартовым произведением (размножением строк) при соединении таблиц. Для расширения возможностей аналитики можно использовать инструменты пользовательской СКД, которые позволяют более гибко настраивать отчеты на стороне клиента.
Краткий чек-лист для проверки вашего отчета в СКД:
ЛЕВОЕ СОЕДИНЕНИЕ (или ПОЛНОЕ).Исправив запрос группировкой таблицы фактов, мы получаем корректные цифры как в детальных записях, так и на уровне итогов по менеджерам и торговым сетям.