Advanced queries

Edalitics places a special focus on ease of use and the abstraction of the technical knowledge required to perform queries against a database. However, sometimes it is necessary to resort to the power of SQL to perform some complex queries. For this, edalitics makes the “SQL Mode” available to advanced users. To access this mode, you must activate the switch located at the top right of the query definition dialog in the configuration of a panel (1)

By activating this switch, you can change from “EDA Mode”, the predetermined edalitics mode, to this “SQL Mode”, where advanced users will be able to define and execute native SQL queries against the database.

Once the “SQL Mode” switch has been activated, the user will be able to define his free SQL query in the text area provided for that purpose (2) and specify one of the main tables of the data model on which the query is based. (3).

Queries: they are free native SQL queries taking into account the following restrictions:

• The queries must be on the same database schema on which the report is made. Database schemas cannot be mixed. In case you need tables of different schemas, we recommend generating views.
• Aliases must be used for tables. In other words, the queries must be defined with the following example structure “select a.field1, a.field2 from table a”.

In order to facilitate advanced interaction with SQL queries, help (3) is available on the same screen that will remind us:

• General recommendations
• How to link the report filters with our SQL query
• Examples of use

Once our sql query (2) has been defined and the main table has been identified, we can now execute the query and use it normally as if it were any other query from any other panel.

The native SQL queries are matched by edalitics and possible data access filters that may exist in the model on which the report is based are injected.

If you want to link the query with the report filters, you must:

• To do so you just have to add: AND ${alias_table.columna_filtrada} in the ‘WHERE’ clause of the query where you want to inject the filter
• If you haven’t added any WHERE clause, add it to the query and do the necessary joins, along with the filter in the format ${alias_table.column_filtered}
• If the table we are filtering by is not in the query, remember that you must add the necessary JOIN clauses to be able to link the filter table with your query

Video tutorial

Examples

Here are some examples:

• Simple query:

SELECT c.customername FROM CUSTOMERSc

• Query with linked report filters:

  • We have a filter in the report for the ‘city’ field of the CUSTOMERS table:

    • Query without linked filters:

      SELECT c.customername FROM CUSTOMERSc WHERE c.customername IN (‘Julia’, ‘John’)

    • Query with linked filters:

      SELECT c.customername FROM CUSTOMERSc WHERE c.customername IN (‘Julia’, ‘john’) AND ${c.city}

• We have a filter in the report for the ‘office_id’ field of the OFFICES table

  • Query without linked filters:

    SELECT e.employee_name FROM EMPLOYEE e WHERE e.employee_name IN (‘Julia’, ‘John’)

  • Query with linked filters: SELECT e.employee_name FROM EMPLOYEE e INNER JOIN OFFICES or ON o.office_id = e.office_id WHERE e.employee_name IN (‘Julia’, ‘John’) AND ${o.office_id}