SSRS LookUpSet

Updated April 3, 2023

Introduction to SSRS LookUpSet

The following article provides an outline for SSRS LookUpSet. Using SSRS Lookup methods, one can merge data from the two datasets in a single data zone. For a 1-N relationship between two datasets, the LookupSet method is utilized. And play an important influence on the performance and quantity of our reports.

What is SSRS LookUpSet?

Employ LookUpSet to get a set of values for a name-value pair with a 1-to-many connection from the provided dataset. For example, we may use LookUpSet to fetch all associated phone numbers for Patient identification in a table from a dataset that is not tied to the data region.

How to Use SSRS LookUpSet?

We can bind a table to a single dataset in SSRS analyses, which makes showing records from another dataset. So, based on a matching result between the current and destination datasets, we utilize LookupSet to retrieve entries from another dataset. For instance, we could use LookupSet to display the event parties linked with a certain activity.

LookupSet (source_expr, destination_expr, Output_expression, dataset)

Definitions of parameters:

• source expression is a placeholder for a source phrase (Variant Array), An expression that defines the set of terms or keys to look up and is executed in the current scope.
• destination expression – (Variant) is an expression that provides the name or key to check on and is tested for every line in a dataset. This column will be part of the same dataset as the last argument in almost the same function.
• result expression – (Variant) is an expression that defines the value to get for the row in the dataset when source expression = destination expression. Again, we want to get a field from the destination dataset for appropriate source expression and destination expression.
• Dataset – A constant that defines a dataset’s identifier in the report. The dataset within which we’d like to examine the values.

For example, let’s have two datasets for the comparison:

DataSet1 fields would be:

PID, PName, PDescription, Date

DataSet2 fields Would be:

Time, Weather, Temp

A Tablix has been created for DataSet1 that displays a listing of all of the tasks and all of DataSet1’s fields.

The LookupSet method can be used in the columns of the tablix to acquire the matching ‘Temp’ value located in DataSet2 in the tablix by utilizing the preceding:

=LookupSet(Fields!DateCreated.Fields, value!Time.Fields, value!Temp.Value,"DataSet2")

The first argument is that the current dataset’s column/field matches the other dataset’s column/field.

The column/field in the other dataset, which will equal the column/field indicated in the first argument, is the second parameter.

The third option specifies the column/field in the other dataset that will be fetched if the first two parameters’ columns/fields match.

The name of the other dataset, as just a string, is the fourth argument.

SSRS LookUpSet Function

=LookupSet(Fields!datasetX_ID.Value, Fields!datasetY_IDs.Value,Fields!datasetYColumnTarget.Value,"datasetY")
//Provides array of values

This function is not an alternative to JOINs but rather something akin to SQL JOINs. In the tablix, we can’t just put the results in a textbox. These findings must be concatenated into a string. We’ll use the Join function with our LookUpSet method to accomplish this. When we use the FORMAT function, then get a string rather than an integer as a return.

• JOIN is used to combine strings from many tables into a single string, which isn’t going to help us with the problem.
• With a LookUpSet, SUM would not perform.

Using Join()

=Join(LookupSet(Fields!datasetX_ID.Value, Fields!datasetY_IDs.Value,Fields!datasetYColumnTarget.Value,"datasetY"), ",")
//returns a string values : Value1, Value2, ..., ValueN

In various cases, one may want to total the values returned by MultiLookup and LookupSet. The following code appears to be a logical way to implement this:

=Sum(LookupSet(Fields!Parent_Num.Value, Fields!Parent_Num.Value,Fields!StoreCount.Value, "StoreCount"))

Using LookupSet allows multiple values from the secondary dataset to be retrieved. LookupSet has the following syntax. First, we must submit the following four pieces of information:

• The field value for the main dataset’s identical column (Subcategory).
• The field value for the secondary dataset’s common column is frequently the same as the first parameter (so again, Subcategory).
• We need to get a column from the secondary dataset (Reseller Sales).

LookupSet(Fields!Subcategory.Value,Fields!Subcategory.Value, Fields!wholesale_Sales_Amount.Value, "WholeSalesYearly2022")

The secondary dataset’s name. For the sum of the lookupset function, I used the following custom code and expression.

Function SumLup(ByVal items As Object()) As Decimal
If items Is Nothing Then
Return Nothing
End If
Dim sumx As Decimal = New Decimal ()
Dim pt as Integer = New Integer ()
sumx = 0
pt = 0
For Each item As Object In items
sumx += Convert.ToDecimal(item)
pt += 1
Next
If (pt = 0) Then return 0 else return sumx
End Function

Examples

The following steps can be used to implement these functions in SSRS or Report Builder:

Let’s create a Data source and then Datasets

Dataset name: LookUp_Param ,

Dataset Query:

DECLARE @Cntry_Master TABLE (Id INT,Cntry_Code VARCHAR(10),Cntry_Name VARCHAR(10))
INSERT INTO @Cntry_Master SELECT 1,'NOR','NORWAY' INSERT INTO @Cntry_Master SELECT 2,'USA','America' INSERT INTO @Cntry_Master SELECT 3,'CN','CHINA'
SELECT * FROM @Cntry_Master

As seen below, we’ll utilize the Employee dataset as the secondary dataset and the Country dataset as the head table in Tablix.

Employee names from the Employee database related to the particular countries must be called. So, right-click on the column and choose expressions from the menu. Using the lookup, now need to consider the necessary changes in the Expression box. Set up a role where the first value (Key Value) comes from the main dataset (Country), and the second value (Key-value) arises from the suggested dataset (Employee). The third value (retrieved value) emerges from the requested dataset (Employee), and the fourth value (name of the requested dataset) arrives first from the main dataset (Country) (Employee).

The expression goes like this:

=Join(LookUpSet(Fields!CtryCode.Value,
Fields!CtryCode.Value,
Fields!EmployeeName.Value,
"Employee"),"," )

And finally, in the report generation, we could see two names have been generated in the USA Row due to the LookUpSet function, which is shown below:

Conclusion

Because any SSRS report component, such as charts, tablix, tables, and so on, may only be assigned to one dataset, I’ve found the LookupSet method to be very handy in retrieving data from other datasets.

Recommended Articles

This is a guide to SSRS LookUpSet. Here we discuss How to work SSRS LookUpSet and the examples and functions with code. You may also look at the following articles to learn more –

1. What is SSRS?
2. PyTorch Loss
3. SSRS Interview Questions
4. SSRS Versions