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

← На главную

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

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

Эта задача очень похожа на получение курса валюты на любую дату: нам всегда нужен последний курс на эту дату или ранее. Аналогично, нам нужен последний менеджер, закрепленный за клиентом, на дату продажи или ранее.

Особенности задачи и ограничения 1С:Предприятие

Прежде чем перейти к решениям, давайте выясним, почему эта задача требует особого подхода:

Периодический регистр сведений: Информация о закреплении менеджеров хранится с привязкой к дате. Нам нужен "срез последних" данных на каждую дату продажи.
Ограничения SQL в 1С: В отличие от "чистых" SQL-баз данных (MS SQL, Oracle, PostgreSQL), язык запросов 1С не поддерживает оконные функции, рекурсивные CTE и некоторые другие мощные конструкции. Это часто вынуждает нас использовать временные таблицы для поэтапной обработки данных.
Динамичность данных: Менеджеры могут меняться нечасто, но эти изменения могут происходить в любой момент, а также задним числом, что делает запись менеджера непосредственно в документ продажи не всегда достаточным или корректным решением.

Метод 1: Использование виртуальной таблицы «СрезПоследних»

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

Мы рассмотрим принцип работы и как его применить к нашей задаче:

Принцип работы СрезПоследних:
Виртуальная таблица СрезПоследних регистра сведений возвращает одну запись для каждой комбинации значений измерений, которая является последней записью по периоду, не превышающему заданную дату.
Синтаксис РегистрСведений.ИмяРегистра.СрезПоследних(&ДатаСреза, &УсловияОтбора)
Здесь &ДатаСреза — это дата, на которую мы хотим получить срез, а &УсловияОтбора — дополнительные условия для отбора записей, например, по партнеру.

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


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

Мы используем ЛЕВОЕ СОЕДИНЕНИЕ, чтобы учесть продажи по клиентам, у которых, возможно, не было закреплено менеджера в истории. Условие Партнер = Продажи.АналитикаУчетаПоПартнерам.Партнер внутри СрезаПоследних обеспечивает выбор менеджера только для текущего партнера продажи.

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

Метод 2: Построение таблицы периодов действия менеджеров и сложный запрос

Иногда прямое использование СрезаПоследних может быть неоптимальным или сложным для чтения при очень сложных условиях. Альтернативный подход, часто используемый в 1С из-за ограничений языка запросов, — это поэтапное построение временных таблиц, имитирующее поиск последней записи или построение периодов действия. Этот метод был представлен и доработан в ходе обсуждения на форуме.

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

Пошаговый разбор запроса

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

Временная таблица Партнеры (можно оптимизировать):

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


ВЫБРАТЬ РАЗРЕШЕННЫЕ
    Продажи.АналитикаУчетаПоПартнерам.Партнер КАК Партнер,
    СУММА(Продажи.СуммаВыручкиРеглОборот - Продажи.СтоимостьРеглОборот - Продажи.ДопРасходыРеглОборот - Продажи.ТрудозатратыРеглОборот - Продажи.ПостатейныеПостоянныеРеглОборот - Продажи.ПостатейныеПеременныеРеглОборот) КАК Прибыль
ПОМЕСТИТЬ Партнеры
ИЗ
    РегистрНакопления.ВыручкаИСебестоимостьПродаж.Обороты(&НачалоОтчета, &КонецОтчета, , АналитикаУчетаПоПартнерам.Партнер <> ЗНАЧЕНИЕ(Справочник.Партнеры.НашеПредприятие) {(АналитикаУчетаПоПартнерам.Партнер.ОсновнойМенеджер = &Менеджер), (АналитикаУчетаПоПартнерам.Партнер.ОсновнойМенеджер.Подразделение = &Подразделение)}) КАК Продажи
СГРУППИРОВАТЬ ПО
    Продажи.АналитикаУчетаПоПартнерам.Партнер
ИНДЕКСИРОВАТЬ ПО
    Партнер

Комментарий к оптимизации: Как было отмечено на форуме, эта временная таблица используется в дальнейшем только для фильтрации. Мы можем избежать ее создания, если получим список уникальных партнеров "на лету" из следующей временной таблицы ДниПродаж, используя ВЫБРАТЬ РАЗЛИЧНЫЕ Партнер ИЗ ДниПродаж.

