Как в СКД разделить колонки по годам, рассчитать нарастающий итог и получить остатки из оборотного регистра

← На главную

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

В этой статье мы подробно разберем, как решить три взаимосвязанные технические проблемы, с которыми часто сталкиваются разработчики при проектировании отчетов в СКД:

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

Давайте вместе разберем каждый шаг реализации этих задач.

Задача 1: Динамическое разделение колонок на выбранный год и предыдущий

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

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

Рассмотрим, как настроить такую схему по шагам:

Шаг 1. Подготовка запроса. Нам необходимо передать в запрос параметр выбранного года (например, &ВыбранныйГод) и сопоставить дату каждой записи с этим параметром. Напишем в запросе вычисляемое поле, которое будет текстовым идентификатором категории года:


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

Шаг 2. Настройка структуры отчета. Перейдем на вкладку «Настройки» в схеме компоновки данных. Создадим структуру типа «Таблица»:

В «Строки» таблицы добавим группировку по полю Номенклатура.
В «Колонки» таблицы добавим группировку по созданному нами полю ТипГода.
Внутрь группировки ТипГода (как подчиненную группировку) добавим группировку по полю Период с детализацией, например, по месяцам (для этого в роли поля периода запроса нужно указать соответствующий тип или использовать функцию НАЧАЛОПЕРИОДА(Период, МЕСЯЦ)).

Шаг 3. Вывод ресурсов. На вкладке «Выбранные поля» для таблицы в целом укажем наш ресурс — СуммаРеализации. Теперь СКД автоматически сгруппирует данные: по горизонтали верхним уровнем будут идти колонки "Выбранный год" и "Предыдущий год", внутри них распределятся соответствующие месяцы, а на пересечении строк и колонок выведутся суммы.

Задача 2: Расчет нарастающего итога и обращение к предыдущим строкам

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

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

Выясним, как правильно настроить это поле в ресурсах отчета:

Шаг 1. Создание вычисляемого поля. Перейдем на вкладку «Вычисляемые поля». Добавим новое поле со следующими параметрами:

Путь к данным: НакопленныйИтог
Выражение: оставляем абсолютно пустым (это крайне важно, так как расчет должен производиться на уровне ресурсов по уже сгруппированным показателям).
Заголовок: Нарастающий итог

Шаг 2. Определение поля как ресурса. Перейдем на вкладку «Ресурсы». Добавим наше поле НакопленныйИтог в список ресурсов. В колонке «Выражение» напишем специальную формулу:

ВычислитьВыражение("Сумма(СуммаРеализации)", , , "Первая", "Текущая")

Давайте подробно разберем, как работает эта функция и что означают ее параметры:

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

Нюанс для кросс-таблиц (расчет по горизонтали): Если нарастающий итог нужно вычислять не сверху вниз по строкам, а слева направо по колонкам периодов (например, по месяцам в кросс-таблице), то синтаксис функции потребует явного указания имени группировки колонок во втором параметре:

ВычислитьВыражение("Сумма(СуммаРеализации)", "Период", , "Первая", "Текущая")

Как получить значение строго предыдущей строки? Если перед вами стоит задача рассчитать разницу между текущим и прошлым периодом (прирост), вам потребуется значение строго предыдущей строки. Для этого используйте следующую конструкцию в выражении ресурса:

ВычислитьВыражение("Сумма(СуммаРеализации)", , , "Предыдущая", "Предыдущая")

Задача 3: Получение остатков из оборотного регистра накопления

Бывают ситуации, когда в конфигурации есть только оборотный регистр (например, регистр продаж или оборотов бюджетов), а перед разработчиком ставят задачу вывести отчет с остатками. Поскольку регистр оборотный, у него нет системного понятия «остаток» и нет готовой виртуальной таблицы .ОстаткиИОбороты.

Любой остаток на конец периода для оборотного регистра — это сумма всех его движений (оборотов) от самого начала ведения учета в базе данных до конечной даты этого периода. Рассмотрим два способа решения этой задачи во внешнем отчете.

Способ 1. Расчет силами компоновщика данных (рекомендуемый)

Самый быстрый и производительный способ — не перегружать СУБД сложными запросами с соединениями, а переложить расчет остатка на саму СКД, используя уже изученную нами функцию нарастающего итога.

Разберем алгоритм реализации:

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

Способ 2. Получение остатков через левое соединение в запросе

Если по какой-то причине вам необходимо получить готовые остатки непосредственно на уровне СУБД (например, если эти остатки должны дальше участвовать в сложных соединениях с другими таблицами внутри одного запроса), придется прибегнуть к классическому приему левого соединения таблицы самой с собой по условию неравенства периодов.

Проанализируем структуру такого запроса:


// Шаг 1: Выбираем обороты во временную таблицу
ВЫБРАТЬ
    ПродажиОбороты.Номенклатура КАК Номенклатура,
    ПродажиОбороты.Период КАК Период,
    ПродажиОбороты.КоличествоОборот КАК Оборот
ПОМЕСТИТЬ ВТ_Обороты
ИЗ
    РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода, Месяц) КАК ПродажиОбороты
;

////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Соединяем таблицу саму с собой для расчета накопительного итога
ВЫБРАТЬ
    Т1.Номенклатура КАК Номенклатура,
    Т1.Период КАК Период,
    Т1.Оборот КАК ОборотЗаМесяц,
    СУММА(Т2.Оборот) КАК НакопленныйОстаток
ИЗ
    ВТ_Обороты КАК Т1
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Обороты КАК Т2 
        ПО Т1.Номенклатура = Т2.Номенклатура 
        И Т1.Период >= Т2.Период
СГРУППИРОВАТЬ ПО
    Т1.Номенклатура,
    Т1.Период,
    Т1.Оборот

Важное предупреждение о производительности: Использование Способа 2 сопряжено с серьезными нагрузками на сервер баз данных. Условие соединения Т1.Период >= Т2.Период заставляет СУБД выполнять декартово произведение строк в разрезе каждой номенклатурной позиции. Если у вас в отчете фигурируют тысячи товаров и сотни периодов, запрос может выполняться крайне медленно. Поэтому для разработки внешних отчетов всегда отдавайте предпочтение Способу 1 с использованием механизмов расчета ресурсов СКД.

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