With a feature like Lookup Columns you can
Once you relate two tables using lookup columns, Zoho Analytics will use this information to enable you create reports by combining columns from these tables, seamlessly without no additional effort.
In a relational database, the related columns from multiple tables are joined using a Foreign key. In Zoho Analytics, the related columns from multiple tables are joined using a Lookup column relationship using cardinality functions. When we join two tables in Zoho Analytics, a Parent-Child relationship will be created between the two tables.
The Employee table has the Employee Names and their respective Employee IDs. The Department to which each Employee belongs to is referred by the third column Department ID.
You can also create a lookup coloumn by following the below steps:
Lookup columns can be used to join tables where you need to retrieve data from two or more tables in a Workspace. In Zoho Analytics, tables with lookup relationship can be joined in the following two ways:
While creating reports, Zoho Analytics provides you with a special feature called Auto-Join which automatically joins tables connected using lookup column. After selecting a table you want to create the report on, Auto-Join feature attempts to determine if it the selected table has any lookup relationship defined with other tables in the database. If it has, Auto-Join will list all the columns of the related tables in Column List panel in Report Designer.
Once listed, you can drag and drop the required columns from the list in to respective shelves to create the reports. Zoho Reports will join the corresponding tables and fetch the required data to generate the report. (You can also join tables using Query Tables. Refer to the topic Joining Tables with Query Table).
Following example shows Department wise Employee count Pivot Table created over the Employee and Department tables using Auto-Join feature.
You can choose to change the join type. You will find a view relationship icon in the report designer while creating reports by joining tables. Click this, the View Relationship Used dialog will open. Select the type of join and click Apply.
Query Table is a feature that enables you to prepare data for easy reporting and analysis. You can combine data from one or more tables in a Workspace and create specific data views using the standard SQL SELECT queries. These data views are similar to tables and you can perform operations such as report creation, sharing, and even create another Query Table over an existing Query Table.
You can create Query Tables for filtering datasets, batching datasets together (union), transforming data, applying SQL query functions, joining datasets and more.
For example, a query combining the Employee and Department tables can be made as shown below.
The example query above joins the Employee & Department tables, getting the department name mapped to each employee. Over the query table that you have created by joining the necessary tables, Zoho Analytics allows you to create any type of reports for analysis and visualization.
Learn how to use the best tools for sales force automation and better customer engagement from Zoho's implementation specialists.
If you'd like a personalized walk-through of our data preparation tool, please request a demo and we'll be happy to show you how to get the best out of Zoho DataPrep.
Watch comprehensive videos on features and other important topics that will help you master Zoho CRM.
Download free eBooks and access a range of topics to get deeper insight on successfully using Zoho CRM.
Sign up for our webinars and learn the Zoho CRM basics, from customization to sales force automation and more.
Make the most of Zoho CRM with these useful tips.