One to Many Relationships and Duplicate Data
Adding data sources that have a one-to-many relationship often confuse the inexperienced user when they see data duplicated displayed in more than one row of a report. However, this result is entirely expected when adding data sources that have one-to-many relationships. For example, adding Clients and Assignments into the same report will have this effect because a Client can have more than one Assignment.
- If every Assignment should display in the report, use "separators" to prevent the Client name from displaying more than once.
- If every Assignment does not need to display but the table is still needed for filtering purposes, use "grouping" to remedy the display.
- If every Assignment does not need to display and the table is not needed, remove it from the data sources.
Too Many Tables
If you add too many tables to the report, the loading process of the report may be very slow or cannot finish. Although there is not a restrictive limit of the number of tables, experienced designers know that at some point the outcome may not warrant the design. Trying to add too many data sources and crowding a huge number of fields from different tables into one report may seem like a desired outcome, but the results may not.
- Reduce the number of data sources.
- Create more than one report to obtain the desired outcome.
Cross Branch versus One Branch
In a multi-Branch site, most reports are meant to display data from one Branch. Using the [Branch] data source in the design automatically accomplishes this because it contains a field called [braID]. When the report application sees this field, it automatically filters based on the Branch where the report is launched from.
InSight Pro users can create reports to display data across some or all Branches. Instead of using the [Branch] data source, add the [BranchMulti] instead. This data source does not have the [braID] field and prevents the automatic trigger to filter the data to the current Branch.
Client and Staff Shared Across Branches
Client and Staff data can be shared across more than one Branch in a Multi-Branch site. If not linked correctly, data could display for more than one Branch. Be sure to link both the Primary Key for the table and the Branch to related tables.
- Example: Linking the [Client] to the [Assignments] data sources, link the [cliID] and the [BranchID] to both. You will add two links for one set of data sources.
Adding Same Table more Than Once
In most designs, there is no need to add a data source to the relationships more than once. Doing so can create duplicate data rows and the appearance of duplicate data when in actuality, the duplication does not exist in the database. You will see an "Alias" data source name automatically created when adding the data source more than once.
- Remove the relationship and start again.
- Be sure to be aware of what data sources are added to the left side of the relationships and those that are on the right. Adding the same data source to the left side more than once creates the duplication and "alias" issue.
Rick Stern
Comments