Reports - Data Relationships

Reports - Data Relationships

Summary

Database relationships are associations between tables created to retrieve data.

• eVolve tab ⮞ Utilities panel ⮞ Report Manager ⮞ Data Relationship ellipsis button in the grid

Usage

Data Profile Alias

Assigning an alias ensures the fields in the report are pointing to the proper data source; this is especially important when using multiple data sources. The Report Designer displays the Primary Profile as eVolveData, any Additional Profiles as eVolveSubData1, eVolveSubData2, etc., and Data Tables as eVolveTable1, eVolveTable2, etc., unaware of the original profile or table's name. If an alias is not assigned, the data profile(s) and table(s) are passed to the Report Designer as a list, and if a profile or table is renamed, this list may be passed in a different order. In that case, when passed to the Report Designer, the Designer will assign a different name, e.g., it may be assigned eVolveSubData2 instead of eVolveSubData1. Additionally, a descriptive name can help to quickly locate parameter(s) from the Field list while in the Designer. Even when using a single data profile or table, it's a good habit to assign an alias because you never know when someone will ask you to change the report, and you will have to add multiple profiles or tables, which breaks all of the fields, calculated fields, and parameters.

Data Relationships

Data Relationships allow data stored in different data profiles to be linked by specifying fields that contain matching values. Some common examples appear below.

Examples

#1 one-to-one-1

In this example, a single one-to-one relationship is defined (highlighted in blue):

The Parent Field would be the ID column in Table A, and the Child Field would be the ID column in Table B.

In this case, the Product Codes in Table A would resolve to the Hanger Types in Table B, e.g., (highlighted in yellow) PC-H00100 would resolve to Band.

#2 one-to-one-2

In this example, multiple one-to-one relationships are defined:

1st (highlighted in blue), the Parent Field would be the ID column in Table A, and the Child Field would be the ID column in Table B.

2nd (highlighted in red), the Parent Field would be the ID column in Table A, and the Child Field would be the Hanger Id column in Table C.

In this case, the Product Codes in Table A would resolve to the Hanger Types in Table B and C, e.g., (highlighted in yellow) PC-H00100 would resolve to Band and PC-H00600 would resolve to Beam Clamp.

#3 one-to-many

In this example, a single one-to-many relationship is defined:

The Parent Field would be the Hanger Type column in Table A, and the Child Field would be the Hanger Type column in Table B.

In this case, all the IDs or Product Codes in Table A would resolve to the Fitting Types or Product Codes found in Table C, e.g., (highlighted in yellow) 001 would resolve to Nut, Bolt, PC-F000212, and PC-F000157.

Window Overview

• Data Profiles Alias Names panel - used to define an alias for data profiles
  • Grid Column Headers
    • Source Name - displays the report's Primary and any Additional Data Profiles
    • Alias - used to define an alias for each associated data profile
• Data Tables Alias Names panel - used to define an alias for data tables
  • Grid Column Headers
    • Source Name - displays the data table(s) associated with the report
    • Alias - used to define an alias for each data table
• Data Relationships panel - defines equality relationships between data sources. This establishes parent-child relationships, which can be used in reports. Each Child Field value must have a unique match in Parent Field. If equality depends on more than a single field match, multiple entries using the same Parent Source and Child Source can be used with different fields to create an 'AND' in the join condition.
  • Grid Column Headers
    • Parent Source menu - displays the report's data profiles and data tables
    • Parent Field menu - populated based on the selected Parent Source, the menu contains a list of all fields in the Parent Source
    • Child Source menu - displays the report's data profiles and data tables
    • Child Field menu - populated based on the selected Child Source, the menu contains a list of all fields in the Child Source
  • Refresh Sources button - forces a refresh of the selections available within the Data Relationships grid. This may be necessary for situations where selections are out of sync.
  • Constraint Options panel
    • Enforce radio button - When enabled, an error will result when all Child Field fields do not exist within Parent Field.
    • Do not enforce radio button panel - When enabled, a no error will result when all Child Field fields do not exist within Parent Field.
      
      • Create table for orphaned child records checkbox - When enabled, records within Child Source that are not in Parent Source are placed within a separate data source and passed to the report.

Relevant Articles

• Report Manager
• Accessing and displaying modeled data