Before You Begin: It’s recommended that you have an understanding of SQL and XML, database management, and Dayforce's database fields to configure relationships.
When you click Relationships in the report designer, the Relationships slide-out panel opens. If you haven't added any entities to the report you’re editing, the panel shows the relationships that exist between the entities in the report's topic.
If you have added entities to the report, Dayforce will have added relationships to create joins between the entities that you added and the existing entities in the topic. Advanced report writers can manage these data relationships and make changes to the joins.
Add Data Relationship Conditions
When entities from outside the report's topic have been added to the report, you can add new conditions to the relationships that were created automatically by Dayforce.
To configure a data relationship condition:
- Click Relationships in the report designer.
- In the Relationships slide-out panel, select the relationship that you want to add a condition to, and click the Add Field button.
- Select a checkbox under Join Type:
- Inner Join: An inner join returns records that have matching values in both tables.
- Left Join: A left join returns all records from the left table (selected in Field), and the matched records from the right (Compare To Field) table. The result is empty from the right side, if there is no match.
- Select a checkbox in the Operator:
- and: Any condition added to the topic with and selected must be met for records to be included in the topic.
- or: Any relationship added to the report with or selected represents one possible relationship value for a record to be eligible and included in the report. Any of the relationships that are included with or selected must be met for the record to be included.
- Select the field for which the relationship is defined.
- Dayforce includes all of the fields under the selected entities, formatted as
[Entity].[Field]. - To filter and search the list, enter any part of or all of the name in the field at the top of the list.
- Select an option in the Operator drop-down list.
- Select an option in the Compare To Field drop-down list.
- The most common relationship configuration is a join-predicate that defines equivalency between the column values of the field and the compare to field.
- Note: Select the Show only Foreign Keys in Compare to Field checkbox at the top of the panel to limit the fields displayed in the Compare To Field drop-down list. A foreign key is a field (or collection of fields) in one table that refers to the primary key in another table. By default, this checkbox is selected, and only fields that can be joined to the selected Field are displayed in the associated Compare To Field drop-down list.
- Click Apply.
Edit Data Relationships
In the report designer, you can edit all data relationships in the report. When you use the Fields > Add Field button to add entities to the report dataset, Dayforce adds the necessary relationships. These new relationships are shown at the top of the list with their conditions expanded so that you can edit them or add conditions. After you save the report, the relationships are displayed in alphabetical order.
To edit a data relationship:
- Click Relationships in the report designer.
- In the Relationships slide-out panel, select the relationship in the list that you need to edit.
- Edit the properties of the relationships. For more information about configuring relationships, see Add Data Relationship Conditions.
Remove Data Relationship Conditions
Before You Begin: You can remove data relationship conditions that have been added manually to the report. You can't remove relationships that exist in the topic, or those that are created automatically by Dayforce when you add entities to the report dataset. To remove these, you have to remove the associated entity from the report dataset.
To remove a data relationship in a report:
- Click Relationships in the report designer.
- In the Relationships slide-out panel, select the relationship that you want to remove.
- Click the trash can icon next to the relationship.