Query tables

Query tables

When you have two sources of data (Say, sales and customer details) and you want to create reports by combining data from both these sources, you can use a query table. A query table pulls fields/columns from different tables and combines them to create a new table. Create a query table using a SQL dialect you are comfortable with. We support Oracle, SQL Server, IBM DB2, MySQL, Sybase, Informix, PostgreSQL and ANSI SQL dialects.

Creating a query table
Follow the instructions below to create a new query table. In this example, we will be combining sales data from the "Sales data" table and customer details from the "Customer data" table. 

  •  Click the Create icon on the side panel, and select Query Table under the Create New Table section.

  • The SQL query editor appears. You can now enter your SQL query to create a new query table.
  • To insert columns, select the Insert columns tab. You can alternatively type column names in the editor if you know the column/field names in these tables. 

  • Analytics Plus has a number of in-built SQL functions that can be utilized while constructing the query table. Select the Insert SQL Functions tab to insert functions of your choice. 

  • Once you have entered your SQL query, click Execute Query to create your query table. In our example, we are combining the email column from a customer data table with the sales data. The common column, Customer id is used to join the two tables. 

  • Once your query table is ready, make sure you save before using it to create reports and dashboards. 

Editing a query table 
To edit a query table

  • Open the query table and click the Edit Design button.

  • Here you can change the SQL query used to create that query table.
  • Save the query table.
  • Click View Mode to exit the edit mode. 

Note: You will be unable to edit existing columns in a query table if any of those columns are being used in a report. 

Points to keep in mind

  • Query tables are not a form of report. They should only be used as the base for creating other reports. 
  • Analytics Plus doesn't allow nested queries but it is possible to include the result of one query table in a second query table. As a rule of thumb, limit such operations to three queries. When the nesting goes beyond the third level, loading the reports created from the nested table takes a long time and in turn, affects the application performance. Keeping the nesting to a minimum will ensure a faster report loading time. 
  • Feel free to contact us at analyticsplus-support@manageengine.com and we will help you create and optimize your query tables.




Share this post : FacebookTwitter