Временная таблица ДниПродаж:

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


ВЫБРАТЬ РАЗРЕШЕННЫЕ
    Продажи.Период КАК Период,
    Продажи.АналитикаУчетаПоПартнерам.Партнер КАК Партнер,
    СУММА(Продажи.СуммаВыручкиРеглОборот - Продажи.СтоимостьРеглОборот - Продажи.ДопРасходыРеглОборот - Продажи.ТрудозатратыРеглОборот - Продажи.ПостатейныеПостоянныеРеглОборот - Продажи.ПостатейныеПеременныеРеглОборот) КАК Прибыль
ПОМЕСТИТЬ ДниПродаж
ИЗ
    РегистрНакопления.ВыручкаИСебестоимостьПродаж.Обороты(&НачалоОтчета, &КонецОтчета, День, АналитикаУчетаПоПартнерам.Партнер <> ЗНАЧЕНИЕ(Справочник.Партнеры.НашеПредприятие) {(АналитикаУчетаПоПартнерам.Партнер.ОсновнойМенеджер = &Менеджер), (АналитикаУчетаПоПартнерам.Партнер.ОсновнойМенеджер.Подразделение = &Подразделение)}) КАК Продажи
СГРУППИРОВАТЬ ПО
    Продажи.Период,
    Продажи.АналитикаУчетаПоПартнерам.Партнер
ИНДЕКСИРОВАТЬ ПО
    Партнер

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

Временная таблица МенеджерыПоДням:

Эта таблица является ключевой для агрегирования всех точек изменения менеджера по каждому клиенту, которые релевантны для нашего отчетного периода. Она строится в два этапа с использованием оператора ОБЪЕДИНИТЬ ВСЕ:
1. Начальное состояние: С помощью РегистрСведений.ИсторияМенеджеров.СрезПервых(&НачалоОтчета, ...) мы получаем менеджера, который был закреплен за каждым партнером на начало отчетного периода. Это важно, чтобы учесть клиентов, у которых менеджер был назначен до начала нашего отчета и с тех пор не менялся.
2. Изменения внутри периода: Далее мы добавляем все записи из регистра ИсторияМенеджеров, которые произошли непосредственно в отчетном периоде (МЕЖДУ НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(&НачалоОтчета, ДЕНЬ, 1), ДЕНЬ) И &КонецОтчета).
Обе части отбираются только для тех партнеров, по которым у нас были продажи (используется подзапрос к временной таблице Партнеры или, как мы обсудили выше, к ДниПродаж).
```
ВЫБРАТЬ РАЗРЕШЕННЫЕ
    НАЧАЛОПЕРИОДА(&НачалоОтчета, ДЕНЬ) КАК Период,
    ИсторияМенеджеровСрезПервых.Партнер КАК Партнер,
    ИсторияМенеджеровСрезПервых.Менеджер КАК Менеджер
ПОМЕСТИТЬ МенеджерыПоДням
ИЗ
    РегистрСведений.ИсторияМенеджеров.СрезПервых(
        &НачалоОтчета,
        Партнер В
            (ВЫБРАТЬ
                Партнеры.Партнер
            ИЗ
                Партнеры)) КАК ИсторияМенеджеровСрезПервых
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
    ИсторияМенеджеров.Период,
    ИсторияМенеджеров.Партнер,
    ИсторияМенеджеров.Менеджер
ИЗ
    РегистрСведений.ИсторияМенеджеров КАК ИсторияМенеджеров
ГДЕ
    ИсторияМенеджеров.Период МЕЖДУ НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(&НачалоОтчета, ДЕНЬ, 1), ДЕНЬ) И &КонецОтчета
    И ИсторияМенеджеров.Партнер В
            (ВЫБРАТЬ
                Партнеры.Партнер
            ИЗ
                Партнеры)
ИНДЕКСИРОВАТЬ ПО
    Партнер
```
Важная поправка: На форуме было замечание (7) Ошибка: СрезПоследних, а не СрезПервых. Это замечание касалось *концепции* получения актуального менеджера. Однако в данном конкретном коде СрезПервых используется для получения *начального состояния* на дату начала отчета, что является корректным способом включить менеджеров, действующих до этого периода. Далее эти данные объединяются со всеми последующими изменениями, формируя полный список "точек изменений".
Временная таблица ХешМен:

