Создание дашборда с динамическими столбцами в таблице

В данном подразделе показан пример настройки дашборда, содержащего виджет таблицы с динамическими столбцами.

Допустим:

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

1. Вы задаёте интервал в фильтре.
2. Введённые значения передаются в виджет «Число».
3. В виджете «Число» формируется регулярное выражение для таблицы.
4. Регулярное выражение через специальный фильтр подставляется в запрос таблицы как переменная.
5. Таблица получает значение переменной и обновляется.

Результат: набор столбцов динамически изменяется в соответствии с заданными значениями.

Описанный здесь принцип подходит для решения более сложных задач. Пример с датами взят для удобства.

Вам понадобится:

• промежуточный технический виджет «Число» (и соответствующий запрос). Он будет хранить переменную для подстановки;
• два поля ввода (текстовых фильтра) для задания границ интервала: левая граница входит в интервал, правая — не входит;
• дополнительный технический фильтр. Он примет значение из «Числа» и применит его к таблице;
• переменная в запросе таблицы.

Создайте запрос для регулярного выражения

Используйте следующий код:

SELECT concat(
          '^(',
          arrayStringConcat(
            arrayMap(x -> concat('__', toString(x)),
                     range(toUInt32({{start}}), toUInt32({{end}}))
            ),
            '|'
          ),
          ')$'
        ) AS regexp

Псевдоним после AS может отличаться от regexp, это некритично для получения результата.

В данном запросе используются переменные start и end. Чтобы проверить его работу, введите начало и конец интервала в поля ввода над запросом. В блоке результата вы должны увидеть регулярное выражение с перечислением лет. Правая граница не входит в интервал.

Двойное нижнее подчёркивание присоединяется к году как приставка и нужно, чтобы система отличала динамический столбец от обычного. Это позволяет автоматически располагать столбцы при выводе в порядке возрастания.

Если возникают проблемы с применением введённых значений, используйте Ctrl + Enter.

Выберите визуализацию «Число». Затем сохраните запрос в вашу коллекцию и добавьте на дашборд с таблицей.

Добавьте на дашборд текстовые фильтры

На предыдущем шаге вы вводили значения в поля ввода запроса. Теперь нужно обеспечить их инициализацию на дашборде. Для этого будут использоваться текстовые фильтры.

Также понадобится техническое поле, которое будет хранить значение регулярного выражения.

Добавьте на дашборд 3 текстовых фильтра:

• поля ввода «с» и «по»;
• список значений «regexp» (технический фильтр).

Названия ваших виджетов фильтра могут отличаться.

Задайте значения по умолчанию для «с» и «по», чтобы не получать ошибку при открытии дашборда.

Свяжите:

• первое поле ввода «с» > start виджета «Число»;
• второе поле вводе «по» > end виджета «Число».

Сохраните изменения.

Добавьте интерактивность для «Числа»

Необходимо обеспечить передачу регулярного выражения из «Числа» в поле технического фильтра.

Перейдите к редактированию дашборда. Наведите курсор на «Число», выберите «Поведение при нажатии». На появившейся панели справа выберите:

• Обновить фильтр дашборда;
• свяжите фильтр «regexp» со столбцом regexp;
• активируйте «Применять по событию изменения значения»;
• нажмите «Готово».

Сохраните дашборд.

Преобразуйте запрос таблицы в прямой

Из настроек виджета таблицы перейдите к запросу. Если он был составлен в конструкторе, потребуется преобразовать его в прямой запрос для возможности подстановки переменной в блок SELECT.

Далее вставьте в SELECT строку:

COLUMNS({{regexp}})

Ниже приведён пример запроса с подстановкой значения переменной. Вы можете использовать этот код, если у вас ещё нет таблицы для экспериментов.

WITH dataset AS (
    SELECT 'Отрасль A' AS "отрасль", 'Эмитент 1' AS "эмитент", 'Выручка' AS "показатель", 100 AS "значение_показателя", 2020 AS "год"
    UNION ALL SELECT 'Отрасль A','Эмитент 1','Выручка',120,2021
    UNION ALL SELECT 'Отрасль A','Эмитент 1','Выручка',150,2022
    UNION ALL SELECT 'Отрасль A','Эмитент 1','Выручка',170,2023
    UNION ALL SELECT 'Отрасль A','Эмитент 1','Выручка',200,2024
    UNION ALL SELECT 'Отрасль A','Эмитент 2','Выручка',80,2020
    UNION ALL SELECT 'Отрасль A','Эмитент 2','Выручка',90,2021
    UNION ALL SELECT 'Отрасль A','Эмитент 2','Выручка',110,2022
    UNION ALL SELECT 'Отрасль A','Эмитент 2','Выручка',130,2023
    UNION ALL SELECT 'Отрасль A','Эмитент 2','Выручка',140,2024
    UNION ALL SELECT 'Отрасль B','Эмитент 3','EBITDA',50,2021
    UNION ALL SELECT 'Отрасль B','Эмитент 3','EBITDA',60,2022
    UNION ALL SELECT 'Отрасль B','Эмитент 3','EBITDA',65,2023
    UNION ALL SELECT 'Отрасль B','Эмитент 3','EBITDA',70,2024
)

SELECT
    src."показатель",
    src."эмитент",
    COLUMNS({{regexp}})     -- расширяем только по годам, "г." не выводим
