Report on Multiple Tables

Data required for a report does not always come from a single table, there are scenarios where multiple tables are needed.

Reports on two tables with direct relationship

In this scenario, data needed for a report does not come from one single table, but from a primary table and a related one. The primary table must have a field to contain the id value of the related table. Both tables simply need to be selected for the report to work.

Fig. 392 ProductID Ordered By Discount

For example, table Order Details in Northwind database stores the list of products (ProductID) together with the discount percentage in each order. This data allows for a report on products with the highest discount percentage. However, that report can only show the meaningless product IDs (Fig. 392).

Fig. 393 ProductName Ordered By Discount

The report will be more useful if it includes the product name (stored inside table Products) (Fig. 393). To do that:

1. Select both tables Order Details and Products in Middle Panel.

2. Define relationship between two tables if needed (See Add and remove relationships) (in this case the relationship has already been defined and automatically populated). (Fig. 394)

3. Add a Grid report part.

4. In Columns box, use the ProductName field instead of ProductID.

   In Fig. 393, the ProductID is intentionally kept to be compared with the previous figure.

   

   Fig. 394 Order Details - Products Relationship
   

Reports on two tables with relationships via a third table

Sometimes, data in two tables are related to each other through a third table (many-to-many relationship). These two tables have no direct relationship, so a report with these two tables only will not work: all three tables need to be selected.

Note

A rule of thumb to detect this scenario is that the third table usually has only two fields, each contains the id value of either related table.

For example, a report is needed for the total sales per year in each of two cities Rio de Janeiro and Sao Paulo. The city values are in City field in Customers table while the sales are calculated from UnitPrice, Quantity and Discount fields in Order Details table. These tables are linked to each other by the Orders table although no field from this table is needed for the calculation. To design this report:

1. Select all three tables Customers, Order Details and Orders.

2. Define relationships if needed (See Add and remove relationships) (in this case the relationship has already been defined and automatically populated).

3. Drag the City field in Customers table into the Filter box.

4. Click the City field in Filter box to open Filter Properties.

5. Go to Filter Settings group, Filter Operator drop-down, select Equivalence then Equals (Manual Entry) then Multiple.

6. Type Rio de Janeiro and press Enter.

7. Type Sao Paulo and press Enter.

8. In Middle Panel, click Add Calculated Field to open Add Calculated Field pop-up.

9. Name the field “Sales”.

10. Build the expresion

    [Northwind].[dbo].[Order Details].[UnitPrice]
    * [Northwind].[dbo].[Order Details].[Quantity]
    -
    [Northwind].[dbo].[Order Details].[UnitPrice]
    * [Northwind].[dbo].[Order Details].[Quantity]
    * [Northwind].[dbo].[Order Details].[Discount]
    

11. Click Ok to save the calculated field.

12. Add a Grid report part.

13. In Columns box, add the City field and the Sales calculated field.

14. Select City field in Columns box to open Field Properties.

15. Select “Group” as Function.

16. Select Sales field in Columns box to open Field Properties.

17. Select “Sum” as Function.

18. Select descending as Sort.