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  

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}},'%') ]]

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.