Finding the Maximum Value from a LookupSet in Bold Reports using Custom Code
When working with Bold Reports, you may encounter a scenario where you need to find the maximum value from a set of values returned by the LookupSet function. The LookupSet function is commonly used to retrieve a set of values that are associated with a specified key or set of keys from a dataset. However, finding the maximum value within this set requires additional steps. Below is a guide on how to achieve this using custom code within Bold Reports.
Using Custom Code to Find Maximum Value
To find the maximum value from a set of values returned by LookupSet, you can use custom code in your report. Here’s how to do it:
-
Click the grey background of the report area, then find the Code option in the Properties panel to access the code module.
-
In the code editor, insert the following VB code snippet. This function calculates the maximum value from an array of values:
Function GetMaxValue(values As Object()) As Integer Dim maxValue As Integer = Integer.MinValue For Each value As Object In values If Not IsNothing(value) AndAlso IsNumeric(value) Then Dim numericValue As Integer = Convert.ToInt32(value) maxValue = Math.Max(maxValue, numericValue) End If Next Return maxValue End Function
-
After adding the custom code to your report, you can use the GetMaxValue function in an expression to find the maximum value from a LookupSet. Here’s an example expression:
=Code.GetMaxValue(LookupSet(Fields!EmployeeID.Value, Fields!EmployeeID.Value, Fields!Rate.Value, "DataSet2"))
Note: Replace Fields!EmployeeID.Value and Fields!Rate.Value with the appropriate field references for your report, and “DataSet2” with the name of the dataset that you are using.
Preview snapshot:
By following the steps outlined above, you can successfully retrieve the maximum value from a set of values returned by the LookupSet function in Bold Reports.
You can download the sample report from here.