Field filter from nested query

[biji]

I have created custom SQL with Field filter and add the question to dashboard. I can add category filter and choose the field
And then I create another question based on previous question this is pie chart. And I add the question to the dashboard but I can not use variable from previous question for filtering

Thank you

⬇️ Please click the 👍 reaction instead of leaving a +1 or update? comment

[wvengen]

It would be very useful to propagate variables from the originating question to the derived question.

[eyalfir]

Same issue.
The Field Filter options for native SQL questions is a life saver in term of flexibility to power users (=knows their SQL). However, that power user cannot democratise this by giving hist non-SQL-savvy colleagues the ability to ask derived questions. So in my case, most of the team cannot realistically benefit from custom SQL queries.

[brycemelvin]

I am trying to create a cumulative sum view of a filterable custom sql query but cannot pass the variables through on the cumulative sum view via the dashboard. This is a must have for us, unless someone knows another way to do a cumulative sum view in SQL.

[flamber]

Variables are currently not available to parent questions, so it would require allowing pass-thru of those, which would involve some permission levels too. It's slightly more complicated than what it sounds like.

[mlm603]

similar issue to some of those in this thread, when we have a SQL question with a required field filter variable, we want to be able to build a Simple Question from it and filter the variable in the Simple Question.

Running into some issues in v1.35

My SQL Question looks like this, with a field filter variable called Created Date that is a date type

Then when I build a Simple Question off of that SQL Question, I see this template_variable filter option (which I assume is supposed to be connected to my Created Date variable?)

But then that template_variable filter only allows text inputs

[etoulas]

@rouanes: I am using Postgres, which DB do you have?

[etoulas]

@rouanes: I'm afraid that I cannot help without your specific query. The FROM clause is not mandatory in Postgres, so there's another problem somewhere.

Here's a working example as a reference:

-- Original question
-- It will receive the variables from the derived question.
-- I saved it as quenstion_id = 123 and called it something like "power with arguments"
SELECT power({{base}}, {{exponent}}) AS "base raised to the power of exponent"

Base and exponent are defined as "Number" variables and are "required" with 2 as default values (to keep the showcase sane).

-- Derived question
-- Contains variables which get pushed down to the original question, simply by defining them.
-- The variables here have no other purpose in the derived questions.
WITH
  subquery_args AS (
    -- use the same variable names that you have in the original question.
    SELECT 1 WHERE {{base}} IS NULL AND {{exponent}} IS NULL
  )
, power_with_args AS {{#123}}  -- this is the original question from above!

SELECT *
FROM power_with_args

Make sure to define the variables with the same types as the variables in the original question.

[Netzvamp]

I've coded a solution, that pulls, merges and pushes questions from a local file repository. That way you can work around this issue in an elegant way.
https://discourse.metabase.com/t/mbs-manage-your-metabase-queries-with-local-files/21372
https://github.com/Administerium/mbs

[Abdillah]

Providing manual dropdown options might be a temporary user experience patch for this issue.
Metabase could provide dropdown control that's not dependent on any column, instead data analysts should provide its value themselves. The audience then able to use this more intuitive filter rather than typing custom value..

[MarcSamD]

That's not completely the same case, but still find below my feedback about a closely related issue (single question with nested queries and Field Filters):

First case:

This nested query works because the variable is a type "Text" (so not Field Filter)

Second case:

If I try to convert the variable to a Field Filter, the query will not work because the variable directly refer to the column "PUBLIC.PEOPLE.SOURCE" instead of "table1.SOURCE"

Workaround:

Use another select clause in the where clause so that the Field Filter can directly be used.

Additional comment:

Obvisoulsy in this simple second case, it would have been much easier to just move the WHERE clause into the nested query. However you may need to keep the variable in the last WHERE clause when you are playing with more complex queries with several JOIN.

[BennoDev]

Is this ever happening? Definitely a big miss currently when trying to avoid duplication / work with base queries in large datasets.

[pedrokost]

Unfortunately, the workaround I had (see below) stopped working with Metabase 0.43. It works until 0.42.6. This is currently limiting my abilities to upgrade Metabase. Before upgrading I would have to modify several hundred saved questions and introduce a lot of duplicate SQL in each query.

I am using a hacky workaround for this:

The question which includes the sub-query must re-define the variables from the sub-query.

For example:

WITH subquery_args AS ( SELECT 1 WHERE {{user}} IS NULL),
all_devices AS {{#111}}
SELECT ....

Inside the CTE subquery_args I use the {{user}} variable which is also used inside the subquery {{#111}}. The subquery_args is formed in a way that it does not affect the query results in any way.

This is obviously a hack, which could break in the future (I hope it won't break until there is a proper solution).

@ngnclht1102

[Telofy]

Same, except I already upgraded and just noticed that it's broken.

[Minishlink]

Stumbled upon this problem again today and I can confirm the workaround of re-using the variable in the parent query does not work in latest metabase version (v0.48.6)

[joeyvmason]

Looks like it's been 7 years since this was first reported. For my use case this makes Metabase basically unusable. Is it likely this will be worked on?