Combine Data from Multiple Datasets Using the Lookup Function in Bold Reports

Bold Reports simplifies the process of integrating data from multiple sources with the Lookup function. This feature allows you to combine related data from different datasets, created from distinct data sources, into a single report.

This step-by-step guide explains how to merge data from multiple datasets and display it in a table.

Steps to Combine Data from Multiple Datasets

1. Create Two Datasets

• Define and create two datasets based on your reporting requirements.
• Ensure that both datasets contain fields that can serve as keys for the lookup operation.
  
  

2. Add a Tablix to Your Report

• Drag and drop a Tablix control into your report designer canvas.
• Bind the Tablix to the primary dataset and select the relevant fields to display.
  
  

3. Use the Lookup Function to Retrieve Fields from Another Dataset

• To fetch fields from the secondary dataset, use the Lookup function.
• Click the pencil icon to open the text editor.
• Within the text editor right-click and choose Expression from the dropdown menu to open the expression editor.
  
  

Example Usage

• Here’s an example expression to retrieve the Name field from the second dataset:

  =Lookup(Fields!DepartmentID.Value, Fields!ID.Value, Fields!Name.Value, "DataSet2")
  

  

• In this example:

  Function Breakdown	Description
  Fields!DepartmentID.Value	The lookup key from the primary dataset.
  Fields!ID.Value	The matching key in the secondary dataset.
  Fields!Name.Value	The field being retrieved from the secondary dataset.
  "DataSet2"	The name of the secondary dataset.

• 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.
  dataset	The name of the secondary dataset.

References

• For further details on using the Lookup and LookupSet functions, refer to the Bold Reports documentation on How to Use the Lookup and LookupSet Functions in Bold Reports.

Conclusion

By leveraging the Lookup function, you can efficiently integrate fields from multiple datasets into a single report, ensuring comprehensive and accurate data representation. This functionality is ideal when data resides across different sources but needs to be presented cohesively within one report.