Contact support

Modules & Reporting

Advanced Reporting 8: Combining Multiple Data Sources in a Report

Introduction

This guide explains how to combine data from multiple sources into one federated data source and use it effectively in your reports. This enables reports to include fields not available in the primary data source, such as employee location name or employment type name.

Initial Setup

Start with a basic report displaying employee details. The report is initially bound to a single data source (Synergy Workforce JSON API).

Identifying Missing Fields

The primary data source includes fields like EmployeeLocationID and EmploymentTypeID, but lacks the names for these IDs. These names are available via separate endpoints in the Synergy Workforce API.

Adding Additional Data Sources

  1. Go to Menu > Add Data Source.
  2. Select JSON and search for Employment Types.
  3. Repeat the process to add the Employee Locations data source.

Creating a Federated Data Source

  1. In the data source design interface, drag and drop the additional data sources onto the surface.
  2. Create relationships:
    • Drag EmploymentTypeID from the employee source to the ID field of the Employment Types data source.
    • Configure the relationship as Left Outer Join (to allow for optional values).
    • Do the same for EmployeeLocationID and the Employee Locations data source.

Selecting Columns for Output

  • Select all columns from the Employees data source.
  • From Employment Types: Select Name and alias it as Employment Type.
  • From Employee Locations: Select DisplayName and alias it as Employee Location.

Saving the Federated Source

  1. Click OK to save the data source.
  2. Name it something meaningful (e.g., EmployeesCombinedDataSource).

Binding the Report to the Federated Data Source

  1. Go to Report Properties > Data Source.
  2. Select EmployeeFederatedDataSource.
  3. Choose EmployeesCombinedDataSource as the Data Member.

Adding New Fields to the Report

  1. Insert two new columns in the report layout (header and detail sections).
  2. Label the header columns: Employment Type and Employee Location.
  3. Bind the detail cells to the new fields from the federated data source.

Final Adjustments

  • Resize columns to maintain layout clarity.
  • Optionally, adjust header fonts for better readability.

Previewing the Report

  1. Save the report.
  2. Run it by selecting parameters and clicking Submit.
  3. Verify that: Employment Type and Employee Location fields are visible and populated.

Conclusion

You've now successfully:

  • Added new data sources.
  • Created a federated data source with joins.
  • Included additional fields in the report.

This process allows for dynamic, richer reporting using joined datasets.