Hi Metabase Team,
I am writing to report a significant limitation (or a potential bug) regarding the propagation of Field Filters when using Nested Queries to build Pivot Tables.
Context: I have a complex SQL query (PostgreSQL) that uses CTEs and UNION ALL to unpivot data into a format suitable for a report (Dimensions: Locale, Month, Metric; Measure: Value). This query includes several Field Filters ({{date}}, {{country}}, {{year}}) mapped to physical tables.
The Workflow & Problem:
-
Step 1: The SQL Question (Question A) works perfectly. Filters are visible and functional.
-
Step 2: To create a Pivot Table, Metabase requires an aggregated source. Since Native SQL is often not automatically recognized as an "aggregated" query, I create a new Question (Question B) using Question A as the source.
-
Step 3: In the GUI editor for Question B, I use
SummarizeandGroup Byto enable the Pivot Table visualization.
The Bug:
-
Once Question B (the nested query) is created, the Field Filters from the underlying SQL (Question A) are no longer visible in the filter widgets.
-
More importantly, when I add Question B to a Dashboard, I cannot map Dashboard Filters to the underlying SQL parameters. The variables
{{date}},{{country}}, etc., are not exposed in the mapping interface, even though they are correctly defined in the source SQL. -
Mapping only works if I use simple "Text" or "Number" variables, but this breaks the "Field Filter" functionality (like dynamic dropdowns based on database values).
Expected Behavior: Field Filters defined in a base SQL Question should be "exposed" through all layers of nesting, allowing them to be mapped to Dashboard Filters, especially when nesting is the only way to achieve specific visualizations like Pivot Tables.
Technical Details:
-
Metabase Version: v0.57.6 from 2025-12-11
-
Database: PostgreSQL
-
Query Structure: CTEs with UNION ALL followed by a GUI-based aggregation layer.
SELECT
"source"."Год" AS "Год",
"source"."Месяц" AS "Месяц",
"source"."Департамент" AS "Департамент",
"source"."Метрика" AS "Метрика",
SUM("source"."Значение") AS "sum"
FROM
(
WITH data AS (
SELECT
COALESCE(
CASE
WHEN o.is_site = 1 THEN 'Site'
END,
d.name ->> 'en',
'-'
) AS locale_name,
TO_CHAR(DATE_TRUNC('month', o.order_date), 'YYYYMM') AS month,
EXTRACT(
YEAR
FROM
o.order_date
) AS year_val,
-- Год для группировки
COUNT(DISTINCT o.tm_cnt_contact_id) AS clients_count,
COUNT(
DISTINCT CASE
WHEN o.new_amount_with_revenue > 0 THEN o.tm_cnt_contact_id
END
) AS new_clients_count,
SUM(o.amount_with_revenue) AS total_revenue,
SUM(o.new_amount_with_revenue) AS new_revenue,
COUNT(DISTINCT o.sa_order_id) AS payments_count,
COUNT(DISTINCT o.new_sa_order_id) AS new_payments_count
FROM
dwh.agg_order o
LEFT JOIN dwh.v_asn_crm_emp_department_auth_manager_user dept ON o.auth_manager_user_id = dept.auth_manager_user_id
LEFT JOIN dwh.dim_crm_emp_department d ON dept.crm_emp_top_level_department_id = d.crm_emp_department_id
LEFT JOIN dwh.dim_tm_cnt_communication comm ON o.tm_cnt_contact_id = comm.tm_cnt_contact_id
LEFT JOIN dwh.dim_auth_user u ON comm.value = u.email
WHERE
o.is_order = 1
/* ПРОВЕРЬТЕ НАСТРОЙКИ СПРАВА: */
-- Field Filter -> agg_order.order_date
-- Field Filter -> dim_auth_user.profile_country
-- Field Filter -> agg_order.order_date (или Number)
-- ТИП TEXT
GROUP BY
1,
2,
3
),
unpivoted AS (
SELECT
locale_name,
month,
year_val,
'Количество клиентов' AS metric,
clients_count AS val
FROM
data
UNION ALL
SELECT
locale_name,
month,
year_val,
'Количество новых клиентов',
new_clients_count
FROM
data
UNION ALL
SELECT
locale_name,
month,
year_val,
'Сумма оплат',
total_revenue
FROM
data
UNION ALL
SELECT
locale_name,
month,
year_val,
'Сумма оплат по новым клиентам',
new_revenue
FROM
data
UNION ALL
SELECT
locale_name,
month,
year_val,
'Количество оплат',
payments_count
FROM
data
UNION ALL
SELECT
locale_name,
month,
year_val,
'Количество оплат по новым клиентам',
new_payments_count
FROM
data
)
SELECT
locale_name AS "Департамент",
metric AS "Метрика",
month AS "Месяц",
year_val AS "Год",
SUM(val) AS "Значение"
FROM
unpivoted
GROUP BY
1,
2,
3,
4
ORDER BY
CASE
WHEN locale_name = 'Spanish' THEN 1
WHEN locale_name = 'Site' THEN 2
WHEN locale_name = 'Russian' THEN 3
WHEN locale_name = 'English' THEN 4
ELSE 5
END,
2,
3
) AS "source"
GROUP BY
"source"."Год",
"source"."Месяц",
"source"."Департамент",
"source"."Метрика"
ORDER BY
"source"."Год" ASC,
"source"."Месяц" ASC,
"source"."Департамент" ASC,
"source"."Метрика" ASC
LIMIT
100
Best regards, Harry Smith