При разработке отчетов на базе Системы компоновки данных (СКД) программисты часто сталкиваются с неприятным поведением системы: при добавлении в детальные записи или группировки поля Регистратор (документа движения) начальные и конечные остатки на уровне общих итогов или вышестоящих группировок (например, по складам или подразделениям) начинают некорректно суммироваться и удваиваться. Вместо реального сальдо на начало и конец периода мы получаем огромные, экономически бессмысленные цифры.
Давайте подробно разберем, почему возникает эта проблема, проанализируем внутренние механизмы СКД и рассмотрим по шагам несколько надежных способов ее решения.
Система компоновки данных рассчитывает остатки принципиально иначе, чем обычный запрос СУБД. Она делает это динамически на основе переданных ей полей-измерений и полей-периодов. Для правильного расчета остатка СКД должна четко понимать хронологическую последовательность записей и сопоставлять движения с временной шкалой.
Если в запросе присутствует поле Регистратор, но системе не объяснили его роль, СКД начинает воспринимать регистратор как обычное измерение (наряду со складом или номенклатурой). В результате для группировок более высокого уровня она просто складывает остатки по всем документам-регистраторам, что и приводит к кратному завышению (суммированию) итогов. Для корректной работы СКД должна четко сопоставить каждую запись с осью времени, на которой Регистратор является одной из точек периода.
Ниже мы рассмотрим три основных метода решения этой проблемы: правильная настройка ролей периодов, использование пакетных запросов с временными таблицами и тонкая настройка вычисления ресурсов.
Этот способ является самым правильным с точки зрения архитектуры СКД. Нам необходимо явно указать системе, какие поля отвечают за периоды и какова их иерархия. Выполним настройку по шагам:
Шаг 1. Добавление поля периода в запрос. Прежде всего, в запросе обязательно должно присутствовать поле секундного периода. Без него СКД не сможет выстроить документы в однозначной хронологической последовательности. Добавим поле ПериодСекунда в наш запрос к виртуальной таблице остатков и оборотов:
ВЫБРАТЬ РАЗРЕШЕННЫЕ
Активы.Статья КАК Статья,
Активы.Аналитика КАК Аналитика,
Активы.Подразделение КАК Подразделение,
Активы.СуммаНачальныйОстаток КАК НачОстатокАктивов,
Активы.СуммаПриход КАК ПриходАктива,
Активы.СуммаРасход КАК РасходАктива,
Активы.СуммаКонечныйОстаток КАК КонОстатокАктивов,
Активы.ПериодСекунда КАК ПериодСекунда,
ВЫБОР
КОГДА Активы.Регистратор = НЕОПРЕДЕЛЕНО ТОГДА NULL
ИНАЧЕ Активы.Регистратор
КОНЕЦ КАК Регистратор
ИЗ
РегистрНакопления.ПрочиеАктивыПассивы.ОстаткиИОбороты({(&ДатаНачала)}, {(&ДатаКонца)}, Авто, , ) КАК Активы
Обратите внимание на конструкцию ВЫБОР. В виртуальной таблице ОстаткиИОбороты для строк начального остатка поле Регистратор содержит значение НЕОПРЕДЕЛЕНО. Преобразование его в физический NULL критически важно для последующей настройки СКД.
Шаг 2. Настройка ролей на вкладке "Набор данных". Перейдем в схеме компоновки данных на вкладку "Наборы данных". Нам необходимо вручную настроить роли для полей ПериодСекунда и Регистратор в таблице полей:
ПериодСекунда установите роль Период и задайте порядковый номер 1.Регистратор установите роль Период и задайте порядковый номер 2.Регистратор обязательно установите флаг "Игнорировать значения NULL".Благодаря флагу "Игнорировать значения NULL", СКД будет отбрасывать пустые "технические" строки начала и конца периода при выводе детальных записей регистраторов, но сохранит их для корректного расчета общих итогов.
Некоторые разработчики пытаются решить проблему "в лоб": получить остатки в первом запросе пакета без регистратора, поместить их во временную таблицу, а затем во втором запросе левым соединением подключить таблицу с регистраторами (как в сообщениях 11 и 13).
Посмотрим на пример такого построения запроса:
ВЫБРАТЬ РАЗРЕШЕННЫЕ
Активы.Статья КАК Статья,
Активы.Аналитика КАК Аналитика,
Активы.Подразделение КАК Подразделение,
СУММА(Активы.СуммаНачальныйОстаток) КАК НачОстатокАктивов,
СУММА(Активы.СуммаПриход) КАК ПриходАктива,
СУММА(Активы.СуммаРасход) КАК РасходАктива,
СУММА(Активы.СуммаКонечныйОстаток) КАК КонОстатокАктивов
ПОМЕСТИТЬ вт_Активы
ИЗ
РегистрНакопления.ПрочиеАктивыПассивы.ОстаткиИОбороты({(&ДатаНачала)}, {(&ДатаКонца)}, Авто, , ) КАК Активы
СГРУППИРОВАТЬ ПО
Активы.Статья,
Активы.Аналитика,
Активы.Подразделение
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
вт_Активы.Статья КАК Раздел,
вт_Активы.Аналитика КАК Аналитика,
вт_Активы.Подразделение КАК Подразделение,
вт_Активы.НачОстатокАктивов КАК НачОстатокАктивов,
вт_Активы.ПриходАктива КАК ПриходАктива,
вт_Активы.РасходАктива КАК РасходАктива,
вт_Активы.КонОстатокАктивов КАК КонОстатокАктивов,
АктивыДвижения.Регистратор КАК Регистратор,
АктивыДвижения.Регистратор.Склад КАК Склад
ИЗ
вт_Активы КАК вт_Активы
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ПрочиеАктивыПассивы.ОстаткиИОбороты({(&ДатаНачала)}, {(&ДатаКонца)}, Регистратор, , ) КАК АктивыДвижения
ПО вт_Активы.Подразделение = АктивыДвижения.Подразделение
И вт_Активы.Статья = АктивыДвижения.Статья
И вт_Активы.Аналитика = АктивыДвижения.Аналитика
В чем кроется главная ловушка этого подхода? Механизм "Автозаполнение" в СКД отлично распознает роли полей только при прямой работе с виртуальными таблицами СУБД. Когда мы выбираем остатки из временной таблицы вт_Активы, СКД "забывает", что поля НачОстатокАктивов и КонОстатокАктивов являются остатками. Система начинает считать их обычными числовыми ресурсами и складывает при любой группировке.
Чтобы этот метод заработал, нам придется вручную настроить роли полей на вкладке "Наборы данных" для полей из временной таблицы:
НачОстатокАктивов задайте роль Остатки. Имя группы укажите, например, СуммаАктивов. Тип остатка укажите Начальный остаток. Обязательно укажите поле связи (например, ПериодСекунда).КонОстатокАктивов задайте ту же роль Остатки, то же имя группы СуммаАктивов, но тип остатка выберите Конечный остаток.Только при такой ручной разметке СКД поймет, как правильно агрегировать данные из временной таблицы без двоения и лишнего суммирования.
Если структура вашего запроса слишком сложна, содержит множество левых соединений и настроить роли периодов стандартным путем не получается, можно принудительно ограничить расчет остатков на уровне вкладки "Ресурсы" в СКД.
Проанализируем ситуацию: нам нужно, чтобы остатки выводились на уровне Склада, Статьи и Подразделения, но не суммировались на уровне детальных записей документов. Для этого выполним следующие действия:
НачОстатокАктивов в колонке "Выражение" укажем стандартную агрегатную функцию: Сумма(НачОстатокАктивов).Статья, Подразделение, Аналитика, Склад). Поле Регистратор должно остаться не отмеченным.НачОстатокАктивов еще раз. В колонке "Выражение" укажем значение 0 (или NULL), а в колонке "Рассчитывать по..." выберем исключительно Регистратор.Аналогичную процедуру настройки выполним для ресурса КонОстатокАктивов. Таким нехитрым способом мы заставим СКД выводить верные математические итоги по группировкам верхнего уровня, при этом полностью исключим некорректное суммирование остатков по строкам документов движения.
При использовании детализации до регистратора большое значение имеет четвертый параметр виртуальной таблицы ОстаткиИОбороты — метод дополнения периодов. По умолчанию СКД может не выводить остатки на те даты (документы), по которым не было движений. Чтобы избежать искажения картины, рекомендуется явно указывать в параметрах таблицы метод дополнения Движения или ДвиженияИГраницыПериода в тексте запроса:
РегистрНакопления.ПрочиеАктивыПассивы.ОстаткиИОбороты(&ДатаНачала, &ДатаКонца, Авто, ДвиженияИГраницыПериода, )
Это гарантирует, что остатки на границах выбранного периода будут рассчитаны и выведены абсолютно точно, независимо от наличия оборотов в эти дни.
Подводя итог, можно сказать, что корректный расчет остатков по регистратору в СКД требует четкого понимания ролей полей. Использование первого метода с настройкой ролей периодов ПериодСекунда (роль Период, номер 1) и Регистратор (роль Период, номер 2, Игнорировать NULL) является наиболее надежным и стандартным решением, которое гарантирует правильность отчета при любых изменениях структуры компоновки.