A nifty feature in Zoho Reports is the way it supports relational data modeling. You can create or import a reporting database consisting of multiple tables which have relationships defined between them as in a relational database.

In relational databases world, tables are related using Foreign Key relationships. In Zoho Reports, this is done using the Lookup Column feature where a column in one table points to a column in another table.

Relating tables with Lookup columns

Let me explain this by creating a sample Employee database. This Database consists of two tables Department and Employee. The Department table has two columns - Department Name and Department ID.

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


We define the Department ID in the Department table as the column that is being looked up by the Department ID column of the Employee table. Click on the Edit Design button in the Employee table to define this relationship. Double-clicking on a cell in the Lookup Column lists the different tables & their columns. In our example, we choose the Department table's Department ID.


Joining Tables with Query Table

Database tables which are connected to each other through such relationships allow you to fetch data combining the related tables. In Zoho Reports you can combine the data in such related tables by creating a Query Table which contains a SQL Select Join query. 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 Reports allows you to create any type of reports for analysis and visualization.

Enforces Data Integrity using Cascade-on-Delete

To ensure that the integrity of the data is maintained when data rows get deleted from related tables, Zoho Reports supports Cascade-on-Delete feature. That is, when rows are deleted in a parent table, then all the corresponding rows in the child table will be deleted automatically. In the above example, if any department gets deleted in the Department table (parent table), then all the employees will get deleted automatically from the Employee table (child table). For example, let's say the Accounts department (row) is deleted.


This will result in the corresponding rows in the Employee table getting deleted.


Hope you find these relational modeling features in Zoho Reports useful. Give it a try and let us know your feedback in the comments or mail us at support at zohoreports dot com. We have planned for a lot more enhancements on relational modeling in the future. Stay tuned!