After you have created a database connection, now its time to create a report using SQL. There are 2 ways you can create a report.

Report Builder Mode
Allows you to search for a table/view and select the desired columns for the report. It also allows you to setup report filters to enable users to filter data at runtime. Currently Report builder mode supports select data from one table/view. If you have a requirement to get data by joining multiple tables, then you can use the SQL Report mode.
SQL Mode
Allows you to write your own SQL.

In this short course, you will learn how to create a report using SQL. You can use the same SQL you have been using in your reporting systems. Just copy and paste it in the Kloudio SQL editor. You can also add filters to your SQL.

Here are the steps:

Step1: Select SQL Report Option
Click on the Reports link in the left side bar and then select the SQL Report option as shown below:

Step2: Write SQL
Select a connection from the connections dropdown.


Start writing your SQL. Incase if you want to lookup for a table or column, you can use the left side panel to search for tables/columns. This side panel is for reference purpose only.

Once you are done writing your sql, you can click on the Test button to test the query you have written. You will get the preview data if everything goes fine. If there is an error, error message will be shown in the console below the Query Editor.


Step3<optional>: Filters
Now that you have written a sql and tested it out, its time to add filters to your SQL query. Users can use these filters to dynamically filter the data in google sheets/excel.


Filters are added by mentioning the filter names inside the double curly braces. 

As soon as you add the filter in the query editor, you will see that filter in the Filters tab as shown below:

Now when you click on the Test button you will be asked to enter a value for the filter you just added.  That's it, you just added a filter to your SQL query.

But wait, most of the times users would like to select a value from a list of values instead of manually typing it. Let's see how to convert the filter we added to a select list of values.

Change the Display As option from to Text to  Select as shown below:

Once you change the value to Select, a button appear next to the select. Click on that button to enter a list of values or a SQL query which returns a list of values.

Static List of Values
 
If you want to display a static list of values to choose from, then you can simply enter comma separated values in the List of Values popup dialog box as shown below:

Dynamic List of Values
If you want to display a dynamic list of values from a database table, you can write a SQL in the List of Values popup dialog box. The syntax is shown below:

The sql shown below should select 2 values, one for display and one for value. The display field is what is shown to the users and value field is returned as the filter value. 

At times you will need your SQL's where condition to check for an ID, in that case you can select an ID field as the value column.

For example:
select project_name as display, project_id as value from projects

In this project_id is returned as the filter value, which will be used in the where condition of your SQL.

Once you have defined the filter, you can click on the Test button to test it out.

Step4<Experimental>:
In the previous step we saw how to add a select filter. Select filters allows only one value to be selected at a time. But if you want your users to select multiple filter values, you can choose the Multi-Select option from the Display As dropdown.

You will need to change your query a bit to make use of this multi-select option. 

For example:

select * from amzn_weekly_prod_sales where vendor_name = {{vendor_name}} 

should be changed to

select * from amzn_weekly_prod_sales where vendor_name in ( {{vendor_name}})

Please note, as of now multi-select is not supported in google sheets. It is available only in the Kloudio Excel Addon.

Did this answer your question?