Metabase Filters example

Question

I am using metabase integrated with Mysql for reporting purposes. I went through documentation but was unable to find any example explaining how to implement filters in SQL based questions.

The only example I found was regarding Date Range and Field Filters but not of Text and Numbers.

Can anyone provide documentation or any example on how to use Text filters.

I am using metabase version v0.24.2

The Query I am trying to run is this

 SELECT  @a:=@a+1 "Serial Number", ssk_transaction.transactionId AS "TranId",   
t2.typeName AS "Transaction Type",  
ssk_transaction.createdTime AS "GenDate", t3.deviceName AS "Machine Name",  
 t3.deviceLocation AS "Machine Location", t9.eventApiName AS 'API Name' ,  
t8.vendorResultCode AS 'Last API Response',  
(SELECT createdTime FROM ssk_transaction_event_detail t4 WHERE t4.transactionId  
 = ssk_transaction.transactionId ORDER BY id DESC LIMIT 1) AS "Last API Called",  
(SELECT IFNULL(SUM(t5.itemName * t4.itemCount), 0) FROM  
 ssk_transaction_cash_detail t4  
LEFT JOIN ssk_inventory_item t5 ON (t4.itemId = t5.itemId)  
LEFT JOIN ssk_inventory_category t10 ON (t5.categoryId = t10.categoryId)  
WHERE t4.transactionId = ssk_transaction.transactionId AND t10.categoryName =   'acceptor') "Cash In",  
(SELECT IFNULL(SUM(t5.itemName * t4.itemCount), 0) FROM       ssk_transaction_cash_detail t4  
LEFT JOIN ssk_inventory_item t5 ON (t4.itemId = t5.itemId)  
LEFT JOIN ssk_inventory_category t10 ON (t5.categoryId = t10.categoryId)  
WHERE t4.transactionId = ssk_transaction.transactionId AND t10.categoryName =   'dispenser') "Cash Returned",  
IFNULL((SELECT "Cash In"), 0) - IFNULL((SELECT "Cash Returned"), 0) AS "Amount   of Activity",  
(SELECT t8.vendorResultCode FROM ssk_transaction_event_detail t8 WHERE       t8.transactionId = ssk_transaction.transactionId AND t8.eventId = 6 ORDER BY id       DESC LIMIT 1) AS "Sim Status",  
'Completed' AS "Transaction Status",  
ssk_transaction.customerMsisdn AS MSISDN,  
ssk_transaction.customerCNIC AS CNIC  
FROM  (SELECT @a:=0) initvars, ssk_transaction  
LEFT JOIN ssk_transaction_type t2 ON (ssk_transaction.typeId = t2.typeId)  
LEFT JOIN ssk_device t3 ON (ssk_transaction.deviceUUID = t3.deviceUUID)  
LEFT JOIN ssk_transaction_cash_detail t6 ON (ssk_transaction.transactionId =       t6.transactionId )  
LEFT JOIN ssk_inventory_item t7 ON (t6.itemId = t7.itemId)  
LEFT JOIN ssk_transaction_event_detail t8 ON (ssk_transaction.transactionId =   t8.transactionId AND t8.eventId = 10)  
LEFT JOIN ssk_transaction_event t9 ON (t9.eventId = t8.eventId)  
WHERE {{created_at}} AND {{id}} [[AND ssk_transaction.customerMsisdn=       {{msisdn}}]] AND {{cnic}} and  t2.typeId = 3 AND t8.eventId = 10 AND       t8.vendorResultCode = '405000000'  
GROUP BY ssk_transaction.transactionId  
ORDER BY ssk_transaction.createdTime ASC  

Caramelize

It is missing the column alias in your filter. The variables {{createdAt}}, {{id}} and {{cnic}} are not being compared with their respective columns. It should be something like: createdAt = {{created_at}} AND id = {{id}} [[AND ssk_transaction.customerMsisdn= {{msisdn}}]] AND cnic = {{cnic}} But I'm assuming the variables match the column names, so you should fix your query to use the right columns. – Snider

the rest of the varialbles are set to field filter and are working properly only the text ones are giving me a problem – Caramelize

Answer 1

Open a issue on github page under the version number your are using. The contributors will help you with your query or even providing the requested documentation / wiki.

Maybe this could help you :

try to use CONCAT('%',{{variable}},'%') such as:

WHERE 1=1 [[ AND test LIKE CONCAT('%',{{variable}},'%') ]]

Amandie

I already opened an issue on their github before posting here, no reply there as well and also i tried the solution your giving .. still doesnt work. – Caramelize

Answer 2

To create a variable just write your query and define placeholders for your variables in the format {{variablename}}, when you do this Metabase will automatically show the Variables panel on the right and you must choose the type of this variable (you can also mark the variable as required if you provide a default value).

You should not escape the variable placeholder in your query (that is why CONCAT('%',{{variable}},'%') does not work) and you can mark a whole expression as optional surrounding it with double-brackets.

The image bellow gives one example for the query:

select * from pg_tables where schemaname = {{schemaname}} [[ and tablename = {{tablename}} ]]

In this example, the whole tablename filter will be ignored if you don't provide a value for the tablename variable. And note that both variables are marked with type text.

Snider

and this query uses a Postgres database., so sorry for not giving an example using MySQL. – Snider

Let me try using this and will get back to you shortly – Caramelize

how do i invite you to chat? i already tried using ur way still getting the same issue, when ever i put any value in the filter it shows nothing. i tried again this time as well and the same issue... i have a snapshot i would like to show u – Caramelize

could you paste the query you are trying to parametrize here? – Snider

Added the query in the question as it was too long to copy paste here – Caramelize

I added a comment in your question. But I'm not going to edit this question since it already explains how to configure the variables correctly. – Snider

i have added the snapshot of metabase to make things clearer – Caramelize

Answer 3

To add filters on sql based(Native query) questions, after you generate the sql result, click the compass on the right hand bottom and then click "Analyze the results of this query". The filter options will appear on the top.

You can then save this as a different question so that the filter screen always appears.

Azole

Answer 4

It would be helpful to know what data type ssk_transaction.customerMsisdn is. I am wondering if it is some sort of int type, in which cast you need to cast it to text before you can compare it to your text filter.

[[ AND ssk_transaction.customerMsisdn::text = {{msisdn}} ]]

Intercostal