How to Use the Lookup and LookupSet Functions in Bold Reports

The Lookup and LookupSet functions are powerful tools within Bold Reports that enable you to retrieve values from datasets based on specific conditions. These functions are particularly useful when you need to fetch dependent fields or collections of values. In this article, we’ll explore how to use both the Lookup and LookupSet functions, highlighting their syntax and providing step-by-step instructions for implementing them in your reports.

The Lookup Function:

The Lookup function is used to retrieve a specific value from a dataset based on a given condition. It’s particularly handy when you need to fetch a single value associated with a certain condition.

The syntax for the Lookup function is:

 =Lookup(source_expression, destination_expression, result_expression, dataset)

• source_expression: is the expression that returns the value of the field to be used as the basis for the lookup. In this case, the Max function is used to obtain the maximum value of the ReleaseDate field.
• destination_expression is the expression that returns the value of the field for which you want to retrieve the lookup value. In this case, the ReleaseVersion, Link, and ReleaseDescription fields are used.
• result_expression is the expression that returns the value of the lookup. In this case, the value of the field is simply returned.
• dataset is the name of the dataset that contains the fields used in the lookup.

Steps to use the Lookup Function:

1. Create a table with your dataset fields and then drag and drop a textbox.

2. In the textbox’s expression, use the Lookup function to fetch the dependent field value. For example:

   =Lookup(Fields!ProductCategoryID.Value, Fields!ProductCategoryID.Value, Fields!Name.Value, "DataSet1")
   

   This retrieves the dependent Name field value based on the ProductCategoryID.
   

   

3. Save and preview the report.
   

   

   For the ProductCategoryID, the dependent Name field will be displayed as an output.

The LookupSet Function:

The LookupSet function is an extension of the Lookup function, retrieving a collection of values rather than a single value. This is useful when you want to obtain multiple dependent values that correspond to a certain condition.

The syntax for the LookupSet function is:

 =LookupSet(source_expression, destination_expression, result_expression, dataset)

• source_expression is the expression that returns the value of the field to be used as the basis for the lookup. In this case, the Max function is used to obtain the maximum value of the ReleaseDate field.
• destination_expression is the expression that returns the value of the field for which you want to retrieve the lookup value. In this case, the ReleaseVersion, Link, and ReleaseDescription fields are used.
• result_expression is the expression that returns the value of the lookup. In this case, the value of the field is simply returned.
• dataset is the name of the dataset that contains the fields used in the lookup.

Steps to use the LookupSet Function:

1. Create a table with your dataset fields and add a new column, then add the LookupSet expression in it.

2. Use the below expression to fetch the collection dependent Name fields.
   

   

=Join(LookupSet(Fields!CategoryID.Value,Fields!ProductID.Value, Fields!ProductName.Value, "Products"),",")

In this example the Join function is used to retrieve the collection of values and then Join those values separated by commas.

3. Save and preview the report.
   
   

In our example, the CategoryID field from the Categories dataset is compared with the ProductID field in the Products dataset. The LookupSet function retrieves the collection of dependent ProductName values.