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 Insert icon and select Import Data / New table option.
- Select Query Table from the menu.
- 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 email@example.com and we will help you create and optimize your query tables.