Content
- Introduction
- Video Overview
- Getting Started
- Creating the Table
- Linking Query Tables to Other Tables
- Create Charts and Tables based off Query Tables
Introduction
Zoho Reports (http://reports.zoho.com) is a 3rd party custom report and dashboard builder available as an optional add-on item. If your agency doesn’t currently have this option and you are interested in learning more, please contact sales@extendedreach.com.
Within Zoho, you have the ability to create many different reports based off of the data that comes from extendedReach. Query tables are a more advanced way to organize columns and rows of data in a database. Query tables use SQL, which stands for Structured Query Language and lets you access and manipulate databases.
To learn more about SQL, check out the following SQL Tutorial Guide.
Video Overview
Getting Started
To get started creating a query table, click on the green Create New button and select Import Data/New Table. From there, click on Query Table.
Image: “Create a new query table”
When you select query table, Zoho will provide an example of an SQL statement and the associate keywords. The statement is seen on Row 1, the keywords are in all caps (SELECT and FROM).
Image: “Example of new query table”
| Query Options | Description |
|---|---|
| Execute Query | Will create a query table based on the statements and keywords used in the query box |
| Clear Query | Will clear all content in the query box |
Creating the table
You can either enter in the field names manually, or you can click on them using the Insert Columns tab. If you are entering in field names, be sure to include the name in quotations. E.g. “PlacementType”. When you use the Insert Columns tab and click on the fields you want, it will automatically add the quotations for you.
Image: “Insert Columns”
To add any equations or functions, you can use the Insert SQL Functions tab. Similar to the Insert Columns tab, you can either click on the function or manually type it in. Hover over the name of the function to show a brief description of it as well as an example.
Image: “Insert Functions”
Image: “Hover over for example”
Once you have filled out your query table with the functions and fields that you want to use, click on the Execute Query button. If there are no errors in your table, your table will appear under the Executed Result tab. If there were errors, the Executed Result tab will provide error details – detailing which line has the error and what the encountered problem was.
Image: “Table successfully executed”
Image: “Table unsuccessfully executed”
When your table has been successfully executed, save and click on the View Mode button to view the entire table.
Image: “View mode”
Image: “Full table in view mode”
Linking Query Tables to Other Tables
To link a query table to a standard extendedReach table, right click on the column you wish to link and select “Change to Lookup Column”. This can also be done by clicking on the Column Properties button located at the top of the table and selecting “Change to Lookup Column”.
Image: “Right click and Change to Lookup Column”
Image: “Use Column Properties to Change to Lookup Column”
Once selected, Zoho will prompt you to choose the name of the field and the associated table to link the column to. Select the appropriate table and then press Ok.
Image: “Change to Lookup Column selection”
Create Charts and Tables based off Query Tables
Once the query table has been created and saved, it can be used to create pivot tables and charts as well. To get started, click on the green Create New button, go to New Report, and select the type of report you’d like to create. When you are prompted to select the base table, the query table you created will show up as an option. Once selected, you will be able to create a chart or pivot table based off of the fields in the query table.
Image: “Use a Query Table as a base table”
