How to Use Native Query in BI Reporting

SQL experience is required to write questions in native query in BI Reporting.

The Simpro Premium fields and tables for writing SQL queries are available in Fields for Native Query.

Follow the steps below to build a Native query in BI Reporting:

1. Go to Reports > BI Reporting and click Ask question > Native query.
2. In the New question box, enter custom SQL statement. The relevant keywords are displayed as you type the SQL statement for quick reference.
   
3. Click Get Answer to display the results.

Detailed below are a few examples of basic SQL queries:

• To show all columns from a particular table:
  select * from simpro_customers

  
  Here, simpro_customers is the name of the table. For the entire list of available tables, see Fields for Native Query.

• To show only specific fields from a table:
  select customer_id, entity_type, business_name from simpro_customers
  
  This query shows the customer_id, entity_type and business_name fields from the simpro_customers table.
  
• To filter the results further, you can specify which rows to show based on a specific criteria:
  select customer_id, entity_type, business_name from simpro_customers where customer_id=2
  
  This query displays only the customer that has Customer ID as 2.
• To rename columns in the results, use the as keyword and specify a different name.
  select customer_id, entity_type, business_name as "name" from simpro_customers where customer_id=2
  
  This query renames the business_name column to "name". The inverted commas around the "name" field helps the native query to differentiate between the term name and the name keyword.