Это, пожалуй, самый важный шаг. Здесь мы для каждого дня продажи (из таблицы ДниПродаж) и каждого партнера находим максимальную дату изменения менеджера, которая произошла до или в этот день. Таким образом, мы получаем точную дату, на которую нужно смотреть в регистре ИсторияМенеджеров, чтобы узнать, кто был актуальным менеджером.
```
ВЫБРАТЬ
    ДниПродаж.Период КАК Период,
    ДниПродаж.Партнер КАК Партнер,
    МАКСИМУМ(МенеджерыПоДням.Период) КАК ДатаМена
ПОМЕСТИТЬ ХешМен
ИЗ
    ДниПродаж КАК ДниПродаж
        ЛЕВОЕ СОЕДИНЕНИЕ МенеджерыПоДням КАК МенеджерыПоДням
        ПО ДниПродаж.Период >= МенеджерыПоДням.Период
            И ДниПродаж.Партнер = МенеджерыПоДням.Партнер
СГРУППИРОВАТЬ ПО
    ДниПродаж.Период,
    ДниПродаж.Партнер
ИНДЕКСИРОВАТЬ ПО
    Партнер
```
Обратите внимание на условие соединения ДниПродаж.Период >= МенеджерыПоДням.Период. Именно оно в сочетании с функцией МАКСИМУМ позволяет найти последнюю по времени запись об изменении менеджера для данного партнера, которая произошла до или в день продажи. Если для какого-то дня продажи не нашлось изменений менеджера (из-за ЛЕВОЕ СОЕДИНЕНИЕ), то ДатаМена будет NULL, и такие продажи не будут привязаны к менеджеру.

Финальная временная таблица Продажи (результирующий отчет):

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

Регистр накопления ВыручкаИСебестоимостьПродаж.Обороты соединяется с ХешМен по Период и Партнер.
Далее ХешМен соединяется с МенеджерыПоДням по Партнер и найденной дате изменения менеджера (ХешМен.ДатаМена = МенеджерыПоДням.Период).

Таким образом, для каждой записи продаж мы получаем именно того менеджера, который был закреплен за клиентом на дату этой продажи. Результат группируется по менеджеру для получения общей прибыли.


ВЫБРАТЬ РАЗРЕШЕННЫЕ
    МенеджерыПоДням.Менеджер КАК Менеджер,
    СУММА(Продажи.СуммаВыручкиРеглОборот - Продажи.СтоимостьРеглОборот - Продажи.ДопРасходыРеглОборот - Продажи.ТрудозатратыРеглОборот - Продажи.ПостатейныеПостоянныеРеглОборот - Продажи.ПостатейныеПеременныеРеглОборот) КАК Прибыль
ПОМЕСТИТЬ Продажи
ИЗ
    РегистрНакопления.ВыручкаИСебестоимостьПродаж.Обороты(&НачалоОтчета, &КонецОтчета, День, АналитикаУчетаПоПартнерам.Партнер <> ЗНАЧЕНИЕ(Справочник.Партнеры.НашеПредприятие) {(АналитикаУчетаПоПартнерам.Партнер.ОсновнойМенеджер = &Менеджер), (АналитикаУчетаПоПартнерам.Партнер.ОсновнойМенеджер.Подразделение = &Подразделение)}) КАК Продажи
        ЛЕВОЕ СОЕДИНЕНИЕ ХешМен КАК ХешМен
        ПО Продажи.Период = ХешМен.Период
            И Продажи.АналитикаУчетаПоПартнерам.Партнер = ХешМен.Партнер
        ЛЕВОЕ СОЕДИНЕНИЕ МенеджерыПоДням КАК МенеджерыПоДням
        ПО ХешМен.Партнер = МенеджерыПоДням.Партнер
            И ХешМен.ДатаМена = МенеджерыПоДням.Период
СГРУППИРОВАТЬ ПО
    МенеджерыПоДням.Менеджер

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

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

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

Альтернативные подходы (для сравнения)

В ходе обсуждения на форуме часто поднимаются вопросы о других способах решения, давайте кратко их рассмотрим:

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

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