SQL Query - custom SQL queries

The system has an editor that allows creation of custom SQL queries.

At time of writing this article, access to this is restricted for security reasons, so it may not be available to every user.

Go to settings >> sql query

Create a new SQL query

Here is an example:

invoice__c.customer_code__c || '-' || coalesce(invoice__c.source_id, invoice__c.owner_id) AS key,
-- person_family_name__c,
sum(legal_monetary_total__c) as amount_spent,
count(*) as invoice_count
FROM slyncyhub.invoice__c

((legal_monetary_total__c > $avg_atf_yr) AND
(invoice__c.issue_date_time__c IS NOT NULL ) AND
(invoice__c.issue_date_time__c >= $period[from]) AND
(invoice__c.issue_date_time__c <= $period[to]) AND
invoice__c.owner_id IN ($datahubs))

invoice__c.customer_code__c || '-' || coalesce(invoice__c.source_id, invoice__c.owner_id)
HAVING count(*) >= 3

sum(legal_monetary_total__c ) DESC

Note:

• that you do not enter the select, where, group statement but you DO have to enter the HAVING
• that variables can be used eg. $period[to]
• that multiple columns can be queried as above and should be given an alias via a AS statement. This allows a single query to create a multi column reprot.
• That there always has to be a unique KEY column!

Once the query is created, it can then be added to a report as a row. To deal with multi column queries, the row type has some inputs. For the above example:

The report could look like this

The amount spent row MUST be like this. Note how the 'value' is set to the same word as the alias in the query

The resulting output would then look like this: