Can I join multiple datasets in a single report?
In Bold Reports, each dataset is associated with a single data source. While the platform does not support traditional SQL-style joins directly between datasets originating from different data sources within the report designer, users can achieve data integration through alternative methods, including expression-based functions. This design promotes data integrity, enhances performance, and upholds security by treating each data source as an isolated entity.
Reasons for Limited Direct Joins Across Data Sources
Bold Reports, like many reporting platforms, maintains data sources as independent components. This approach results in the following characteristics:
- Isolated Contexts: Datasets from different sources lack inherent relationships, preventing native merging or linking at the query level.
- Autonomous Operations: Each dataset operates independently, limiting cross-source integration in the query designer.
- Technical Considerations: Factors such as performance optimization, security measures, and schema compatibility restrict built-in joins between datasets from different sources.
Alternative Approaches for Data Integration
Although direct query-level joins across data sources are not available, Bold Reports provides several effective strategies to combine and present data:
1. Use Lookup Functions for Data Correlation
Use the Lookup and LookupSet functions in report expressions to retrieve and combine values from multiple datasets within a single data region (e.g., a table or matrix). These functions support 1-1 or 1-N relationships without altering the underlying data sources.
- Lookup Function: Retrieves a single value from another dataset based on a matching key.
Example syntax:
=Lookup(source_expression, destination_expression, result_expression, DatasetName)
The Lookup function syntax is
| Syntax | Description |
|---|---|
| source_expression | The value from the primary dataset used as the lookup key. |
| destination_expression | The value from the secondary dataset that matches the lookup key. |
| result_expression | The field to be retrieved from the secondary dataset. |
| DatasetName | The name of the secondary dataset. |
The following displays a product name from Dataset2 by matching ProductID from Dataset1
=Lookup(Fields!ProductID.Value, Fields!ProductID.Value, Fields!ProductName.Value, "Dataset1")
- LookupSet Function: Retrieves a set of matching values, often combined with Join for display.
Example syntax:
=Join(LookupSet(source_expression, destination_expression, result_expression, DatasetName), ", ")
The following concatenates multiple employee names linked to a project:
=Join(LookupSet(Fields!ProjectID.Value, Fields!ProjectID.Value, Fields!EmployeeName.Value, "Dataset1"), ", ")
These functions allow you to enrich report elements (e.g., tables, charts) with data from different databases dynamically during rendering.
2. Combine Data into One Source
Perform the join outside Bold Reports using database features (e.g., SQL Server linked servers) or ETL tools like Bold ETL, then expose the unified result as a single dataset.
Conclusion
The restriction on direct joins between datasets from different data sources in Bold Reports is a deliberate design choice to prioritize reliability, performance, and security. By leveraging expression-based lookups, pre-import consolidation, centralized databases, users can effectively integrate and visualize comprehensive data within a single report.
Future Updates
We understand that combining datasets from multiple data sources is a valuable feature for many users. To stay updated on potential enhancements or to share your feedback on this capability, please visit our feedback portal Create a Combined Dataset from Multiple Data Sources.
