CODEWITHSUNDEEP
reporting-services
akhil chunduri
akhil chunduri

Reputation: 1

SSRS lookup function not fetching data in correct

Table1:

+------------+
| Id  Value1 |
+------------+
| 1  abc     |
| 1  bda     |
| 1  bvc     |
+------------+

Table2:

+-----------+
| ID Value2 |
+-----------+
| 1  11     |
| 1  12     |
| 1  13     |
+-----------+

Now I have used Lookup function in ssrs(Below)

=Lookup(Field!ID.Value,Field!ID.Value,Field!Value2.Value,"Table2")

And the result looks like this:

+------------------+
| ID Value1 Value2 |
+------------------+
| 1  abc  11       |
| 1  bda  11       |
| 1  bvc  11       |
+------------------+

Field name Value2 values are not fetching correct.

Please help on this.

Thanks in advance.

Upvotes: 0

Views: 854

Answers (1)

Pete Rennard-Cumming
Pete Rennard-Cumming

Reputation: 1618

If your two tables are from the same data source, and you're able to adjust the SQL query, it's much easier to JOIN the tables into a single dataset instead of using Lookup() expressions.

Lookup() will only return the first value with a matching ID. As all your IDs are the same, Lookup() is just returning the first value in Table2 for all rows.

If you only want to look up one specific row from Table2, you'll need to find a unique ID number (primary key) that can be referenced in Table2. If you want to look up ALL the rows that match between Table1 and Table2, you'll need to use LookupSet() instead.

There's likely some options to Lookup by each table's Row Number, but you might want to reassess your data structure and/or report design before considering this.

Upvotes: 1

Related Questions