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

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

Постановка задачи и анализ проблемы

Представим структуру данных: Абонент, ГодДолга и СуммаДолга. В кросс-таблице мы хотим видеть годы слева направо (2016, 2017, 2018), а абонентов — отсортированными сначала по убыванию суммы за 2016 год, затем (для равных или пустых значений) по убыванию за 2017 год и так далее. Основная сложность заключается в том, что СКД не умеет «из коробки» обращаться к значению ресурса в конкретной колонке динамической группировки для целей сортировки строк.

Способ 1. Метод весовых коэффициентов в запросе

Проанализируем самый надежный метод, который работает на уровне математического расчета «веса» каждой строки. Суть заключается в создании синтетического поля, которое объединяет год и сумму в одно число, где ранние годы имеют больший «вес» за счет разрядности.

Рассмотрим пример реализации в тексте запроса:

ВЫБРАТЬ
    Абонент,
    ГодДолга,
    СуммаДолга,
    ВЫБОР
        КОГДА ГодДолга = 2016 ТОГДА СуммаДолга * 1000000000
        КОГДА ГодДолга = 2017 ТОГДА СуммаДолга * 1000000
        КОГДА ГодДолга = 2018 ТОГДА СуммаДолга * 1000
        ИНАЧЕ СуммаДолга
    КОНЕЦ КАК ВесСортировки
ИЗ
    РегистрНакопления.Задолженность

В данном случае, даже небольшой долг в 2016 году даст значительно большее значение ВесСортировки, чем огромный долг в 2018 году. Просуммировав это поле по абоненту, мы получим идеальный индекс для сортировки. В настройках СКД на закладке Ресурсы добавим СУММА(ВесСортировки), а в настройках группировки Абонент укажем сортировку по этому ресурсу по убыванию.

Способ 2. Использование функции СКД ВычислитьВыражение

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

ВычислитьВыражение("Сумма(СуммаДолга)", "ГодДолга", , "Первая", "Возрастание")

Разберем, что это дает: функция вычисляет агрегат суммы долга в контексте первой группировки по году (самого раннего года). Однако для полноценной последовательной сортировки (2016 -> 2017 -> 2018) этого может быть недостаточно, если долги распределены неравномерно. В таком случае рекомендуется использовать более сложную конкатенацию строк.

Способ 3. Сортировка через строковый индекс (Конкатенация)

Рассмотрим ситуацию, когда периодов много. Мы можем создать вычисляемое поле, которое превращает суммы в строки фиксированной длины и объединяет их. Например, сумма 500 преобразуется в "0000000500".

В ресурсах для Абонента создадим выражение, которое собирает такие значения. Это позволит реализовать логику «Упорядочить по Сумма2016, Сумма2017...» без ограничения количества колонок. Важно помнить, что СКД позволяет сортировать по ресурсам, и если ресурс возвращает строку вида "2016_0000500_2017_0000100", то сортировка по убыванию поставит наверх самых «старых» должников.

Способ 4. Транспонирование в запросе (Фиксированные колонки)

Если список анализируемых периодов (лет) заранее известен и ограничен, проанализируем метод «разворота» данных прямо в запросе. Это сделает настройку сортировки в интерфейсе СКД максимально прозрачной.

Пример текста запроса:

ВЫБРАТЬ
    Абонент,
    СУММА(ВЫБОР КОГДА ГодДолга = 2016 ТОГДА СуммаДолга ИНАЧЕ 0 КОНЕЦ) КАК Долг2016,
    СУММА(ВЫБОР КОГДА ГодДолга = 2017 ТОГДА СуммаДолга ИНАЧЕ 0 КОНЕЦ) КАК Долг2017,
    СУММА(ВЫБОР КОГДА ГодДолга = 2018 ТОГДА СуммаДолга ИНАЧЕ 0 КОНЕЦ) КАК Долг2018
ИЗ
    РегистрНакопления.Задолженность КАК Задолженность
СГРУППИРОВАТЬ ПО
    Абонент

Теперь в настройках СКД для группировки Абонент на закладке Сортировка мы просто добавляем поля в нужном порядке:

1. Долг2016 — По убыванию
2. Долг2017 — По убыванию
3. Долг2018 — По убыванию

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

Настройка интерфейса СКД и типичные ошибки

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

• Отключение автосортировки: У группировки Абонент крайне желательно снять флаг Автосортировка. Если он включен, СКД может попытаться сначала отсортировать абонентов по их наименованию (представлению), что перекроет наши настройки.
• Обработка значений NULL: Если у абонента нет долга в 2016 году, сумма будет NULL или 0. При сортировке по убыванию такие абоненты окажутся внизу, что нам и нужно. Однако, если используется сложная математика весов, всегда используйте функцию ЕСТЬNULL(Поле, 0) в запросе.
• Выражение упорядочивания: На закладке Наборы данных для поля Абонент можно указать «Выражение упорядочивания». В некоторых случаях полезно прописать туда специфическую логику, например, Минимум(ГодДолга), чтобы сгруппировать абонентов по дате возникновения первой задолженности.

Резюме по шагам

Подведем итог, как реализовать наиболее гибкую сортировку:

1. Определим критерий важности данных (старый год — высший приоритет).
2. Создадим вспомогательный ресурс (через ВесСортировки или ВычислитьВыражение).
3. Добавим этот ресурс в настройки компоновки.
4. В настройках группировки строк (Абонентов) установим сортировку по этому ресурсу.
5. Отключим автосортировку для группировки, чтобы приоритет наших настроек был максимальным.

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