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