Довольно часто при разработке отчетов на Системе компоновки данных (СКД) возникает задача: вывести список номенклатуры, общие начальные и конечные остатки, обороты (приход и расход), а также разбить данные по датам в отдельных колонках (сформировать кросс-таблицу). Разберем по шагам, как правильно реализовать такой отчет и почему простое добавление группировки колонок не всегда работает корректно.
Проанализируем ситуацию: главная проблема вывода остатков и оборотов с разбивкой дат по колонкам кроется в математической природе этих данных. В отличие от прихода и расхода (оборотов), остатки не являются аддитивными полями. Это значит, что их нельзя просто суммировать за разные даты. Если в СКД выводить периоды без специальных настроек, система не поймет логику расчета и либо задублирует строки с номенклатурой, либо некорректно сложит начальные и конечные остатки в общих итогах кросс-таблицы.
Для того чтобы кросс-таблица корректно рассчитывала итоги при выводе периодов в колонки, применим встроенные механизмы платформы. Рассмотрим подробнее, какие настройки метаданных схемы нам потребуются:
Остаток и явно указать их взаимосвязь. СКД должна четко понимать, какое поле является начальным остатком для группировки, а какое — конечным.ПериодДень или ПериодСекунда, в наборе данных нужно назначить роль Период. Обязательно укажем их иерархический порядок (1, 2, 3...). Это позволит платформе выстроить правильную хронологию. Благодаря этому система будет брать начальный остаток из самого раннего периода, а конечный — из самого позднего.Посмотрим на ограничения, с которыми можно столкнуться при использовании описанного выше метода. Функция дополнения периодов сработает только в том случае, если платформе заранее известны границы отчета. В настройках группировки колонок потребуется явно указать поля или параметры начальной и конечной даты интервала.
Кроме того, в кросс-таблицах усложняется использование пользовательских вычисляемых полей в общих итогах. Это связано с тем, что значения рассчитываются в контексте пересечения конкретной строки (номенклатуры) и колонки (даты), что может исказить итоговые суммы.
Если стандартные настройки ролей СКД кажутся слишком громоздкими или не подходят под специфику задачи, рассмотрим два альтернативных варианта.
1. Расчет остатков на каждый день внутри самого запроса
Эту задачу можно решить на уровне SQL-запроса, без сложной настройки ролей в самой СКД. В тексте запроса мы генерируем виртуальную таблицу со всеми датами выбранного интервала (создаем так называемый «календарь»). К этой таблице дат левым соединением (о чем упоминалось в исходном обсуждении) привязываются обороты, после чего рассчитывается нарастающий итог. Остатки по дням вычисляются по простым математическим формулам: к начальному остатку прибавляется оборот нарастающим итогом. В этом случае СКД получает уже подготовленные данные и просто выводит плоскую таблицу без хитрых настроек итога.
2. Использование нескольких наборов данных
Вместо того чтобы собирать всё в одном огромном запросе, разнесем получение данных. Создадим два независимых набора: Набор данных 1 будет получать только остатки, а Набор данных 2 — только обороты. Сопоставление таблиц произведем на вкладке Связи наборов данных. Этот подход позволяет избежать непреднамеренного дублирования записей еще на этапе получения данных базой, а также дает возможность гибко управлять параметрами и фильтрами каждого набора по отдельности.