Setting up Relations

Setup Guide for Database Relations in Dotnet Report Application

This guide will walk you through the process of setting up database relations in your Dotnet Report application. The steps outlined below will enable you to create dynamic queries, auto-generate joins, and manage the relationships between tables effectively. These relations are essential to ensure accurate report generation across multiple tables.

Prerequisites:

Before you begin setting up the database relations, ensure the following steps have been completed:

1. Database is connected and Tables are loaded/saved.
   
2. Tables have primary and foreign keys:
   
   • Dotnet Report automatically identifies primary and foreign keys when tables are connected. These keys are essential for setting up relationships and generating accurate queries.
     

Step-by-Step Guide for Setting Up Relations in Dotnet Report:

Step 1: Open the Relations Tab

Navigate to Manage Database > Relations to start setting up the relationships between the tables. This is where you will define and manage the joins required for accurate data retrieval in your reports.

Step 2: Add a New Join

To create a new relationship between two tables:

1. Click on the Add New Join button.

2. A form will appear where you will need to specify the following:
   
   • Primary Table: Select the primary table in this relationship. This will be the table from which data is pulled.
     
   • Field/Column of Primary Table: Choose the specific column in the primary table that will be used for the join.
     
   • Join Type: Choose the type of join you wish to create. The available options are:
     
     • INNER JOIN: Returns rows when there is a match in both tables.
       
     • LEFT JOIN: Returns all rows from the left table and matching rows from the right table.
       
     • RIGHT JOIN: Returns all rows from the right table and matching rows from the left table.
       
   • Join Table: Select the table to join with the primary table.
     
   • Field/Column of Join Table: Choose the field/column in the join table that will match the primary table’s field.
     

Step 3: Configure Auto Join (Optional)

1. If your schema has fields ending in ‘Id’ (e.g., userId, orderId), Dotnet Report can automatically suggest joins for these fields.

2. Click the Auto Join button to have Dotnet Report suggest joins based on matching ‘Id’ columns across tables.
   
3. A prompt will appear asking if you want to add suggested joins. Click Yes to auto-generate multiple relations for the fields ending in ‘Id’. This option helps streamline the process and ensures consistency across your schema.

Step 4: Save Joins

After confirming that the joins are correctly set up, click the Save Joins button. This action will finalize the relationship setup in Dotnet Report, and it will use these joins to generate the dynamic queries required for your reports.

Step 5: Visualize Joins

1. Once you’ve set up the joins, click the Visualize Join button. This will generate a diagram showing how the tables are connected via their respective joins.
   
2. You can view the relationship between your tables, allowing you to verify that the joins are configured correctly before finalizing them.
   

Step 6: Export/Import Joins (Optional)

• Export Joins: If you want to save your join configuration for future use or share between Data Connections, click the Export Join button. This will export the join configuration as a JSON file.
  
• Import Joins: If you have a previously exported join configuration, you can restore it by clicking the Import Join button and uploading the saved JSON file.
  

After Completing the Setup

Once you have set up the relationships, Dotnet Report will generate the necessary dynamic queries to pull data from the connected tables, using the joins you’ve defined. This will allow you to create comprehensive reports that involve data from multiple tables and join them in a way that maintains the integrity of the database schema.

Next Steps: Working with Stored Procedures

After setting up the relations, the next step is to integrate stored procedures into Dotnet Report. Stored procedures allow you to encapsulate business logic and optimize queries, making them reusable and more efficient.

For the next guide, we will discuss how to configure and use stored procedures in Dotnet Report to enhance your reporting capabilities.