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

  3 минутное чтение  

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

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

  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! ; })

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

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