Issue: Dashboard Filters Not Passing Through Query Builder Questions to SQL Questions with Variables

[mcrimes]

Issue: Dashboard Filters Not Passing Through Query Builder Questions to SQL Questions with Variables

What we're trying to achieve:

We have a dashboard with filters (tenant, start_date, end_date) that need to filter a Query Builder question that's based on a SQL question.

Our setup:

1. Dashboard has 3 filter parameters: tenant, start_date, end_date

2. Question A (Query Builder) - uses Question B as its data source, displayed as a Pivot Table

3. Question B (SQL/Native Query) - contains variables {{tenant}}, {{start_date}}, {{end_date}}

Why this architecture:

We need Question A to be a Query Builder question because we're rendering it as a Pivot Table. Writing the entire query as a native SQL question and rendering it as a pivot table is extremely slow (unusable performance). However, the underlying SQL query in Question B executes very quickly. This two-layer approach (Query Builder pivot on top of fast SQL query) gives us acceptable performance - except the dashboard filters don't work.

The problem:

When we connect dashboard filters to Question A, the filters don't pass through to the variables in Question B. The SQL variables remain unfiltered.

What we've tried:

• Dashboard filters work if Question A is also a SQL question with variables

• But pivot table rendering on a SQL question is incredibly slow (unacceptable performance)

• When Question A is a Query Builder question (fast pivot rendering), there's no way to connect dashboard filters to the SQL variables in its source Question B

Related GitHub issue:

This appears to be the same limitation described in GitHub issue #6449 (480+ upvotes): "Field filter from nested query"
https://github.com/metabase/metabase/issues/6449

From the discussion, it seems that "variables are currently not available to parent questions" and implementing pass-through would require addressing permission levels and other complexities.

Question for support:

Is there a way to pass dashboard filters through a Query Builder question to SQL variables in its source SQL question? Or is this still a known limitation as described in issue #6449?

If this is not supported, what would you recommend as the best workaround for our use case where we need both pivot table visualization and dashboard filters with good performance?

Environment:

• Metabase version: 0.57.3

• Database: SQL Server

[dwhitemv]

It’s not currently possible to specify values for SQL variables from a Query Builder question, so no, you can’t do this as described.

Metabase 57 added the ability to have SQL snippets take variables. You can change Question B into a snippet, then write Question A as a SQL question but just have the snippet as the query text. The downside of this, of course, is that you aren’t using the query builder and you lose drill-through.

That said, I’m not sure you really need Question B in the first place; what is it doing that Question A can’t do?

[mcrimes]

Thanks for the response, it’s good to know I wasn’t missing anything with the route I was trying to go with.

However, your questioning of why I needed both A and B made me go back and look again at what I was doing. I’d gotten there by trying both pure questions in either SQL (fast performance but couldn’t use as a Pivot Table, but displayed all the data in a flat table) or Query Builder (incredibly bad performance on a massive table, but did give me the Pivot Table). If I ignored the filtering part, then I found I could get the best of both worlds via splitting the actual querying out into a SQL (B) and then overlaying the summarization via a Query Builder (A).

At that point I think the blinkers set in & I convinced myself that was the only route that metabase would permit.

Your question this morning (and likely a fresh set of eyes on the problem) made me try putting the SQL into a view in the database and removing B from metabase, then using the view as the source of my Query Builder with both tenant and date range filters.. And lo & behold, it works and while not quite as fast as the unfiltered A+B, it’s still returning in under 1-2 seconds which looks likely to be acceptable (it certainly beats the 2+ minutes that the pure Query Builder approach was taking yesterday!).

So thanks again for the response! I hope this post will be useful for future searchers who’ve gotten themselves stuck.