How to pass parameter values through URL

You can even filter specific or all charts & tables on a dashboard by passing parameter values via your dashboard's URL and using them in your SQL query. Ubiq will automatically apply those parameter values in your SQL query, during run time. For example, you may want to filter the chart data by passing a user_id in your dashboard URL. Here's how you can do it

Click on Create Chart button on your project's Charts tab and click SQL mode. You will be directed to the Chart creation page. Select database and type your SQL query

If you're using a drag & drop mode, you can click 'Edit SQL' tab to view your SQL query

Add the keyword [url_parameters] in a WHERE condition of your SQL query as shown. Please note, the keyword must be present in the WHERE condition of your query.

Click Run Query and save it. Please note, the URL parameters are applied only in your dashboard, and not during chart creation/editing. Based on the values passed in your dashboard's URL, ubiq will automatically substitute those filter values in [url_parameters] during run time.

For example, if your dashboard URL is:

https://ubiq.co/dashboards/view/1444

and you pass parameter in your URL like shown below

https://ubiq.co/dashboards/view/1444?user_id=A312

then the keyword [url_parameters] in your SQl query will be replaced by user_id=A312

In the above example, your original chart query

select hire_date,count(*) from employees group by hire_date where [url_parameters]

will become

select hire_date,count(*) from employees group by hire_date where user_id=A312

What happens if my parameter column occurs in multiple tables in the query

If your SQL query uses multiple tables (e.g customers, products, invoice) and your parameter column (e.g ID) occurs in multiple tables, then you can use the specify the required table using the table_name.column_name format in your URL. For example,

For example, if your parameter(e.g ID) occurs im multiple tables, you can it in your URL like shown below

https://ubiq.co/dashboards/view/1444?customer.id=A312

then the keyword [url_parameters] in your SQl query will be replaced by customer.id=A312

In the above example, your original chart query

select hire_date,count(*) from employees group by hire_date where [url_parameters] and customer.employee_id=employee.id

will become

select hire_date,count(*) from employees, customer group by hire_date where customer.id=A312 and customer.employee_id=employee.id

You can combine URL parameters with other conditions in WHERE clause using AND/OR operators

In the following example, we have combined [url_parameters] with another where clause and used the AND operator to combine them

How to pass multiple parameter values

If you pass multiple parameters (e.g user_id and product_id) in your URL like shown below

https://ubiq.co/dashboards/view/1444?user_id=A312&product_id="Beacon"

then the keyword [url_parameters] will be replaced by user_id=A312 and product_id="Beacon". Basically, url_parameters will be substituted with all your parameters combined together using the AND condition

In this case your original chart query

select hire_date,count(*) from employees group by hire_date where [url_parameters]

will become

select hire_date,count(*) from employees group by hire_date where user_id=A312 and product_id="Beacon"

Individually apply URL parameters at different places in your SQL

Sometimes you may want to pass multiple parameters in your URL and apply each parameter at a different place in your SQL query, instead of combining them together as shown above. To individually apply parameters at different places in your query, add the text [url_parameter1] in your query's where clause to apply only 1st column,[url_parameter2] to apply only 2nd column and so on. Suitable for complicated sub-queries or nested queries.

In this case your original chart query

select old_hire_date,count(*) from old_employees group by hire_date where [url_parameter1] group by hire_date
union
select new_hire_date,count(*) from new_employees group by hire_date where [url_parameter2] group by hire_date

will become

select old_hire_date,count(*) from old_employees group by hire_date where user_id=A312 group by hire_date
union
select new_hire_date,count(*) from new_employees group by hire_date where product_id="Beacon" group by hire_date

Please note, if you don't pass any parameter value for a dashboard chart, it will not apply any parameter-based filter while running your query result