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.

Department table

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

Employee table

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.

Lookup Column

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.

Query table

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.

Delete row

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

On Delete Cascade


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!

  1. leonordlp

    Hi! I’m using this function with my reports,but how can I add more than one table?? for example I’m having 4 tables:ORDERS
    RECEIVING
    PUTAWAY
    PROCESSINGSo this is my statement:SELECT o.OrderID, o.Customer as “Customer”, o.Subcustomer as “Subcustomer”, o.OrderNumber as “OrderNumber”, o.ShippingDate as “ShippingDate”, o.CancelDate as “CancelDate”, Function, “Unit Type”, “Total Units”
    FROM Orders o
    JOIN “RP RECEIVING1” ON “RP RECEIVING1”.OrderNumber = o.OrderNumber
    JOIN “RP PUTAWAY1” ON “RP PUTAWAY1”.OrderNumber = o.OrderNumber
    JOIN “RP PROCESSING1” ON “RP PROCESSING1”.OrderNumber = o.OrderNumberThe thing is it’s only adding data form the “RECEIVING” table… please help!

  2. leonordlp

    Hi! I’m using this function with my reports,but how can I add more than one table?? for example I’m having 4 tables:ORDERS
    RECEIVING
    PUTAWAY
    PROCESSINGSo this is my statement:SELECT o.OrderID, o.Customer as “Customer”, o.Subcustomer as “Subcustomer”, o.OrderNumber as “OrderNumber”, o.ShippingDate as “ShippingDate”, o.CancelDate as “CancelDate”, Function, “Unit Type”, “Total Units”
    FROM Orders o
    JOIN “RP RECEIVING1” ON “RP RECEIVING1”.OrderNumber = o.OrderNumber
    JOIN “RP PUTAWAY1” ON “RP PUTAWAY1”.OrderNumber = o.OrderNumber
    JOIN “RP PROCESSING1” ON “RP PROCESSING1”.OrderNumber = o.OrderNumberThe thing is it’s only adding data form the “RECEIVING” table… please help!

  3. Gabriel

    This is great. Now, if we could just have Zoho Creator be the User Interface builder for these tables ….

  4. Gabriel

    This is great. Now, if we could just have Zoho Creator be the User Interface builder for these tables ….