In real life, some times you need to work with 2 or more tables to make a report. Assume that, you have 3 tables tblCustomers, tblProducts and tblOrdersData. From these 3 tables you need to create a report Customer vs Product - Sales Quantity.
Image 1: tblCustomers Table
Image 2: tblProducts Table
Image 3: tblOrdersData Table
Cust_ID is the primary key of tblCustomers table, Product_ID is the primary key of tblProducts and Record_Num is the primary key of tblOrdersData.
Why you need to create a relationship between tables?
If you do not create relations between tables, then you have to work some extra time for make extra helping reports and then finally create the target report which you want to create. But if you create a relation between 2 tables then it would be very easier to get the target data within just few seconds. And you can create your desire reports very easily.
In others database program like, SQL, Access, Oracle you can create table relationship among few tables with a Primary Key and Foreign Key.
What is Primary Key and Foreign Key?
Primary Key:
It locates in the Main table and it contains the unique value of a whole column in the Main table. No value will found as duplicate in the Main table. Example: Product_ID column is set as Primary Key for the tblProducts main table, where each code found as single in each row.
Foreign key:
It locates in the New table and it contains the unique value many times. Example: Product_ID column is set as Foreign Key for the tblOrdersData table, where each Product_ID can be found 2 or more times. If it is confusing to you to understand, then what will be the primary key for tblOrdersData table? Well, it would be Record_Num.
Image 4: In tblOrdersData table, Order_Number is Primary Key and Product_ID and Cust_ID is Foreign Key.
How to build relationship between tables?
Step 1: At first you need to import your 3 tables into PowerPivot window. In this part if you wish, your data will automatically changed and updated, then you can import as Linked Table otherwise simply use Copy ~ Paste method.
Image 5: Imported 3 tables in PowerPivot window as Linked Table
Step 2: You can view these 3 tables through Diagram View. To do this just click on Diagram View from Home in PowerPivot window.
Step 3: Click on Design tab in PowerPivot window and Click on Create Relationship icon from Relationship group.
Step 5: Select tblOrdersData from Table: and select Cust_ID from Column: for applying the relationship command on it. Now to set the relationship, Select tblCustomers from Related Lookup Table and Cust_ID as Related Lookup Column. And then click on Create button.
Image 6: Settings of Create Relationship dialog box
Step 6: A relationship will successfully create like below image:
Image 7: Created 1 relation between tblCustomers and tblOrdersData
Step 7: Create another relationship between tblProducts and tblOrdersData table where tblOrdersData will be selected from Table: and Product_ID will be selected from Column: for applying the relationship command on it. Select tblProducts from Related Lookup Table and Product_ID from Related Lookup Column.
Image 8: Settings of Create Relationship dialog box for 2nd relation
Now the relationship diagram will looks like below image:
Image 9: Created Complete relationship in between 3 tables
Step 8: Now it's time to play. Open the PivotTable from PowerPivot window by clicking on Home menu and then click on PivotTable and again click on PivotTable.
Step 9: While Insert Pivot dialog box appeared, Select New Worksheet and click on Ok.
Image 10: Selected New Worksheet for new PivotTable
Step 10: You will see all 3 tables are already loaded in PivotTable.
Image 11: Loaded 3 tables
Step 11: We want to create Customer vs Product - Sales Quantity Report from these 3 tables within a few seconds. Click on tblCustomers table and move the Cust_Name in ROWS section. Again click on tblProducts table and move the Product_Name in COLUMNS section. Now Click on tblOrdersData table and move the Order_Quantity in VALUES section. And your reports are generated in seconds like below image:
Image 12: Final settings in PivotTable
Image 13: The report as we want
You can now change the design of this report and share it. As I have used the Linked Table, so I don't need to update the PowerPivot tables. It will update automatically if my source data in the same Excel Workbook updated.