Использование переменных в дашборде

В скриптах вычисляемых выражений и наборов данных, используемых в Аналитическом портале ModusBI, можно предусматривать различные алгоритмы в зависимости от настроек фильтра визуального элемента.

Примеры использования:

  1. значение сумм продаж надо рассчитывать в различных национальных валютах, вид которой выбирается в фильтре;

  2. необходимо по ключу объединить данные нескольких таблиц (используя для объединения временные периоды, наименования регионов и подразделений, мест возникновения затрат и т.д.). Если фильтрами выбраны лишь некоторые значения для ключей, то процесс объединения может быть более оптимизированным с точки зрения ресурсов и времени выполнения — объединение будет производиться сразу только по выбранным в фильтрах значениям. В этой ситуации популярная в моделировании данных каноническая схема “звезда” будет работать наиболее эффективно.

Для реализации такого механизма предусмотрены пользовательские переменные.

Синтаксис переменной имеет следующий вид:

{…!env.\<Имя переменной\>!...; \<Выражение пустого условия\>}

где “env” — это пространство имен для пользовательских переменных.

В примере с валютой синтаксис может выглядеть следующим образом:

Область выражения { …<!env.ИмяПеременной!>…; … } может содержать произвольное выражение в левой и в правой части, но с условием, что в левой части конструкции будет объявлена переменная <!env.ИмяПеременной!>.

Конкретно в примере, если переменная VAL выбрана в фильтре как ‘USD’, то используется цена в валюте из поля Цена_вал, в противном случае (при невыполнении условия, когда в фильтре выберут, например, валюту ‘Руб’) — цена в рублях из поля Цена_руб. Если фильтр не выбран, то прописывается условие “1=1”, которое всегда возвращает истину (true), и для этого — вариант по умолчанию: применяется цена в валюте (возвращается опять-таки поле Цена_вал).

К примеру, имеется “большая” таблица продаж “modus_big_table” следующего вида:

В фильтре видов валют выберем USD:

В следующей таблице производится пересчёт суммы в валюту:

Если в фильтре валют выбрать Руб, то будет произведен соответствующий пересчёт.

Посмотрим, как сконструирована предыдущая таблица в Аналитическом портале, включая код двух вычисляемых выражений:

Как видно из рисунков выше, для вычисляемых выражений Цена и Сумма использован одинаковый механизм обращения к тем или иным значениям фильтра (USD / Руб) и полям набора данных, отвечающих за цену (Цена_вал, Цена_руб) и сумму соответственно (Сумма_вал, Сумма_руб). Везде задействована переменная VAL.

Чтобы механизм работал, нужна соответствующая настройка фильтра валюты. Набор данных для фильтра формируется специальным запросом:

Здесь просто объединяются в набор две текстовых константы: ‘Руб’ и ‘USD’. Самое главное здесь, что переменной присваивается имя “Валюта”.

При этом, в настройке фильтра предусмотрен раздел “Переменные”, в котором мы можем с помощью кнопки “Добавить” указать нашу переменную “Валюта” и связать её с VAL, которая используется в вычисляемых выражениях:

В следующем примере надо пересчитать сумму продаж для региона:

Здесь верхняя “маленькая” таблица из двух колонок имеет имя “modus_small_table”.

В настройке фильтра региона добавлена переменная фильтра региона “Кол”, связанная с переменной REGION:

Для набора данных нижней (итоговой) таблицы используется следующий скрипт:

SELECT 
FROM "modus_big_table" pr
JOIN (
        SELECT 
        FROM "modus_small_table"
        WHERE "Кол" in ({!env.REGION!; 2})
    ) vz 
ON pr."id_Региона" = vz."Кол"

Здесь есть подзапрос с “маленькой” таблицей “modus_small_table”. В ней данные фильтруются по условию, что столбец “Кол” попадает в список значений фильтра REGION. То есть согласно совпадению с фильтром выбирается либо строка {“Москва”, 1}, либо {“Санкт-Петербург”, 2}, либо {Татарстан, 3}.

Затем идёт объединение этой таблицы с “большой” таблицей продаж “modus_big_table”. В большой таблице есть поле-ключ “id_Региона”. Объединение идёт по совпадению значения “Кол” в отфильтрованной в подзапросе таблице и ключом “id_Региона” большой таблицы (цифры 1, 2 или 3).

Цифра 2 дает запасной вариант (“страховочное” условие, если вычисление переменной произошло с ошибкой) для скрипта: ему соответствует “Санкт-Петербург”.

На основе полученного набора данных в конструкторе настраивается макет итоговой таблицы:

В процессе применения переменных есть определённые особенности.

  1. Значение переменной должно быть числом или текстом. Для дат лучше делать преобразование, например:

{toDate(!env.Date!) ; '2020-01-01'} = '2023-11-08'

  1. Значение может быть единичным значением или списком, в зависимости от настройки фильтра:

Region IN ({!env.Reg! ; 0)

  1. Даты начала и окончания периода фильтра обозначаются суффиксами .BEGIN и .END:

Date between ({ toDate(!env.Reg.BEGIN!) ; '2020-01-01'} AND ({ toDate(!env.Reg.END!) ; toDate(now()) }