FROM (
    SELECT
        "показатель",
        "эмитент",
        maxIf("значение_показателя", "год" = 2020) AS "__2020",
        maxIf("значение_показателя", "год" = 2021) AS "__2021",
        maxIf("значение_показателя", "год" = 2022) AS "__2022",
        maxIf("значение_показателя", "год" = 2023) AS "__2023",
        maxIf("значение_показателя", "год" = 2024) AS "__2024",
        maxIf("значение_показателя", "год" = 2025) AS "__2025"
    FROM dataset
    GROUP BY "показатель","эмитент"
) AS src
ORDER BY src."показатель", src."эмитент";

В предварительном просмотре такого запроса будет ошибка до того момента, пока вы не введёте значение переменной. Чтобы увидеть все столбцы в предварительном просмотре, подставьте в поле ввода regexp: «.+».

AS "__2025": как уже отмечалось выше, двойные нижние подчёркивания являются признаком динамических столбцов таблицы.

Сохраните изменённый запрос. Вернитесь на дашборд.

Свяжите технический фильтр с таблицей

Перейдите к редактированию дашборда. Щёлкните по техническому фильтру regexp и свяжите его с вашей таблицей — столбцом regexp.

Сохраните дашборд.

Проверьте работу связки

Измените значения в полях виджета фильтра «с» и «по». Убедитесь в обновлении таблицы.

Вы можете щёлкнуть по «Числу» — первый щелчок применяет технический фильтр, второй — отменяет. Виджет таблицы может не отображаться, когда фильтр отменён, это не является ошибкой.

Скройте лишнее

Технический фильтр regexp можно скрыть в настройках дашборда. Карточку «Число», передающую значение по событию изменения, оставьте видимой: скрытая карточка не отрисовывается и не запускает автоматическую подстановку.

Подробности и ограничения приведены в разделе «Значение фильтра, вычисленное запросом к данным».

Сохраните настройки дашборда и ещё раз проверьте работу связки после скрытия технических элементов.

Теперь настройка завершена.

Значение фильтра, вычисленное запросом к данным

Карточка с визуализацией «Число» может получать результат запроса и передавать его в фильтр дашборда. Например, запрос к таблице заказов определяет последнюю доступную дату:

SELECT max(order_date) AS latest_order_date
FROM orders;

1. Сохраните запрос и выберите визуализацию «Число». Добавьте вопрос на дашборд.
2. Добавьте фильтр даты и подключите его к полям нужных карточек либо к SQL-переменной, например {{report_date}}.
3. У карточки «Число» откройте «Поведение при нажатии» → «Обновите фильтр дашборда» и сопоставьте столбец latest_order_date с фильтром даты.
4. Включите «Применять по событию изменения значения» и сохраните дашборд. Этот переключатель позволяет передавать результат без нажатия на число.

Когда карточка отрисовывается в браузере после получения результата, берётся первая строка и сопоставленный столбец; если столбец не найден, используется первый. Значение записывается, если оно отличается от текущего при строковом сравнении. Для фильтра с несколькими значениями сравнивается первое выбранное значение. NULL и отсутствие результата пропускаются; ноль допустим.

Исходный вопрос можно подключить к другим фильтрам: после их применения запрос пересчитается, и изменившееся значение снова попадёт в целевой фильтр. При автоматическом обновлении дашборда запрос также выполняется повторно; новое значение передаётся после получения результата и отрисовки карточки. Не подключайте вычисляющую карточку к тому же фильтру без проверки зависимостей: это может создать цикл пересчётов. Механизм требует отрисованной карточки «Число» в браузере.

Для фильтра даты возвращайте календарную дату в формате YYYY-MM-DD, без времени и часового пояса. Значение с временем может передаться в SQL, но остаться без подписи в виджете. Если драйвер возвращает дату с временем, приведите результат к строке в SQL; например, для поддерживающей такие преобразования СУБД:

SELECT CAST(CAST(MAX(order_date) AS DATE) AS VARCHAR) AS latest_order_date
FROM orders;

При NULL прежнее значение фильтра сохраняется. Переключатель не превращает фильтр в серверную переменную с выражением: подстановка зависит от выполнения компонента в открытом браузере.

Режим	Поведение и ограничения
Обычный дашборд	Видимая карточка «Число» передаёт результат без нажатия. Смена связанного с ней исходного фильтра запускает новый расчёт.
PDF и PPTX из открытого дашборда	В выгрузку попадает уже отрисованное состояние. Дождитесь появления рассчитанной даты и результатов зависимых карточек перед экспортом; сам файл не пересчитывает фильтры.
XLSX из открытого дашборда	Выгружаются таблицы и сводные с текущими параметрами. Рассчитанная дата используется зависимой таблицей; сама карточка «Число» не экспортируется как таблица. Не рассчитывайте на экспорт как на отдельный запуск автоматической подстановки.
Подписки	Запросы выполняются на сервере с параметрами подписки, без браузерного события карточки. Этот приём не обеспечивает автоматическую подстановку для подписок: задавайте и проверяйте их параметры отдельно.
Публичная ссылка в iframe	Подстановка выполняется при отрисовке видимой карточки в браузере, если фильтры и данные доступны. Для других вариантов встраивания проверяйте доступность карточки и параметров отдельно.
Скрытая техническая карточка	«Скрыть карточку» исключает её из отрисовки, поэтому подстановка не выполняется. Скрытие на мобильном экране также останавливает её на этом экране. Не делайте вычисляющую карточку скрытой, если от неё зависит фильтр.

См. подключение фильтров к карточкам и подключение SQL-переменной.

Настройка относится к карточке «Число».

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

Дополнительные материалы

• Интерактивные дашборды
• Редактор прямых запросов
• Сводная таблица
• Таблица
• Фильтры и параметры запросов
• Число