SQL query, dynamic lookup parameters

1

I am creating SQL query inking multiple tables.
I would like to enable Filter on this query based on the different database lookup.
Concretely I want to have lookup of active company names (companies with invoices in last year), and use this as a filter for main query (all invoice details for a year sql query with joins).

how to achieve this.

Thanx,
D

2

Hi @dinon
You’re using certain words that makes me think you’re asking for something different, but it sounds like a Field Filter is what you’re after.
https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html#the-field-filter-variable-type

Basically something like this - and link the variable to company.name:

SELECT invoice.* FROM invoice
LEFT JOIN company ON company.id=invoice.company_id
WHERE {{company_name}}

There should be a post about Field Filters maybe tomorrow:
https://www.metabase.com/blog/

3

HI,
sorry for possible confusion but looks like everyone is trying is using different wording.

Let me share with you concrete problem that I am trying to solve, I have a SQL Server function that is requiring 2 parameters:

  • Date for report period
  • CompanyID, integer for companyID

31
312816×2622 406 KB

when I try to trace this in SQL profiler i can see that metabase is sending out CompanyID as a text field with company name, how can I get ID but still have company names listed in the parameter fileds that user can choose from?

This is from SQL Server profiler after I try to refresh from metabase:
SELECT funGenericCompanyDaysCalc_1.*
FROM dbo.funGenericCompanyDaysCalc(@P0,"dbo"."Company"."CompanyName1" IN (@P1)) AS funGenericCompanyDaysCalc_1
Order By SeamanName ',N'@P0 date,@P1 nvarchar(4000)','2020-08-13',N'ATLAS d.d.'

If I change FieldFilter to ID field in data-table it is working but for users it is listing CompanyID's not Company names to choose from

Also, for FieldFilters is there possibility to use complex queries as a base, for example I would need to use something like this for CompanyID in above case (this is producing key value pair table) so could be used as a generic solution:
SELECT InvoiceFeeAssignments.CompanyID AS Value, Company.CompanyName1 AS Text FROM InvoiceFeeAssignments INNER JOIN Company ON InvoiceFeeAssignments.CompanyID = Company.CompanyID GROUP BY InvoiceFeeAssignments.CompanyID, Company.CompanyName1

4

@dinon Uhh, yeah, you cannot do that - Field Filters inserts SQL, which doesn’t play with the function call you’re doing.
You would have to do something like:

SELECT funGenericCompanyDaysCalc_1.*
FROM dbo.funGenericCompanyDaysCalc( {{rpt_period}}, (SELECT CompanyID FROM dbo.Company WHERE {{company_id}}) ) AS funGenericCompanyDaysCalc_1
Order By SeamanName

And if rpt_period is a range/period, then you would either need a lookup table and do a sub-select with min/max, or have two filter, a start and end date.

5

Ok,
many thanks now I understand how to do create FieldFilters, but If I want to use something specific I would need to create a separate query in database and use it as a base for FieldFilter and do it as a subquery within larger query.

Is there option to suggest his key/value pair strategy as enhancement?

Regards,
D

6

@dinon
I’m not quite sure what you’re asking for, but I think it’s these issues - upvote by clicking :+1: on the first post of each issue:
https://github.com/metabase/metabase/issues/5245
https://github.com/metabase/metabase/issues/6820