Использование LPE в кубе

В кубе можно использовать выражение${filters}. Оно используется для вставки пользовательских фильтров из управляющего дэша или onClickDataPoint в тело запроса.

к сведению

Чтобы перечисленные ниже функции отрабатывали в кубе, нужно в конфиг куба вставить выражение is_template: 1.

Функция filters()

Синтаксис:

<...>
where ${filters()}

Это выражение подставляет в куб условия, пришедшие из запроса дэша.
Запрос дэша в Luxms BI представляет собой JSON объект следующего формата:

{
  "with":"pokaz22.pokaz22",
  "columns":["test","name","sum(v_main):smv"],
  "filters":{
    "test":["=","Доктора наук+Профессор"],
    "name":["=","Женщины","Мужчины"]
  }
}

В поле columns перечисляются measures и dimensions, запрашиваемые дэшем. Поле filters содержит объект, где ключами являются наименования dimensions, а их значениями - массивы. В этих массивах первым элементом идёт знак сравнения, а последующими - перечень значений.

На основе приведённого выше JSON объекта выражение ${filters()} составит следующий запрос:

SELECT
    sum(v_main) as smv,
  degree as test,
  name as name
FROM
    (
    <куб>
    WHERE degree = 'Доктора наук+Профессор'
    AND name in ('Женщины','Мужчины')
   ) as pokaz22

Обратите внимание, что в случае, когда в фильтрах размерности выбрано несколько значений, автоматически используется условие in.

к сведению

Вы можете увидеть получившийся запрос, добавив параметр ?meta к ссылке запроса дэша, скопированного в виде cURL.

Параметры функции filters()

В качестве параметров этой функции могут перечисляться названия одного или нескольких полей.

осторожно

Необходимо использовать названия полей из куба, а не из источника

Синтаксис:

<запрос>
WHERE ${filters([column1, column2, ...])}

Перечисление полей определяет, какие именно фильтры передаются в куб. Написав ${filters(name, test)}, мы получим результат, аналогичный тому, что описан выше.

Приведём пример с JSON объектом, описанным выше:

Фрагмент куба:

<...>
WHERE ${filters(name)}

Запрос:

SELECT
    sum(v_main) as smv,
  degree as test,
  name as name
FROM
    (
    <куб>
    WHERE name in ('Женщины','Мужчины')
   ) as pokaz22

Функция except()

Синтаксис:

<запрос>
WHERE ${filters(except(<column1> [, column2, ...]))}

Данная функция в качестве параметров принимает одно или несколько ID полей в кубе. Перечисленные поля исключаются из перечня подставляемых в запрос.

То есть, если в случае испольования функции filters() мы рассчитываем, что в запрос подставятся только выбранные значения, то функция except() исключит из запроса все перечисленные в ней значения.

Указание алиаса таблицы

В случае, если в SQL запросе то или иное поле встречается в нескольких таблицах, то в функции filters() может потребоваться указать алиас конкретной таблицы. Или, может быть название размерности, по которой выполняется фильтрация, не совпадает с именем поля в таблице. Указать требуемое название таблицы и поля можно в следующей нотации:

${filters("column1":("table1"."fieldname1"))}

Или, если требуется указать и имя схемы, то следующим образом:

${filters("column1":("public"."table1"."fieldname1"))}

Фукнция filter() с аргументом get_in()

осторожно

Здесь используется функция filter() (не filters() что описана выше)

Функция get_in() описана здесь. С помощью нее можно получать данные о текущем пользователе LuxmsBI и прокинуть в SQL-запрос куба:

SELECT name, id
FROM "test"."test"
WHERE ${filter(site_ident=get_in(user,site_role_ident))}

После указания данного условия в кубе, в случае входа в систему под юзером с ролью Super итоговый запрос будет выглядеть следующим образом:

SELECT "name" as name, "id" as id
FROM (
            select  "name" as name, "id" as id from "test"."test"
            where site_ident = Super
) AS test

В случае необходимости оборачивания в кавычки значения необходимо также использовать функцию ql()

SELECT name, id
FROM "test"."test"
WHERE ${filter(site_ident=ql(get_in(user,site_role_ident)))}

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

SELECT "name" as name, "id" as id
FROM (
            select  "name" as name, "id" as id from "test"."test"
            where site_ident = 'Super'
) AS test

Функцию get_in() можно также использовать и для генерации столбца в кубе:

select ${filter(ql(get_in('user','site_role_ident')))} as role ,* from table

При выполнении данного запроса в кубе будет также добавлен столбец role с сайтовой ролью пользователя.

осторожно

Если ключ, указанный в get_in будет не найден, то в результат вернется undefined. Во избежание непредвиденного поведения необходимо использовать конструкцию case/when.

Пример:

select
    case
        when ${filter(ql(get_in(user, sys_config, param)))} != ''
        and ${filter(ql(get_in(user, sys_config, param)))} != 'undefined'
        then ${filter(ql(get_in(user, sys_config, param)))}
        else NULL
    end as column_1,
*
from table