Harnessing the Power of LookUp and LookUpSet in Bold Reports for Superior Reporting

Bold Reports provides a robust platform for crafting comprehensive and dynamic reports. Among its powerful features are the Lookup and LookUpSet functions, which allow users to fetch data from multiple datasets and display it in their reports. Mastering these functions lets you create efficient and user-friendly reports, enhancing accuracy, and turning raw data into valuable metrics for stakeholders. Understanding when and how to use Lookup and LookUpSet functions can streamline data retrieval. This blog delves into the functionality of various lookup functions in Bold Reports, offering insights on selecting the appropriate function to create dynamic and data-rich reports.

Understanding the LookUp Functions

In Bold Reports, lookup functions combine data from different datasets into a single data region. These functions are particularly useful when your data is spread across multiple datasets, allowing you to create comprehensive reports without the need for complex joins or additional queries in your data sources. Two of these functions are Lookup and LookupSet.

The Lookup Function

The Lookup function is used for a 1–1 relation between two datasets. It returns the first matching value for the specified name from a dataset that contains name/value pairs in a paginated report.

The syntax for the Lookup function is the following.

=Lookup(source_expression, destination_expression, result_expression, dataset)

• source_expression: The field from the source dataset.

• destination_expression: The field to be matched from the destination dataset.

• result_expression: The field whose value has to be returned from the destination dataset.

• dataset: destination dataset name.

Steps to use the Lookup function

1. Create a table with your dataset fields. In this case, we have two datasets, EmployeeDataset and CountryDataset. We assign the values from EmployeeDataset to the table.

Datasets created and values from EmployeeDataset assigned to the table

1. Add a column to the right and name it Country Name. Use the following Lookup expression in the content field to retrieve Country Name values from CountryDataset based on the EmployeesDataset country code value.

=LookUp(Fields!CountryCode.Value, Fields!CountryCode.Value, Fields!CountryName.Value,"CountryDataset")

Lookup expression used to retrieve Country Name values.

1. Next, save and preview the report. The Country Name values will be retrieved.

The Country Name values were retrieved.

The LookUpSet Function

The LookUpSet function is used for a 1-N relation between two datasets. It returns a set of matching values for the specified name from a dataset that contains name/value pairs in a paginated report.

The syntax for the LookupSet function is the following.

=Join(LookupSet(source_expression, destination_expression, result_expression, dataset),",")

• source_expression: The field from the source dataset.

• destination_expression: The field to be matched from the destination dataset.

• result_expression: The field whose value has to be returned from the destination dataset.

• dataset: Destination dataset name.

Steps to use the LookUpSet function

1. Create a table with your dataset fields and assign values from each dataset. In this case, Country Name from the CountryDataset and Employee’s Name from the EmployeesDataset are used.

Values assigned to the table from each dataset.

1. To retrieve the Employee’s Name field values from the EmployeesDataset, use the following LookUpSet expression in the content field.

=Join(LookUpSet(Fields!CountryCode.Value,Fields!CountryCode.Value, Fields!EmpName.Value,"EmployeeDataset"),",")

LookUpSet expression added in the content field.

The Join function in the expression is used to retrieve the collection of values and then join those values separated by commas.

1. Save and preview the report. The LookUpSetfunction retrieves the collection of dependent ProductName values.

The employee names retrieved from EmployeeDataset.

Conclusion

Mastering the Lookup and LookUpSet functions in Bold Reports empowers report developers to create dynamic reports that draw from multiple datasets. By understanding when and how to use lookups effectively, report creators can elevate the quality and depth of their analytical outputs. By following this outline, you can create a detailed and informative report leveraging lookup functions in Bold Reports.

If you have any questions, please post them in the comment section below. We welcome you to start a free trial with Bold Reports to experience its features for yourself. Be sure to let us know what you think!